Forum Discussion
coltartjmcsa3
Feb 07, 2022Copper Contributor
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...
- Feb 09, 2022I 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
Feb 09, 2022Silver Contributor
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.
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.
coltartjmcsa3
Feb 09, 2022Copper Contributor
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.
- Lio_NelFeb 20, 2023Copper ContributorHad 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 - coltartjmcsa3Jan 23, 2023Copper Contributor
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
- Mark_KesslerJan 18, 2023Copper Contributor
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
- JKPieterseFeb 09, 2022Silver ContributorI was assuming the file already is on a OneDrive folder. So ActiveWorkbook.Path would give you that.