Coding works in Windows OS but not in Mac OS( Run time error 1004)

Question

Using it for fetching excel file from specific folder. It is working in windows OS but not in Mac OS. How to make it cross plate form? Pls help.

 Sub getfilename()
 Dim objFSO As Object
Dim intCountRows As Integer
Application.FileDialog(msoFileDialogFolderPicker).Title = _
"Select a Path"
intResult = Application.FileDialog( _
msoFileDialogFolderPicker).Show
If intResult <> 0 Then
    strpath = Application.FileDialog(msoFileDialogFolderPicker _
    ).SelectedItems(1)
    Set objFSO = CreateObject("Scripting.FileSystemObject")
ThisWorkbook.Activate
Sheets("dropdown").Select
Range("q2").Value = strpath
End If
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strpath)
 ThisWorkbook.Activate
Sheets("dropdown").Activate
Range("aa3:aa2000").Clear
i = 1
For Each objFile In objFolder.Files
    Filename = objFile.Name      
     Range("aa1000").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    Application.ActiveCell = Filename
 Next objFile
End Sub

`Scripting.FileSystemObject` is a Windows DLL, not available on Mac. You will need entierly different for Mac. — chris neilsen, Dec 15 '14 at 06:47

peege · Accepted Answer · 2014-12-15T08:44:27.447

You could check to see what operating system it is and have different code run for each, working through whichever problem areas specifically crop up in the Mac version. Most likely the non-excel related code. As @chrisneilson mentioned, Specifically "Scripting.FileSystemObject"

Also, as mentioned in the comments below, the lines of code that aren't acceptable will be compiled anyway and cause an error, so you will need to run the code using conditional compilation.

Basically they only COMPILE when the conditions are met, and you do this by using "#" in front of the line such as an If statement: "#If"

Modified from MSDN's: Run The Correct Macro in Windows or on the Macintosh

Sub WINorMAC()
'Test using conditional compiler constants.
    #If Win32 Or Win64 Then
        'Is a Windows user.
        Call getfilename
    #Else
        'Is a Mac user so you need to test whether the product is Excel 2011 or later.
        If Val(Application.Version) > 14 Then
            Call My_Mac_Macro  'almost getfilename, with some replacements for Mac
        End If
    #End If
End Sub

Now just modify your sub getfilename() to include the conditional compiler constants. And do the opposite for the Mac version to ensure there aren't compile errors.

Sub getfilename()
    #If Win32 Or Win64 Then
        Dim objFSO As Object
        Dim intCountRows As Integer

        Application.FileDialog(msoFileDialogFolderPicker).Title = _
            "Select a Path"
            intResult = Application.FileDialog( _
            msoFileDialogFolderPicker).Show

        If intResult <> 0 Then
            strpath = Application.FileDialog(msoFileDialogFolderPicker _
                ).SelectedItems(1)
            Set objFSO = CreateObject("Scripting.FileSystemObject")

            ThisWorkbook.Activate
            Sheets("dropdown").Select
            Range("q2").Value = strpath

        End If

        Dim objFolder As Object
        Dim objFile As Object
        Dim i As Integer

        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFSO.GetFolder(strpath)

        ThisWorkbook.Activate
        Sheets("dropdown").Activate
        Range("aa3:aa2000").Clear
        i = 1
        For Each objFile In objFolder.Files
            Filename = objFile.Name
             Range("aa1000").Select
            Selection.End(xlUp).Select
            ActiveCell.Offset(1, 0).Select
            Application.ActiveCell = Filename
        Next objFile
    #End If
End Sub

edit: modified answer after comments and discussion to include conditional compilers in addition to checking for the OS type.

You can find solutions for each problem. The point here is you need to split the code into PC or Mac. Try searching for "Mac version scripting.filesystemobject" and go from there — peege, Dec 15 '14 at 07:17
This question in StackOverflow should get you started in that direction. Most of the Excel related VBA should be valid still. http://stackoverflow.com/questions/4670420/how-can-i-install-use-scripting-filesystemobject-in-excel-2010-for-mac — peege, Dec 15 '14 at 07:27
@PJ this won't work if code for one OS won't complile for the other. Use Conditional Compilation instead (`#If Mac Then ...`) — chris neilsen, Dec 15 '14 at 07:50
@PJ VBA only tries to _compile_ the code in the relavent It Then Else section. [see this answer for an example](http://stackoverflow.com/a/27371424/445425) — chris neilsen, Dec 15 '14 at 07:53
Could you have the relevant code in a subroutine and only call it once the OS has been established though? Or even having the uncompilable lines in a sub not getting called would stop it? — peege, Dec 15 '14 at 07:55
@PJ for code to be skiped by the compiler it _has_ to to enclosed in the `#If ...`. A separate Sub will be compiled as normal, and possibly error (you could always put the `#If` in the _Sub_ too) — chris neilsen, Dec 15 '14 at 08:00
@PJRosenburg Getting runtime error 1004 and where is My_Mac_Macro. Im struggle on that area. Pls help — user3766722, Dec 16 '14 at 12:33
You would need to write it. You need to make it almost the same as the PC macro, but substituting MAC code for the fileSystemObject parts. Search for MAC equivalent of that in Excel VBA. — peege, Dec 16 '14 at 12:38

Coding works in Windows OS but not in Mac OS( Run time error 1004)

1 Answers1