Dir() function not working in Mac Excel 2011 VBA

by

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

More Related Contents:

  1. VBA Shell function in Office 2011 for Mac
  2. VBA (Excel) Dictionary on Mac?
  3. Create a folder and sub folder in Excel VBA
  4. Excel 2013 VBA Clear All Filters macro
  5. Checking if a worksheet-based checkbox is checked
  6. How do I issue an HTTP GET from Excel VBA for Mac
  7. How can I install/use “Scripting.FileSystemObject” in Excel 2010 for MAC?
  8. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  9. Sequence of numbers based on start and end value of 2 cells [closed]
  10. Function to convert column number to letter?
  11. Better way to find last used row
  12. 2 Dimensional array from range [closed]
  13. How can I create a calendar input in VBA Excel?
  14. Using Google Chrome in Selenium VBA (Installation Steps)
  15. VBA: Convert Text to Number
  16. Iterating through populated rows
  17. Using VBA to detect which decimal sign the computer is using
  18. Referencing Excel Userform Control Name Value From String (VBA)
  19. Loop through each row of a range in Excel
  20. Fill formula down till last row in column
  21. How to create a range from two ranges in VBA?
  22. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  23. If string contains word from list, return value adjacent to list value
  24. Refreshing all the pivot tables in my excel workbook with a macro
  25. excel vba freeze pane without select
  26. Converting a string representation of a constant into a constant?
  27. VBA WinHTTP to download file from password proteced https website
  28. Create folder path if does not exist (saving from VBA)
  29. Concatenate multiple ranges using vba
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment