Forum Discussion
Solved
Excel VBA and OneDrive SaveCopyAs
I am running office 365 home on windows 10 both latest versions. I have a project that requires the workbook ("Diddly.xlsm") data to be archived every 6 months to a code generated file name leaving t...
- I don't know what the current value of lcFile is when the error occurs?
If you pass just a file name (no path) to the SaveAs method, Excel assumes it needs to be saved in the active directory. Judging by the error message you showed that seems to be c:\Windows. Users are not allowed to save files there. You must provide the SaveAs method with both the path and a filename.
It looks like the SaveCopyAs does not allow you to save to a onedrive location. The only way to do this is by using the SaveAs method I'm afraid. So you'll have to:
- Remember the current name
- Save as to the new location
- Remove data
- Save as to the remembered name
- Remember the current name
- Save as to the new location
- Remove data
- Save as to the remembered name
Thank you for your response. Due to my inadequate question it does not address my issue. I have no problem when codng for SaveCopyAs when working with files for which I have a storage address such as C:\users\owner\Onedrive\..... But when I try to code for files I am sharing from another's OneDrive source I can't get a useable location to save any edits to the shared OneDrive. The code Application.ThisWorkbook.Path returns a URL https://d.docs.live.net/61298770340f/Diddly_Share. which cannot be interpreted by subsequenr coding.
- I suspect you missed the point. SaveCopyAs does not work for OneDrive folders. you have to use SaveAs instead of SaveCopyAs.
re my msg 9/2/22 I attached the wrong .gif file. Correct file attached. I'm not dong too well with this problem. You probaby noticed I managed to send a msg to myself. Thanks for your patience.
- In your code the filename is never assigned, lcFile is an empty string.
I've attached Sub test() and the error displayed on running " Application.ActiveWorkbook.SaveAs (lcFile)". I'm guessing that I have something wrong with the syntax? Can you advise?
coltartjmcsa3re my msg 9/2/22 I attached the wrong .gif file. Correct file attached. I'm not dong too well with this problem. Thanks for your patience.
