Thread: Linking multiple workbooks to master document

Hello - I'm very new to Excel macros and VBA and I found a thread that I think will do what I want, but I'm running into some bugs with the script and it's over my head, so I'm having trouble debugging it.

I am trying to create a macro that will automatically import several different Excel 2010 (.xlsx) workbooks into separate sheets of a single master document. I found this script online (attached) but I'm running into errors at the line

[Filename = Application.GetOpenFilename(FileFilter, 1)]

Can anyone help me fix this? The documents are scripted to open automatically from a master script in FileMaker as .xlsx files and my plan is to then run this macro from the master document to import/link/update/(insert appropriate word) and close them.

Can anyone help me figure out where this is going wrong? I was hoping that the macro would bring up a menu that would let me pick the specific files I want to import and name the new worksheets when I import the data.

If there's a way to do this on an ongoing basis so the macro is constantly running, all the better. Any ideas? I'm in over my head!

When you say "import workbook into single sheets", do you mean
"select a workbook and copy all its worksheets" or "select a workbook, select some sheets and copy those"?

The problem with the code you found is that Microsoft's implimentation of file filters for the Mac is FUBAR. (or should that be "a known bug").
One work-around is to use VBA's MacScript command as in this cross-platform routine.

Dim FileFilter As String
Dim FileName As String
    
#If Mac Then
    FileFilter = Chr(34) & "com.microsoft.excel.xls" & Chr(34) & ", " _
        & Chr(34) & "org.openxmlformats.spreadsheetml.sheet" & Chr(34) & ", " _
        & Chr(34) & "org.openxmlformats.spreadsheetml.sheet.macroenabled" & Chr(34) & ", " _
        & Chr(34) & "public.comma-separated-values-text" & Chr(34)
    FileName = "False"
    On Error Resume Next
    MsgBox MacScript("return choose file of type {" & FileFilter & "}")
    On Error GoTo 0
#Else
    FileFilter = "Excel Files, *.xls;*.xla;*.csv, All Files, *.*"
    FileName = Application.GetOpenFilename(FileFilter, 1)
#End If
    
If FileName Then Exit Sub

MsgBox FileName

Alternatly, you could omit the filter argument of GetOpenFilename.