+ Reply to Thread
Results 1 to 8 of 8

MacOS VBA will not SaveAs CSV

  1. #1
    Registered User
    Join Date
    04-23-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Excel for Mac 16.48
    Posts
    4

    MacOS VBA will not SaveAs CSV

    Thanks for reading!

    For weeks now, I have been trying to write a macro to save a bunch of sheets as CSVs from a calculation workbook. The interwebs are rife with people talking about this problem, but sadly unrife with solutions.

    I have developed a test scenario that wants to save a single sheet with 8 1-character cells from a 1-sheet workbook.
    It has two identical macros - one saves xlsx files and the other (fails to) save CSV files.
    I have tried to attach it to this message.

    The XLSX option popped up the Grant Access dialog for the test folder on the desktop. The CSV option never asks, even when I create a new folder for it.

    The perplexing thing is that it works for xlsx files but not CSV.

    I have tried a bunch of stuff suggested by various fora:
    • Use number 6 instead of label xlCSV
    • Use ThisWorkbook instead of ActiveWorkbook
    • Change target to mycsvpath = "/Users/me/Library/Containers/com.microsoft.Excel/Data/" & ActiveSheet.Name & ".csv"
    Result are always the same.

    Run-time error '1004':
    The file could not be accessed. Try one of the following:
    - Make sure that the specified folder exists
    - Make sure that the folder that contains the file is not read-only.

    It exists and is not read-only because the save-as-XLSX variant of the macro works flawlessly.
    I suspect that it is something to do with Sandboxing, but the first rule of sandboxing is that no-one ever talks about sandboxing. And - it works for xlsx.

    Mac OS is Mojave, Excel is 16.48, VBA is 7.1

    Any thoughts?

    Thanks for you help,
    Russ
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: MacOS VBA will not SaveAs CSV

    I don't use Mac Excel.

    That disclaimer made, what happens if you try to run the following code?

    Please Login or Register  to view this content.
    If that works, produces the indicated text file, then it's a mystery to me why Excel is choking on writing CSV files in the same directory.

    What happens if you create a new subdirectory under /Users/me and try saving the CSV files there? I ask to find out whether Excel/VBA can save worksheets as CSV files somewhere. If it can't save CSV files anywhere, then it'd seem this would be a problem only MSFT could solve.

  3. #3
    Registered User
    Join Date
    04-23-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Excel for Mac 16.48
    Posts
    4

    Re: MacOS VBA will not SaveAs CSV

    Nice idea! And - your macro works.

    The file is created in the Data folder.

    That's gotta be a step in the right direction.
    1. I inow the syntax I am using is correct for xlsx files
    2. I know VBA can write a text file to the Library's Data folder
    3. Just for laughs, I also know VBA can write a text file with a .csv suffix to the Library's Data folder
    4. And in the interests of science and fact-based belief systems, I adjusted the save-as-xlsx macro to save here too - and it works, creating a valid xlsx file.
    5. But SaveAs CSV to this folder does not work

    Thanks for your help!
    What next?
    Attached Images Attached Images
    Last edited by Ploughguy; 04-24-2021 at 12:50 AM.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: MacOS VBA will not SaveAs CSV

    I was trying not to have to open your workbook, but I did. Your code:

    Please Login or Register  to view this content.
    You're initializing one variable named mycsvpath but using another, presumably uninitialized variable named csvpath in the .SaveAs statement. That's likely to be your problem.

    Now for advice, the cost of free support: put Option Explicit at the top of your VBA module in order to catch errors like this at compile time. This would have been an easy error to diagnose and fix if you had to declare all variables. That may seem like a burden, but it can save huge amounts of time.

  5. #5
    Registered User
    Join Date
    04-23-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Excel for Mac 16.48
    Posts
    4

    Re: MacOS VBA will not SaveAs CSV

    Noooo! It can't be true!

    I tried sooo many things that this was probably inevitable. I was twitchy that csvpath might be a system variable (I know, right? but I was running out of sensible things to try), so I changed it, with Saturday-approprate incompetence.
    So thank you, hrlngrv, for spotting this. I introduced that error about an hour before I posted the message, at precisely the time when I started trying to write to the Library - Data folder.

    So the current state of play is:
    - can save CSV files, but only to a directory that Apple really doesn't want you to see, (given that it requires magic tricks in Finder - Press Option, click menu item Go to access the Library folder.)
    - can create a folder inside Data and save there. There must be a trailing slash on the path in the macro.
    - xlsx files will generate a GRANT ACCESS dialog when you save them to the location of your choice, but CSV files do not.

    Outstanding issues
    Can anyone explain why this restriction is in place?

    Handy Tip
    To save all the option-Go clicking, Finder will let you drag the Data directory to the sidebar. Then, of course, you have a sidebar entry named "Data" which is a bit obscure. I created a folder "TaxCSVs" in Data and dragged that to the Sidebar. This provides convenient access to the CSV's after export without requiring me to remember obscure paths.

    How-to
    As a public service, this is the minimal macro required to save the CSV:
    Please Login or Register  to view this content.
    Last edited by Ploughguy; 04-24-2021 at 02:15 AM.

  6. #6
    Registered User
    Join Date
    04-23-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Excel for Mac 16.48
    Posts
    4

    Re: MacOS VBA will not SaveAs CSV

    Now for advice, the cost of free support: put Option Explicit at the top of your VBA module in order to catch errors like this at compile time. This would have been an easy error to diagnose and fix if you had to declare all variables. That may seem like a burden, but it can save huge amounts of time.
    Of course. Absolutely. I hang my head in shame. I am a million years old, and started with strongly-typed languages like Pascal and C. Then I strayed into PHP. This has been my downfall. I am suitably chastised - I will ask my wife to give me a good slapping when she gets home, just for emphasis. I have written "Option Explicit, Stupid" on a post-it and stuck it to my screen. It should hang on there long enough for me to remember it. Of course, I also have to remember to leave the last word out.

    My thanks again, hrl. You are good people.

  7. #7
    Registered User
    Join Date
    08-25-2023
    Location
    TX, USA
    MS-Off Ver
    365
    Posts
    2

    Re: MacOS VBA will not SaveAs CSV

    After a lot of research (and failed answers), I came up with a simple solution. You must save the file into the official Office folder on your computer (this is a known working solution mentioned many times on various forums). BUT... you can then use the following command to "move" the file out of this folder to wherever you originally wanted it. For example, I wanted the .csv files to be saved to the desktop. I got the 1004 runtime error all the time. Now, I save to the Office folder as a .csv and then just move it to the desktop via this code. No need to first save it as an .xls as some solutions have shown.



    ** Please note that your office folder location might be different **



    Name "/Users/[Username]/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Startup.localized/Excel/[filename].csv" As "/Users/[Username]/Desktop/[filename].csv"

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: MacOS VBA will not SaveAs CSV

    That's kind of you, but the thread is almost three years old.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. VBA Not Working on MacOS
    By recalling in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-09-2020, 05:50 PM
  2. Excel Addin on MacOS
    By lollapalooza in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-26-2019, 12:42 PM
  3. Plugin Compatibility - Windows to MacOS
    By Edsin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2019, 06:25 PM
  4. macOS 10.14 Mojave will be 64 Bit
    By mitchvail in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 09-06-2018, 02:16 AM
  5. [SOLVED] My macos won't run on the next row (it only works on the first row)
    By hcyeap in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-02-2013, 03:25 PM
  6. Macos If then Statement help
    By bapella in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-12-2012, 02:42 PM
  7. [SOLVED] Diabling Target Workboon Macos (if necessary)
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2006, 09:15 AM

Tags for this Thread

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