Forum Discussion
Excel VBA and OneDrive SaveCopyAs
- 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.
JKPieterse 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
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 lcMySubEnd 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.SaveEnd Sub 'Do Archive
- 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
