Excel VBA and OneDrive SaveCopyAs

[coltartjmcsa3]

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 the original file clear of data. The project was developed using workstation (Acer Laptop) based files and worked as expected. I needed to allow others share access so started to use OneDrive. Opening the workbook from OneDrive generated an error at the line ActiveWorkbook.SaveCopyAs lcfile. Looking at the file path obtained not surprising.

My question is how can I code to obtain a valid file save path when using the online version of the workbook OR is there another way to what I want?

The test code is:

Sub test()
Dim lcfilesavepath, lcfile As String
Protection (False)
Application.DisplayAlerts = False
lcfilesavepath = Application.ThisWorkbook.Path & "\Archive\"
'Opening the diddly file from LapTopUser with OneDrive closed gives
lcfilesavepath=C:\Users\Owner\OneDrive\Diddly_Share\Archive\
'lcfilesavepath = ""
'If lcfilesavepath is not used the file is saved to C:\Users\Owner\OneDrive\Documents
lcfile = lcfilesavepath & Range("Title").Value & ".xlsm"
'If the diddly file is opened from OneDrive the Filesavepath = https://d.docs.live.net/61298770340f/Diddly_Share|Archive
'and the next line fails using lcfilesavepath https:\\... or null
ActiveWorkbook.SaveCopyAs lcfile
lvans = MsgBox("The workbook has been saved to file " & lcfile)
Application.DisplayAlerts = True
Protection (True)

End Sub

[#2]

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

[#3]

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.

[#4]

I suspect you missed the point. SaveCopyAs does not work for OneDrive folders. you have to use SaveAs instead of SaveCopyAs.

[#5]

Hi@Jan Karel Pieterse 

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?

[#6]

@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.

[#7]

@Jan Karel Pieterse 

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.

[#8]

In your code the filename is never assigned, lcFile is an empty string.

[#9]

@Jan Karel Pieterse 

I can't apologise enough. This will be my third try at sending you the correct info. The code works when opening the Excel on my laptop when it has access to the resident file locations. When I open Excel from the online view of OneDrive the code fails with the message shown in the attached .gif. I don't see a way round this and I may have to be satisfied by making the archive file "manually" using the Save As option resident in Excel. As I sometimes reply on others to operate the workbook I would have preferred coding. Perhaps I need to find another route to a solution.

[#10 · Accepted solution]

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.

[#11]

@Jan Karel Pieterse 

Yes I understand all that. The problem is that the sharer would have to provide the path (we could agree one in advance) and then move the file into OneDrive "manually" (can't see a way to provide a path to OneDrive). As I said earlier I need to find another way round this.

I appreciate your help and have gained some more knowledge on the way.

This is my first time of posting on this site so don't know how to close this out.

[#12]

I was assuming the file already is on a OneDrive folder. So ActiveWorkbook.Path would give you that.