Openfile macro (2003)

Topic

New Reply

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.

Reply | Quote

Replies

How about something like this?
Steve

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

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

Steve

Reply | Quote

Thanks. I will give it a try.

Reply | Quote