Run time 1004 error on Mac

[Matt_O]

Hi Excel Gurus,

I'm back with VBA challenges working between Mac and Windows machines. Earlier I encountered simple formatting errors such as an angled apostrophe and one that goes vertical that made my code unresponsive. Now I have some new challenges that are baffling me. All code is created on a Windows machine.

The code asks the user to select a target workbook. All sheets from target are copied to master macro workbook for processing. The target workbook name is captured along with a date.

On Macs running Excel v 15.29 I get a run time error 1004 after the target workbook is selected in the Open Dialog. Here's the error and why I'm concerned there is a format problem. The cancel portion works and the message box appears as it should saying 'Operation Cancelled'.

Are there different formats of quotation marks that might cause this error?

But on a Mac with Excel v 15.24 the code operates as it should and the target workbook's sheets are successfully copied to the master workbook and the user can enter the date.


Run-time error 1004

" could not be found. Check the spelling of the file names, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.


Here's the code.

Sub OpenTarget()
    
  ' I created an If/Then statement in order to determine the variable subQuit value used elsewhere.
 
    Dim FileToConsolodate As Boolean
    FileToConsolodate = Application.Dialogs(xlDialogOpen).Show
    If FileToConsolodate Then
         'MsgBox "File Opened"
         subQuit = 0
        Else
         MsgBox "Operation Cancelled"
         subQuit = 1
         Exit Sub
        End If
           
    
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    'Now opened workbook name is captured here as public string. Short book name is also captured- losing last 14 characters
    
    GetTargetName = ActiveWorkbook.Name


    'shortening the name by removing the dates for version control- this will allow of auto-populate the Save As dialog.
    
    ShortTargetName = Left(GetTargetName, Len(GetTargetName) - 14)
    
    


    
    newdate = InputBox("Enter Today's Date")
     
 
    file_name = ShortTargetName & "_" & newdate




    'copy each sheet to MasterScript
    
    Dim wb1 As Workbook
    Set wb1 = Workbooks(GetTargetName)
    Dim wb2 As Workbook
    Set wb2 = Workbooks("MasterScript.xlsm")


        
        Dim i As Long
            For i = 1 To wb1.Sheets.Count
            wb1.Sheets(i).Copy After:=wb2.Sheets(wb2.Sheets.Count)
        Next


     
    ' create an Index page. Count sheet starting at sheet 2 and placing Index at sheet 2 to keep 1 the home page with buttons
    
   
	wb2.Activate
     
        Application.DisplayAlerts = False
            If ActiveSheet.Name = "Index" Then
            ActiveSheet.Delete
            End If
        Application.DisplayAlerts = True
    
    


  
    Dim myCount As Long, writeRow As Long




        
        writeRow = 2
        
        ' new sheet is created call Index
        
        Worksheets.Add Before:=Sheets(2)
        Sheets(2).Name = "Index"
        
        'adding column header in A1.
        
        Range("A1").Select
        Selection.Value = "Tab Index"
        
        
        
            For myCount = 3 To Sheets.Count
                Sheets("Index").Hyperlinks.Add Anchor:=Range("A" & writeRow), Address:="", SubAddress:="'" & Sheets(myCount).Name & "'!A1", TextToDisplay:=Sheets(myCount).Name
        writeRow = writeRow + 1
        
    Next myCount
 
 
 
    Workbooks("MasterScript.xlsm").Activate
    
    
    Worksheets.Add Before:=Sheets(2)
    Sheets(2).Name = "RecordingScript"
    
    Range("A1").Select
    ActiveCell.Value = "File Path"
    
    Range("B1").Select
    ActiveCell.Value = "File Name"
    
    Range("C1").Select
    ActiveCell.Value = "Recording Prompts"
    
    Range("D1").Select
    ActiveCell.Value = "Notes"
    
    
    
    Call anotherModule
    
    Call anotherModule2
    
    
    Workbooks("MasterScript.xlsm").Activate
    
    Sheets(3).Activate
  
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

Any thoughts on this are greatly appreciated.

Thanks in advance,
Matt

 

[rlzack]

Hey Matt_O,

Did you ever get a response to this, or figure it out some other way? I am having the same problem, and am hoping that you know how to solve it by now (a year after you originally posted...)

Thanks,

 

[Matt_O]

Hi rlzack,

Wow. I completely forgot about this problem. I never received a response nor could I find a solution on-line.

In the end I had to use a hack. The Mac user needed to open the workbook hosting the macro and the 'target' workbook containing the data to be manipulated. In order for this to work I had to write code to remind the Mac user to close all other workbooks besides the two required documents before continuing processing the data. This was a sloppy work around but it did the trick.

I'm still baffled why my code worked on older versions of Excel running on a Mac but not v15.29

Hopefully there is some type of work around for you.

Cheers,
Matt