Dir function doesn't show the files

[sunxunemily]

Hello there,

I am trying to write Excel VBA on Mac. Need to use the Dir function to get the file name in my document folder, but it showed only one file in the folder "DS_Store". There are actually several Excel files in the directory just don't show up by using this function. I have done some research on this "DS_Store" file, it seems a hidden/system file for Mac. It's fine, but it really doesn't make sense that other files won't show. Also, when writing VBA for Windows, *.xls refers to any file with a .xls extension. Does anyone know how to express the same thing for Mac? I am new with Mac, it just works so different than Windows. New job!! Frustrated now!!! Any help will be appreciated!

Emily

 

[JoeMo]

I use Windows, but here's a snippet from VBA help on the Dir Function that might be useful:

Remarks
In Microsoft Windows, Dir supports the use of multiple character (*) and single character (?) wildcards to specify multiple files. On the Macintosh, these characters are treated as valid file name characters and can't be used as wildcards to specify multiple files.
Since the Macintosh doesn't support the wildcards, use the file type to identify groups of files. You can use the MacID function to specify file type instead of using the file names. For example, the following statement returns the name of the first TEXT file in the current folder:

<code>Dir("SomePath", MacID("TEXT")) </code></pre>

<tbody>
</tbody>

To iterate over all files in a folder, specify an empty string:

<code>Dir("") </code></pre>

 

[sunxunemily]

Thank you so much for your reply!
I tried the MacId function as this, fName = Dir(fPath & ":", MacID("xls")), tried to catch any Excel file, but it didn't catch any Excel file. I am sure there are Excel files in that directory. Do you happen to know why? Thanks!

Emily

I use Windows, but here's a snippet from VBA help on the Dir Function that might be useful:

Remarks
In Microsoft Windows, Dir supports the use of multiple character (*) and single character (?) wildcards to specify multiple files. On the Macintosh, these characters are treated as valid file name characters and can't be used as wildcards to specify multiple files.
Since the Macintosh doesn't support the wildcards, use the file type to identify groups of files. You can use the MacID function to specify file type instead of using the file names. For example, the following statement returns the name of the first TEXT file in the current folder:

<code>Dir("SomePath", MacID("TEXT")) </code>

<tbody>
</tbody>

To iterate over all files in a folder, specify an empty string:

<code>Dir("") </code>

 

[JoeMo]

How is fPath defined, and why do you have & ":" in there?

 

[sunxunemily]

Hi Joe,

Ok, here is the code. I tried to loop through all the Excel files in the folder, copy the cells I need to a target Excel sheet. It just didn't find any Excel file in the directory. Could you take a look and let me know what's wrong? Thanks!!

Sub Import_Profile()


Dim fName As String, fPath As String, fPathDone As String, fList As String
Dim SourceFolderName As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet
Dim Company As String
Dim cCount As Long


'Setup
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

SourceFolderName = "Factory Submissions"

Set wsMaster = ThisWorkbook.Sheets("Target_Profile")

With wsMaster
If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
.UsedRange.Offset(1).EntireRow.Clear
NR = 1
Else
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
End If





fPath = ThisWorkbook.Path
'MsgBox fPath
fPathDone = fPath & ":Imported:"
'MsgBox (fPathDone)

On Error Resume Next
MkDir fPathDone
On Error GoTo 0

fList = Dir(fPath, MacID("xls"))
MsgBox fList


fName = fPath & ":" & fList



Do While Len(fName) > 0
If fName <> ThisWorkbook.Name Then
Set wbData = Workbooks.Open(fName)
'Company = Left(fName, Len(fName) - 5) 'get the name of the file



wbData.Sheets("Profile").Range("B3").Copy
.Range("A" & NR).PasteSpecial Paste:=xlPasteValues

wbData.Sheets("Working Hours").Range("A2").Copy
.Range("B" & NR).PasteSpecial Paste:=xlPasteValues

wbData.Sheets("Profile").Range("B8").Copy
.Range("C" & NR).PasteSpecial Paste:=xlPasteValues

wbData.Sheets("Profile").Range("B9").Copy
.Range("D" & NR).PasteSpecial Paste:=xlPasteValues

wbData.Sheets("Profile").Range("B13").Copy
.Range("E" & NR).PasteSpecial Paste:=xlPasteValues

wbData.Sheets("Profile").Range("B14").Copy
.Range("F" & NR).PasteSpecial Paste:=xlPasteValues

wbData.Sheets("Profile").Range("B15").Copy
.Range("G" & NR).PasteSpecial Paste:=xlPasteValues

wbData.Sheets("Profile").Range("B16").Copy
.Range("H" & NR).PasteSpecial Paste:=xlPasteValues

wbData.Sheets("Profile").Range("B20").Copy
.Range("I" & NR).PasteSpecial Paste:=xlPasteValues

wbData.Sheets("Profile").Range("B21").Copy
.Range("G" & NR).PasteSpecial Paste:=xlPasteValues

wbData.Close False
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
Name fName As fPathDone & ":" & fName 'move file to IMPORTED folder
End If
fList = Dir
Loop
End With

errorexit:
ActiveSheet.Columns.AutoFit
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

How is fPath defined, and why do you have & ":" in there?

 

[JoeMo]

I'm not familiar with VBA for the Mac, but this is short and worth a try. See if it returns a filename when installed in whatever workbook the code you posted is installed in.

Sub test()
Dim fpath As String
fpath = ThisWorkbook.Path
MsgBox Dir(fpath & Application.PathSeparator, MacID("xls"))
End Sub

 

[sunxunemily]

Hi Joe,

Thank you! I guess Dir just doesn't work for Mac. I will try something else. Appreciate it!

Emily

I'm not familiar with VBA for the Mac, but this is short and worth a try. See if it returns a filename when installed in whatever workbook the code you posted is installed in.

Sub test()
Dim fpath As String
fpath = ThisWorkbook.Path
MsgBox Dir(fpath & Application.PathSeparator, MacID("xls"))
End Sub

 

[JoeMo]

Hi Joe,

Thank you! I guess Dir just doesn't work for Mac. I will try something else. Appreciate it!

Emily

I just learned that the MacID constant must be exactly 4 characters in length. Take two more tries: substitute either XLS5 or XLS8 for XLS (i.e., MacID("XLS5") or MacID("XLS8") ) in the test code I posted.

 

[sunxunemily]

Thank you Joe! I tried, but they don't work. I tried another thing, saved the xls as xlsx file, it works. But it's going to be a lot of extra work to save as every file I have. There are at list 300 hundred files.

I just learned that the MacID constant must be exactly 4 characters in length. Take two more tries: substitute either XLS5 or XLS8 for XLS (i.e., MacID("XLS5") or MacID("XLS8") ) in the test code I posted.

 

[Ron de Bruin]

Thank you Joe! I tried, but they don't work. I tried another thing, saved the xls as xlsx file, it works. But it's going to be a lot of extra work to save as every file I have. There are at list 300 hundred files.

See Loop through Files in Folder on a Mac (Dir for Mac Excel)