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.

 

[Domenic]

Try...

<font face=Calibri><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Len(Filename) > 0<br>    <SPAN style="color:#007F00">' Merge files</SPAN><br>    Filename = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br></FONT>

 

[timbenz]

Thanks. Trouble is, it never makes it into that loop, as FileName never picks up the first file from the directory, for reasons that escape me entirely. The path is correct, as I wrote above, and Watch shows that it is stored in the variable. Baffling.

 

[Domenic]

Try adding the path separator at the end of your path...

<font face=Calibri>Path = "Macintosh HD:Users:timben:Documents:datafiles:"<br></FONT>

 

[timbenz]

I have tried it both with and without the separator. No joy.

This is really baffling me.

 

[mikerickson]

Have you tried
FileName = Dir(Path, "XLS8")

 

[timbenz]

No, I haven't. I was under impression syntax was MacID("XLS8"). I can try though.

 

[Domenic]

What does the following return when entered in the 'Immediate Window"...

? Dir("Macintosh HD:Users:timben:Documents:datafiles:", MacID("XLS8"))

 

[timbenz]

It returns empty string, i.e., nothing.

By way of interest, ?CurDir returns "Macintosh HD:Users:timben:Documents:datafiles".

 

[Domenic]

What type of Excel files do you have in your folder?