CODEWITHSUNDEEP
excelvbamacos
user857521
user857521

Reputation:

Run Time Error 52 Bad File Name - Mac Only

I have the following function which produces a runtime error 52 (bad filename or number) only on Mac on the line Open filePath & filename For Output As #1

Function ExportAsCSV(ByVal charToEncode As String, _
    ByVal filePath As String, ByVal filename As String) As Boolean

'Function to Export string to CSV. Returns True/False if successful
'Tested and working on PC XL2003,2007 & 2010 // MAC XL2011

    'On Error GoTo Err:
    On Error GoTo 0


    Open filePath & filename For Output As #1
    Print #1, charToEncode
    Close #1

    ExportAsCSV = True

    On Error GoTo 0
    Exit Function

Err:
On Error GoTo 0
ExportAsCSV = False

End Function

Example argument that causes an error is:

  filePath : "Untitled:Users:ooo:Desktop:New" : String
  filename : ":FloridaRhum Barbancourt 5 Star Reserve Speciál 8 Years Old.csv" : String

It's definitely the filename causing an issue as if I change it to ":test.csv" as an example it works. All the individual words also work fine so it doesn't seem to be an encoding issue and if I generate a long filename the same length it's fine as well.

I'm stumped.. any ideas?

EDIT:

Also just to add, the Open filePath & filename For Output As #1 does actually generate a file with the correct filename in the correct location. But because the VBA generates an error nothing is written so the file is zero bytes.

Upvotes: 0

Views: 2338

Answers (2)

user2092957
user2092957

Reputation: 311

Just to add a little more to this problem

I have stumbled on the problem using vba for Word under Office 2011, so its not just Excel.

In my case it was a folder two folders back in the path that caused the problem.

I was creating and writing to a file with this path:

Macintosh HD:Users:myusername:Desktop:Data Extractor V8 copy.zip Folder:xl:Newstyles.xml

When I change the path to reduce the folder name to 31 chars ie:

Macintosh HD:Users:myusername:Desktop:Data Extractor V8 co.zip Folder:xl:Newstyles.xml

It all works fine.

Boy did that take some time to find. Its bad enough trying to make my old brain write vba without having these curlers thrown into the path (sorry for the pun)

Anyway - hope this helps others - I will post an alert through Word to Microsoft

Bob J.

Upvotes: 1

CuberChase
CuberChase

Reputation: 4518

I think you've hit a carry over bug which limits the save through VBA to 32 characters. You will still be able to create the file but won't be able to open and write to it.

I'd either write to a shorter filename while processing and as the last step either use the SaveAs method to save to a longer CSV filename or use AppleScript to rename the file. (I'm sure they'll fix it in the next version of Office ;)

Upvotes: 2

Related Questions