+ Reply to Thread
Results 1 to 6 of 6

ActiveWorkbook.SaveAs runtime error 1004 cannot access.... saving to OneDrive folder

  1. #1
    Registered User
    Join Date
    04-02-2004
    Posts
    16

    ActiveWorkbook.SaveAs runtime error 1004 cannot access.... saving to OneDrive folder

    Hi all,

    Today I tried to run a macro that used to run fine about 3 years ago but which now fails with a run-time error 1004 saying "cannot access [filename]". The line of code that fails is:

    Please Login or Register  to view this content.
    The variable FileToSave has the correct value of the activeworkbook: V702 K&C Summary.xlsx. This file was created and previously saved using the same line of code in a previous sub, so this is the second time I'm asking for the file to be saved to the same folder.

    The whole macro and all related workbooks are running locally on my machine and this workbook in question is stored at this folder address in Windows Explorer: C:\Users\My Name\OneDrive - Company Name\Desktop\K&C ASCII Data for Tracking Sheet\702_ascii (I've edited the link only to hide names).

    However, interrogating the file info shows the full file path to be:
    Please Login or Register  to view this content.
    (I've had to put this in code because I can't work out how to stop the post window from automatically wrapping it in [url]).

    The use of OneDrive and Sharepoint are probably the only changes to the way this macro functions since last using it, but I have no understanding of these things or the significance of them.

    I'm currently working off site and using a VPN, but switching that off had no effect on the problem.

    I'd appreciate any help on how to resolve this so that I can use the macro to continue to save the file repeatedly in a local folder.

    Thanks.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,974

    Re: Activeworkbook.saveas runtime error 1004 cannot access....

    When you are working in a OneDrive folder, VBA sees the Internet URL where the file is mirrored, and not the Windows folder. I have seen this a few times before and have never found a solution. I consider this a major drawback of OneDrive.

    However, you are only showing one line of your code and we might have an approach that will work with your overall problem. Why are you using SaveAs if you just need to save ActiveWorkbook using the same name it has currently into the same location?
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-02-2004
    Posts
    16

    Re: ActiveWorkbook.SaveAs runtime error 1004 cannot access.... saving to OneDrive folder

    I asked myself the same question and I think the answer was that I simply copy-pasted a small chunk of code to each routine to execute saving. Converting the subsequent SaveAs to Save was my solution whilst I waited for responses.

    I could presumably extract the full path after the first SaveAs and use that as the subsequent SaveAs path, but, as you say, why bother.

    I didn't think OneDrive and SharePoint were the same thing so that confused me greatly. Whatever the benefits of each, the problems they bring don't seem to warrant bothering with them at times.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,974

    Re: ActiveWorkbook.SaveAs runtime error 1004 cannot access.... saving to OneDrive folder

    They are not the same thing; OneDrive is the Microsoft version of DropBox or Google Drive. Same idea, but not as elegantly executed as either of those. SharePoint is a whole browser-base environment that also includes a file repository. There is some overlap in that SharePoint can have links to OneDrive files. I have OneDrive because it's bundled in with my Office subscription but I find it more trouble than it's worth. However, sometimes people have to use it because it's their company environment.

    A little anecdote: My (former) company has to send a weekly report to the federal government indicating any impact to the any project due to covid. I automated this process so a non-technical admin person could do it by pushing a button instead of preparing five Excel files every week and sending them to five different federal contracting officers. At some point she moved the file into a OneDrive folder and it stopped working, for this reason of the path. She called me in a panic and it took me a half hour to diagnose the problem. The only solution I could find was to have her move it to a non-OneDrive folder, and it immediately started working again. I have seen the same problem two other times on this and other Excel discussion boards.

  5. #5
    Registered User
    Join Date
    04-02-2004
    Posts
    16

    Re: ActiveWorkbook.SaveAs runtime error 1004 cannot access.... saving to OneDrive folder

    Ok, thanks.

    So what's still confusing me is that I'm working in a OneDrive folder but the URL seen by VBA is to SharePoint.

  6. #6
    Registered User
    Join Date
    03-05-2021
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    1

    Re: ActiveWorkbook.SaveAs runtime error 1004 cannot access.... saving to OneDrive folder

    I think as was explained by 6StringJazzer above. It simply won't work on OneDrive, either store the file on a non OneDrive folder or as I have found, use Google Drive

+ 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. ActiveWorkbook.Save Runtime Error 1004 Help!
    By 7189456 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2022, 12:50 PM
  2. [SOLVED] Saving a file to onedrive shared network and access via onedrive online
    By Sintek in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-09-2021, 11:56 AM
  3. ActiveWorkbook.SaveAs Error 1004
    By incrediblek in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-29-2019, 09:44 AM
  4. [SOLVED] Runtime error 1004 with my saveas code
    By Anto_BT in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-26-2015, 11:49 AM
  5. [SOLVED] Runtime Error '1004' Method 'SaveAs' of object'_Workbook Failed
    By Angsome in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-12-2014, 02:58 PM
  6. Runtime error '1004': cannot access...
    By rsford31 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2010, 03:58 PM
  7. Replies: 4
    Last Post: 05-04-2006, 08:00 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