My error is popping up under a unique circumstance. The macro (code below) is working on the computer (Mac, Excel 2016) where I created it. When I email the excel file with the macro to someone else, and they try to run it, they receive an error after selecting the input file (Run-time error '1004': Method 'Open' of object 'Workbooks' failed).
Ignore the dstFile variable and sub calls. The macro performs some basic data organization. The error happens before any of this can occur, and only when opened/used by another user on another machine.
- I made sure neither the file with the macro, nor the input file are "read only".
- I made sure both files were opened/used from identical folder names.
- Excel versions match between both users.
- OS X versions match between both users.
My brain is fried because the solution is probably obvious, but I have no idea what I'm missing.
Sub BuzzGrid()
Dim mainWb As Workbook
Dim sourceWb As Workbook
Dim MyPath As String
Dim MyScript As String
Dim MyFiles As String
Dim Fname As String
Dim srcFile As Worksheet
Dim dstFile As Worksheet
Set mainWb = Application.ActiveWorkbook
Set dstFile = mainWb.Sheets(1)
Fname = GetFileName()
If Fname > " " Then
Workbooks.Open Fname
Set sourceWb = ActiveWorkbook
Set srcFile = sourceWb.Sheets(1)
srcFile.Rows("1:3").EntireRow.Delete
srcFile.Columns("A:D").Sort key1:=Range("B1"), key2:=Range("D1")
Call InitialValues(dstFile)
Call FillPhone(dstFile, srcFile)
sourceWb.Close
End If
End Sub
Function GetFileName()
On Error Resume Next
MyPath = MacScript("return (path to documents folder) as String")
'Or use MyPath = "Macintosh HD:Users:Sean: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"",""public.comma-separated-values-text""} " & _
"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
Application.DisplayAlerts = False
Fname = Right(MyFiles, Len(MyFiles) - InStrRev(MyFiles, Application.PathSeparator, , 1))
Fname = Mid(Fname, 13)
Fname = Replace(Fname, ":", "/")
GetFileName = Fname
End Function