0

So I've tried looking around the net and forums for solving this but since I can't seem to understand this at all, I need a bit of advice.

I have a Macro that I used to run on Excel 2011 on Mac that I do not have anymore. When I try to run this on Excel 2016 on Mac, I am getting a Run-time Error '1004', Sorry we couldn't find (file location). Is it possible that it was moved, renamed etc error.

The Code is per below

On Error Resume Next
MyPath = MacScript("return (path to documents folder) as String")
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)

            X = ActiveWorkbook.Name
            Workbooks.Open (MySplit(N))

When I see the debug, it stops at the code Workbooks.Open (MySplit(N))
The file that its looking for exists in the location because I have to choose the file when it prompts me

Wondering if theres anyone that can help me out on what code to replace Thank you

Shoujiki
  • 1
  • 2
  • Thanks, updated for coding instead of screenshot – Shoujiki Jun 19 '20 at 13:06
  • Have you debugged the code? What is the content of `MyFiles`, `MySplit`, `N` and `MySplit(N)` at the moment the error occurs? Does the file `MySplit(N)` exists and is it allowed for the process to read it? Usually I would trust the error msg that the file is missing. – FunThomas Jun 19 '20 at 13:25
  • Is MySplit an array of full paths, or just file names? If just filenames then you should include the path when trying to open one of them. – Tim Williams Jun 19 '20 at 16:31
  • @Shoujiki I'm following exactly the same code and having exactly same issue running 365 on mojave. `MyFiles` is set to `Macintosh HD:Users:xxxx:Documents:yyyy.xlsx`. It's a single user environ & file clearly there. – rockhammer Jul 04 '20 at 16:32

1 Answers1

0

Following Mac Excel 2016 VBA - Workbook.open gives 1004 error, just modify output of applescript for file selection (which is something like Macintosh HD:Users:xxxx:Documents:yyyy.xlsx) by replacing : with / and Macintosh HD with "" enables Workbooks.Open() to work.

rockhammer
  • 957
  • 2
  • 13
  • 38