3

I'm trying to get a macro that works perfectly in Excel 2011 for Mac to work in Excel 2016 for mac. The goal is to have the user specify a folder with .csv files in it, and then the macro loops through all the .csvs, opening each to copy information out of it into another Workbook.

The macro fails when trying to open the first .csv file in the user-chosen folder, with a 1004 error, file cannot be found.

(side note: Earlier in the macro, workbooks.open works perfectly with a user selected FILE)

The macro is huge, so I made a brand new smaller one just to get past this problem. Here is the smaller, test code, which has the same failing behavior:

Sub Test()
Dim folderpath As Variant
Dim filename As Variant
Dim newfilename As Variant
Dim wb As Workbook
Dim newfolderpath As Variant

folderpath = MacScript("choose folder as string")

newfolderpath = Replace(folderpath, ":", "\")

MsgBox (newfolderpath)

filename = MacScript("Choose file as string")
newfilename = Replace(filename, ":", "\")
MsgBox (filename)
MsgBox (newfilename)
MsgBox (Dir(filename))
MsgBox (newfolderpath & Dir(filename))

Set wb = Workbooks.Open(newfolderpath & Dir(filename))
End Sub

All the msgboxes provide expected values.

  • newfolderpath= the whole path with \ separators.
  • filename= the whole path and file name with : separators.
  • newfilename= the whole path and file name with \ separators.
  • Dir(filename)= just the file name.
  • newfolderpath & Dir(filename))= the entire path and file name with \ separators.

Set wb line gives: 

Run-time error '1004'
'Hard DRIVE\USERS\DAVE\DESKTOP\CSVS\1.CSV'

could not be found.

Clearly the file is there, openable and works.

Any help is greatly appreciated.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Dave
  • 63
  • 1
  • 5
  • I thought the path separator on Max was either ":" or "/" - does backslash really work? – Tim Williams May 02 '17 at 23:24
  • Doesn't the `Dir` function require `MacID("TEXT")` as a parameter in {vba-mac]? See [this](http://stackoverflow.com/questions/10045474/dir-function-not-working-in-mac-excel-2011-vba). –  May 02 '17 at 23:34
  • Bonus points for code that is [a brand new smaller one]. Nice [mcve]. –  May 02 '17 at 23:38
  • @Tim - Well, the backslash is what is actually returned by the msgbox, and my understanding is, yes, Excel 2016 uses them. However, I'm open to suggestions, as, clearly, SOMEthing is not working. – Dave May 03 '17 at 00:04
  • @Jeeped - I saw the post you referenced, and I used part of that code in my original working copy of the 2011 version. However, if I try to use MacID("TEXT") with Dir in this code, it returns a null string. If I remove the MacID, it at least returns the correct file name. It just can't open it. – Dave May 03 '17 at 00:06
  • @Tim - sorry, I meant to also add that, I tried with forward slashes, as you suggested, and exact same error. – Dave May 03 '17 at 00:08
  • 1
    OK then `debug.print newfolderpath & Dir(filename)` then record yourself using File, Open on the same work book and compare the two results. –  May 03 '17 at 00:09
  • @Jeeped - debug.print = Hard Drive\Users\Dave\Desktop\csvs\1.csv but, code still gives: Run-time error '1004': 'HARD DRIVE\USERS\DAVE\DESKTOP\CSVS\1.CSV' -- any reason the error is in all caps? Meanwhile, when I record a macro, it does not record the file/open operation. – Dave May 03 '17 at 00:21

1 Answers1

2

Well, I think I found the answer, or at least an answer that works for my project.

I went back to the workbooks.open(user-selected FILE) from earlier in the macro, and discovered it was reading it as "/users/Dave/Desktop/csvs/1.csv". So, even though debug and msgbox were returning backslashes, it was somehow wanting forward slashes (You were partially right, Tim). Additionally, it clearly truncates the name of the Mac hard drive, and starts with the users directory. So, I just modified the variables to match this format, and voila, it worked. For those who are interested, here's the modified code I wound up using:

Sub Test()

Dim folderpath As Variant
Dim newfilename As Variant
Dim wb As Workbook
Dim newfolderpath As Variant
Dim newfp As Variant

folderpath = MacScript("choose folder as string")
newfolderpath = Replace(folderpath, ":", "/")
newfp = Right(newfolderpath, Len(newfolderpath) - InStr(newfolderpath, "/") + 1)

newfilename = Dir(newfp)

Set wb = Workbooks.Open(newfp & newfilename)

Exit Sub

Thanks to everyone that helped.

Dave
  • 63
  • 1
  • 5
  • you idea of replacing `:` with `/` works for me without having to do anything with file path. In my case applescript to select file was returning `Macintosh HD:Users:xxxx:Documents:yyyy.xlsx`. All I had to do was to replace `:` with `/` and `Macintosh HD` with `""` to make `Workbooks.open()` work. thx – rockhammer Jul 04 '20 at 16:59