Mac Excel 2011 VBA: Merging xls files in folder

[timbenz]

I'm trying to use Mac Excel 2011 VBA to grab all .xls files in a directory and merge them. The trouble, however, isn't the merge algorithm; it's convincing VBA to properly identify and iterate through the files. I've tried a thousand things, but I can't get Excel to use the path to the folder.

Here is what I have:

Sub CombineSheetsFromAllFilesInADirectory()

Path = "Macintosh HD:Users:timben:Documents:datafiles"
Application.EnableEvents = False 'turn off events
Application.ScreenUpdating = False 'turn off screen updating
Set mWB = Workbooks.Add(1) 'create a new one-worksheet workbook
Set aWS = mWB.ActiveSheet 'set active sheet variable to only sheet in mWB
FileName = Dir(Path, MacID("XLS8")) ' set first file's name to filename variable
Do While Len(FileName) > 0
' Merge files
Loop
' Clean up
End Sub

It never enters the merge loops, as it never picks up any files from the directory. I know the path is right because I checked it with "MyPath = CurDir " in a debug routine.

Ideas? I tried XLS8 and XLS5 MacIDs, with no difference. It will work if I feed it Dir ("") instead of a path and XLS8, but that is clumsy and doesn't allow me to filter file types or set a path.

 

[timbenz]

Excel 97-2004 workbook. Have tried both XLS8 and XLS5 MacIDs, thinking that might be the issue.

 

[Domenic]

You've already verified that the path is correct using CurDir. Have you physically made sure that the folder in fact contains Excel files?

 

[timbenz]

Yes, it does. Locked and loaded with .xls.

I'm wondering if I've stumbled onto an Excel 2011 VBA bug.

 

[Domenic]

...I'm wondering if I've stumbled onto an Excel 2011 VBA bug.

Unfortunately, I only have Excel v.X, so I'm not able to test it for you. If you haven't already done so, try searching Google.

 

[mikerickson]

I have 2011.
This worked for me. Note the final colon in path.

Sub CombineSheetsFromAllFilesInADirectory()
    Dim path As String
    Dim mWB As Workbook
    Dim aWS As Worksheet
    Dim filename As String
    
    path = "Macintosh HD:Users:merickson:Documents:XLstuff:"
    
    Application.EnableEvents = False 'turn off events
    Application.ScreenUpdating = False 'turn off screen updating
    
    Set mWB = Workbooks.Add(1) 'create a new one-worksheet workbook
    Set aWS = mWB.ActiveSheet 'set active sheet variable to only sheet in mWB
    filename = Dir(path, MacID("XLS8")) ' set first file's name to filename variable
    Do While Len(filename) > 0
        Debug.Print filename
        filename = Dir()
    Loop
    ' Clean up
End Sub

 

[timbenz]

Thanks. Strangely enough, messing about with paths again helped me figure it out. The issue, it seems, is that the XLS files, which Excel insists are 2007, were not being recognized by either MacID XLS5 or XLS8. Opening them and resaving them caused them to be visible again to the VBA script. Really strange.

 

[fast_thing]

Hi timbenz, would you mind posting your entire code (including the merge operation)?

I've been trying to accomplish the same thing as you and having similar problems.

 

[Ron de Bruin]

See my Mac page for a example
http://www.rondebruin.nl/mac.htm

Have fun

 

[Ron de Bruin]

Try the code on this page
http://www.rondebruin.nl/mac.htm#Merge