VBA - GetOpenFilename

[bsquad]

I am almost positive I am going about this all wrong - I am trying to open files out of the directory dialog and set them based on a wildcard name. I am thinking it has to do with the declarations between as workbook or as string or as variant. I'll eventually be using 'Windows().Activate' to switch between them. I have the code below.

Sub Data_Import2()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ExtFiles As Variant
    Dim ExtFile_1 As Variant
    Dim ExtFile_2 As Variant
    'Dim File_Path As String
    
    Application.DefaultFilePath = ActiveWorkbook.Path
    ExtFiles = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xlsx; *.xlsm),*.xlsx;*.xlsm", _
                Title:="Select File", MultiSelect:=True)
                Set ExtFile_1 = ExtFiles.Name("*Class1.xlsx")
                Set ExtFile_2 = ExtFiles.Name("*Class2.xlsx")

 

[Norie]

GetOpenFilename doesn't return a folder it will return the path and filenames of the files the user selects.

Is that what you want or do you want the user to select a folder?

PS What are you going to be doing that requires the use of Windows().Activate?

 

[bsquad]

Hi Norie,

no (not returning the folder), I am essentially going to be using workbook.open for both ExtFile_1 and ExtFile_2 and run macros on both - I am just trying to give them each a declaration so I can switch back and forth with Windows().Activate between the 3 workbooks.

 

[Norie]

If ExtFile_1 and ExtFil_2 are to refer to workbooks they should be declared as such.

Assuming the user has selected 2 workbooks then this is how you could set things up.

Sub Data_Import2()
Dim wb As Workbook
Dim ws As Worksheet
Dim ExtFiles As Variant
Dim ExtFile_1 As Workbook
Dim ExtFile_2 As Workbook
    
    Application.DefaultFilePath = ActiveWorkbook.Path
    ExtFiles = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xlsx; *.xlsm),*.xlsx;*.xlsm", _
                Title:="Select File", MultiSelect:=True)

    Set ExtFile_1 = Workbooks.Open(ExtFiles(1))
    Set ExtFile_2 = Workbooks.Open(ExtFiles(2))

 

[bsquad]

this is great thank you, do you know how I can designate them based on their workbook name?

 

[Norie]

Not sure what you mean.

 

[bsquad]

so how to designate (ExtFiles(1)) and (ExtFiles(2)) based on the files' name. In a sense if I select two files; one that ends in "*Class1.xlsx" and one that ends in "*Class2.xlsx" - how I would designate *Class1.xlsx to ExtFile(1)

 

[Norie]

Why are you using wildcards?

If you use the code I posted the 2 files selected by the user can be referred to directly using the workbook variables ExtFile_1 and ExtFile_2.

 

[bsquad]

this is so the user doesn't have to select the files in a particular order in the dialog box

 

[Norie]

What does it matter which order they are selected in if you are only running a macro against each file?

If you want to have the user to select a specific files for specific roles/purposes, eg a source file and a destination file, use GetOpenFileName.

Dim wbSource As Workbook
Dim wbDestination As Workbook
Dim strFileName As String

    Application.DefaultFilePath = ActiveWorkbook.Path

    strFileName = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xlsx; *.xlsm),*.xlsx;*.xlsm", _
                Title:="Select Source File", MultiSelect:=True)

    If strFileName <> "False" Then
        Set wbSource = Workbooks.Open(strFileName)
    Else
        MsgBox "Operation Cancelled.", vbInformation
        Exit Sub
    End If


    strFileName = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xlsx; *.xlsm),*.xlsx;*.xlsm", _
                Title:="Select Destination File", MultiSelect:=True)

    If strFileName <> "False" Then
        Set wbSource = Workbooks.Open(strFileName)
    Else
        MsgBox "Operation Cancelled.", vbInformation
        Exit Sub
    End If

    ' continue on with rest of code