none
Excel on Apple Macintosh using VBA RRS feed

  • Question

  • I am trying to programmatically open a worksheet in Excel in response to a user action. My code is fairly simple:

        Dim doit As Integer
        doit = Application.Dialogs(xlDialogOpen).Show
        If doit = 0 Then
            Exit Sub
        End If

    When run on Excel in Windows, an open file dialog is displayed, and I can select a file which is then opened. All is good.

    But when run on a Macintosh, I get an error after selecting a file to open from the Open File dialog:

        Run-time error 1004
    
        " could not be found. Check the spelling of the file names, and verify that the file location is correct.

    Any help is appreciated.

    Randy



    Tuesday, December 5, 2017 4:59 AM

All replies

  • Hi RLZack,

    I am only available with Windows PC. I try to run code on that and it works correctly.

    it is possible that your above code is only able to work for Windows pc.

    I suggest you to refer code example below which is written for MAC.

    Sub Select_File_Or_Files_Mac()
        Dim MyPath As String
        Dim MyScript As String
        Dim MyFiles As String
        Dim MySplit As Variant
        Dim N As Long
        Dim Fname As String
        Dim mybook As Workbook
    
        On Error Resume Next
        MyPath = MacScript("return (path to documents folder) as String")
        'Or use MyPath = "Macintosh HD:Users:Ron:Desktop:TestFolder:"
    
        ' In the following statement, change true to false in the line "multiple 
        ' selections allowed true" if you do not want to be able to select more 
        ' than one file. Additionally, if you want to filter for multiple files, change 
        ' {""com.microsoft.Excel.xls""} to 
        ' {""com.microsoft.excel.xls"",""public.comma-separated-values-text""}
        ' if you want to filter on xls and csv files, for example.
        MyScript = _
        "set applescript's text item delimiters to "","" " & vbNewLine & _
                   "set theFiles to (choose file of type " & _
                 " {""com.microsoft.Excel.xls""} " & _
                   "with prompt ""Please select a file or files"" default location alias """ & _
                   MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
                   "set applescript's text item delimiters to """" " & vbNewLine & _
                   "return theFiles"
    
        MyFiles = MacScript(MyScript)
        On Error GoTo 0
    
        If MyFiles <> "" Then
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
            End With
    
            MySplit = Split(MyFiles, ",")
            For N = LBound(MySplit) To UBound(MySplit)
    
                ' Get the file name only and test to see if it is open.
                Fname = Right(MySplit(N), Len(MySplit(N)) - InStrRev(MySplit(N), Application.PathSeparator, , 1))
                If bIsBookOpen(Fname) = False Then
    
                    Set mybook = Nothing
                    On Error Resume Next
                    Set mybook = Workbooks.Open(MySplit(N))
                    On Error GoTo 0
    
                    If Not mybook Is Nothing Then
                        MsgBox "You open this file : " & MySplit(N) & vbNewLine & _
                               "And after you press OK it will be closed" & vbNewLine & _
                               "without saving, replace this line with your own code."
                        mybook.Close SaveChanges:=False
                    End If
                Else
                    MsgBox "We skipped this file : " & MySplit(N) & " because it Is already open."
                End If
            Next N
            With Application
                .ScreenUpdating = True
                .EnableEvents = True
            End With
        End If
    End Sub
    
    Function bIsBookOpen(ByRef szBookName As String) As Boolean
    ' Contributed by Rob Bovey
        On Error Resume Next
        bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
    End Function

    Reference:

    Programmatically Selecting Files in Excel for Windows and Excel for the Mac

    other external references:

    Opening files in Excel VBA on a mac

    Use an Open File dialog in VBA with both Mac and Windows

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 5, 2017 7:16 AM
    Moderator
  • Hi RLZack,

    I can see that you did not follow up this thread, after my last post.

    Is your issue solved?

    if yes I suggest you to post the solution and mark it as an answer.

    if your issue is still exist then try to refer my last suggestion.

    if you have any further questions then let us know about that.

    we will try to provide you further suggestions to solve the issue.

    I suggest you to take appropriate action for this thread will be helpful to other community members in future who will have same kind of issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 14, 2017 2:25 AM
    Moderator