Runtime error 1004 on a Mac

[barnetto]

Hi,

I created the macro below to export sheets created within a workbook to individual csv files. Once I got it working, it worked flawlessly. I transferred it to my windows PC to check it worked correctly there and it did. When using it today on my mac, it wasn't working and once I removed the error handler I could see that it was generating "run-time error '1004': method 'saveas' of object'_workbook' failed.". In some cases, it locks excel up and I have to force close it and start again. Its still working on the Windows PC, which adds to the confusion. Any help will be appreciated!

Please Login or Register  to view this content.

[romperstomper]

Does the filename look correct when it fails?

I'm slightly surprised it works in Windows as you're using a forward slash rather than backslash in the paths. (Application.Pathseparator would be safer)

[barnetto]

Thank you for the tip! I've updated my code to use that, but the error is still occurring.

The path for the new workbook is "/Users/barnetto/Desktop/ABC1 Sorter tables/ABC1 FlatSorter1", which is what I'm expecting.

Updated code:

Please Login or Register  to view this content.

[romperstomper]

Is there already a CSV file by that name?

I assume from your posts that this code used to work on your Mac?

[barnetto]

There isn't already a CSV file with that name. The macro is creating a new folder in the same path as the xlsm file that the sheets and macro are in. The folder is being created, then it copies the sheet to a new workbook, but the error occurs when it tries to save it.

It was working last Friday when I created the macro, which took some trial and error. Once I had it working as I wanted, I moved it to my Windows PC to check it worked there and it did. When I tried it out again this morning on my mac, it didn't work. But it still works on my Windows PC.

[romperstomper]

I just tested in 16.47 and it works OK for me.

[barnetto]

I upgraded from 16.45 to 16.47 and tried again, but it still creates the same error. If it works for you then it must be some local issue I'm having.

[romperstomper]

Are you saving locally - i.e. to a location on disc not the cloud?

I'm assuming you've dealt with the sandboxing permissions.

[barnetto]

I'm opening the file on my desktop. Then it should create the new folder and csv files within the same path, so my desktop.

However, I do store versions of this file in the cloud and yesterday morning I opened it directly from there and tried to run the macro without realising. It's as if that doing that has broken something within the file.

Tried it on two colleagues Mac's and they get the same error as me.

I haven't dealt with the sandboxing permissions.

[barnetto]

Ok so I've narrowed it down the the file format being set as a CSV. When it's set to xlOpenXMLWorkbook, it works as expected. See code below thats working.

Please Login or Register  to view this content.