+ Reply to Thread
Results 1 to 10 of 10

Runtime error 1004 on a Mac

  1. #1
    Registered User
    Join Date
    02-22-2021
    Location
    Wakefield, England
    MS-Off Ver
    Excel for Mac v16.45
    Posts
    8

    Runtime error 1004 on a Mac

    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.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,428

    Re: Runtime error 1004 on a Mac

    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)
    Rory
    I drink, and I know things

  3. #3
    Registered User
    Join Date
    02-22-2021
    Location
    Wakefield, England
    MS-Off Ver
    Excel for Mac v16.45
    Posts
    8

    Re: Runtime error 1004 on a Mac

    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.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,428

    Re: Runtime error 1004 on a Mac

    Is there already a CSV file by that name?

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

  5. #5
    Registered User
    Join Date
    02-22-2021
    Location
    Wakefield, England
    MS-Off Ver
    Excel for Mac v16.45
    Posts
    8

    Re: Runtime error 1004 on a Mac

    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.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,428

    Re: Runtime error 1004 on a Mac

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

  7. #7
    Registered User
    Join Date
    02-22-2021
    Location
    Wakefield, England
    MS-Off Ver
    Excel for Mac v16.45
    Posts
    8

    Re: Runtime error 1004 on a Mac

    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.

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,428

    Re: Runtime error 1004 on a Mac

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

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

  9. #9
    Registered User
    Join Date
    02-22-2021
    Location
    Wakefield, England
    MS-Off Ver
    Excel for Mac v16.45
    Posts
    8

    Re: Runtime error 1004 on a Mac

    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.

  10. #10
    Registered User
    Join Date
    02-22-2021
    Location
    Wakefield, England
    MS-Off Ver
    Excel for Mac v16.45
    Posts
    8

    Re: Runtime error 1004 on a Mac

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Runtime Error 1004 + Runtime 5
    By jrean042 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-07-2020, 08:16 AM
  2. VBA error: runtime error 1004 : Method range of object - 'Global' failed.
    By Afjio12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2019, 04:37 AM
  3. Runtime error '1004': Paste special method of range class error.
    By Daryl Zer0 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-05-2014, 01:44 AM
  4. [SOLVED] VBA Error: Runtime Error 1004: AutoFilter method of Range class failed
    By jl22stac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 07:27 PM
  5. [SOLVED] Range error in code, runs alone but not inside my full program, giving runtime error 1004
    By charizzardd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-23-2012, 03:34 PM
  6. runtime error 1004
    By caherine in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-22-2012, 04:28 PM
  7. [SOLVED] Excel 2003 Macro Error - Runtime error 1004
    By Cow in forum Excel General
    Replies: 2
    Last Post: 06-07-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1