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.

[#13]

@Jan Karel Pieterse I have this same problem, and I do have the full path as you said.  I can read from my OneDrive, but when I go to save it fails.  It works fine locally.  Here is a snippet:

Dim wb_NewData As Workbook
Set wb_NewData = openWorkbook("Choose a workbook to reformat")

wb_NewData.SaveAs Filename:=wb_NewData.Path & "/" & "Reformat- " & wb_NewData.Name

[#14]

@Mark_Kessler

After a lot of head scratching I have found a way round the CopyAs issue. The workbooks attached contain macros stripped down fron the full coding in my main application. My AppBook.xlsm opens an "auxiliary" workbook AuxBook.xlsm which then SaveAs AppBook.xlsm to ArcBook.xlsm in (in my case) the Archive folder and returns to AppBook.xlsm which then runs the macro ClearData. I hope this may be relevant to your issue

Can't find means of attaching workbooks so the code is below:

AppBook.xlsm

Sub StartArchive()

Dim lcFileName, lcFilePath, lcMySub As String

lcFilePath = ActiveWorkbook.Path & "/"
Workbooks.Open Filename:=lcFilePath & "AuxBook.xlsm"
lcMySub = "'" & lcFilePath & "AuxBook.xlsm'!module1.DoArchive"
Application.Run lcMySub
lcFilePath = ActiveWorkbook.Path & "/"
Workbooks.Open Filename:=lcFilePath & "AppBook.xlsm"
lcMySub = "'" & lcFilePath & "AppBook.xlsm'!module2.ClearData"
Application.Run lcMySub

End Sub

AuxBook.xlsm

Sub DoArchive()

Dim lcDiddlyFile, lcDiddlyPath, lcArchFile, lcArchPath As String
Dim lvAns As Variant
Application.DisplayAlerts = False
Application.ScreenUpdating = False
lcDiddlyPath = Application.ActiveWorkbook.Path & "\"
lcDiddlyFile = ActiveWorkbook.Name
lcArchPath = lcDiddlyPath & "Archive\"
lcArchFile = "ArcBook.xlsm"
Application.Workbooks("AppBook.xlsm").SaveAs FileName:=(lcArchPath & lcArchFile)
ActiveWorkbook.Save

End Sub 'Do Archive

[#15]

Had the same problem
I found this workaround.
You simply need to relocate the URI file to local computer.

Example : URI of a OneDrive localized file = "https://d.docs.live.net/b034d490c9cb9445/Documents/50 - Working"

You have to rebuild the local path by replacing "https://.../.../" with local path. To do this you can get the OneDrive Path from the local environnement in vba with ENVIRON command => ENVIRON("OneDrive") return the local path of OneDrive

Example here. Adapt it as you need :)

Public Function fRelocateOneDrivePath(strParamURIPath As String) As String

Dim strPath As String
Dim I As Integer

' Replace all "/" by "\" if exists
strPath = Replace(strParamURIPath, "/", "\")

If Left(strParamURIPath, 4) = "http" Then

' Remove the OneDrive URI part of the path : ex : "https://d.docs.live.net/b034d490c9cb9445/Documents/50 - Working"
For I = 1 To 4
strPath = Mid(strPath, InStr(strPath, "\") + 1)
Next

' Return path with local OneDrivePath
fRelocateOneDrivePath = Environ("OneDrive") & "\" & strPath

Else
' Do Nothing
fRelocateOneDrivePath = strParamURIPath
End If

End Function