Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Openfile macro (2003)

    Hello Everyone,
    I need to create a macro that will allow the user to select multiple excel files and open them. The number of files could vary. What is the best way to do this? I know I can go with the command :
    Workbooks.Open Filename:=
    but how do I set the parameters so that once the person is done opening the file. Basically the macro will allow the user to open the multiple files and copy a sheet from there to another file. Any help would be great. Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Openfile macro (2003)

    How about something like this?
    Steve
    <pre>Option Explicit
    Sub FileProcessingExample()
    'Variable Definition
    Dim FilesToOpen
    Dim iFileCount As Integer
    Dim x As Integer

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    'Get files to work with
    FilesToOpen = Application.GetOpenFilename _
    (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
    MultiSelect:=True)

    'Quit if NO files are selected
    If TypeName(FilesToOpen) = "Boolean" Then
    MsgBox "No Files were selected"
    GoTo ExitHandler
    End If

    'Act on each file
    iFileCount = UBound(FilesToOpen)
    x = 1
    While x <= iFileCount
    Workbooks.Open FileName:=FilesToOpen(x)

    'Process each
    With ActiveWorkbook
    'add your code here to work with each workbook
    'this just print preview the activesheet
    .ActiveSheet.PrintOut preview:=True
    End With

    'Close workbook
    ActiveWorkbook.Close SaveChanges:=False

    'Get next file
    x = x + 1
    Wend

    'Give a message saying you are done
    If iFileCount = 1 Then
    MsgBox "1 File was processed"
    Else
    MsgBox iFileCount & " Files were processed"
    End If

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler

    End Sub</pre>


    Just add the code to do what you want witht he file before the code closes it.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Openfile macro (2003)

    Thanks. I will give it a try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •