Dir() function not working in Mac Excel 2011 VBA

excel, macos, vba

Solution

Gianna, you cannot use `DIR` like that in VBA-EXCEL 2011. I mean the wildcards are not supported. You have to use MACID for this purpose.

See this code sample (TRIED AND TESTED)

Sub Sample()
    MyDir = ActiveWorkbook.Path
    strPath = MyDir & ":"

    strFile = Dir(strPath, MacID("TEXT"))

    'Loop through each file in the folder
    Do While Len(strFile) > 0
        If Right(strFile, 3) = "csv" Then
            Debug.Print strFile
        End If

        strFile = Dir    
    Loop
End Sub

See this link for more details on MACID

Topic: MacID Function

Link: http://office.microsoft.com/en-us/access-help/macid-function-HA001228879.aspx

EDIT:

In case that link ever dies which I doubt, here is an extract.

MacID Function

Used on the Macintosh to convert a 4-character constant to a value that may be used by Dir, Kill, Shell, and AppActivate.

Syntax

MacID(constant)

The required constant argument consists of 4 characters used to specify a resource type, file type, application signature, or Apple Event, for example, TEXT, OBIN, "XLS5" for Excel files ("XLS8" for Excel 97), Microsoft Word uses "W6BN" ("W8BN" for Word 97), and so on.

Remarks

MacID is used with Dir and Kill to specify a Macintosh file type. Since the Macintosh does not support * and ? as wildcards, you can use a four-character constant instead to identify groups of files. For example, the following statement returns TEXT type files from the current folder:

Dir("SomePath", MacID("TEXT"))

MacID is used with Shell and AppActivate to specify an application using the application's unique signature.

HTH

Problem

Hi I am trying to list all the files in a subdirectory of where the Excel workbook is residing in. For some reason, the code cannot execute beyond the Dir function. Can anyone please advise? Thank you! ``` Sub ListFiles() ActiveSheet.Name = "temp" Dim MyDir As String 'Declare the variables Dim strPath As String Dim strFile As String Dim r As Long MyDir = ActiveWorkbook.Path 'current path where workbook is strPath = MyDir & ":Current:" 'files within "Current" folder subdir, I am using Mac Excel 2011 'Insert the headers in Columns A, B, and C Cells(1, "A").Value = "FileName" Cells(1, "B").Value = "Size" Cells(1, "C").Value = "Date/Time" 'Find the next available row r = Cells(Rows.Count, "A").End(xlUp).Row + 1 'Get the first file from the folder 'Note: macro stops working here strFile = Dir(strPath & "*.csv", vbNormal) 'Loop through each file in the folder Do While Len(strFile) > 0 'List the name, size, and date/time of the current file Cells(r, 1).Value = strFile Cells(r, 2).Value = FileLen(strPath & strFile) Cells(r, 3).Value = FileDateTime(strPath & strFile) 'Determine the next row r = r + 1 'Get the next file from the folder strFile = Dir Loop 'Change the width of the columns to achieve the best fit Columns.AutoFit End Sub ```

Original source