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 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
Another mockup with lambdas
prefix = {"No", "No:"}; removeFirst = lambda(str, txt, SUBSTITUTE(str, txt, "",1) ); //// noPrefix = lambda(str, k, IF(k=0, TRIM(str), LET(t, removeFirst(str, INDEX(prefix,1,k)), noPrefix(t, k-1) ) )); cleanText = lambda(str, noPrefix(str, COLUMNS(prefix))); nFirstSpace = lambda( str, FIND(" ", str)-1); textStart = lambda(str, n, TRIM(LEFT(str, n))); textEnd = lambda(str, n, TRIM(RIGHT(str, LEN(str)-n))); lastPos = lambda(str, chr, n, IF(RIGHT(str)=chr, n, lastPos(LEFT(str, n-1), chr, n-1))); posRightSpace = lambda(str, lastPos(str, " ", LEN(str))); //// splitParts = lambda( str, LET( txt, cleanText(str), nA, nFirstSpace(txt), partA, textStart(txt, nA), partBC, textEnd(txt, nA), first40, textStart(partBC, 40), space40, posRightSpace(first40), partB, IF(LEN(partBC)<=40, partBC, textStart(partBC, space40) ), partC, IF(LEN(partBC)<=40, "", textEnd(partBC, space40) ), IFERROR(CHOOSE({1,2,3}, partA, partB, partC),"") ) );
14 Replies
- 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- 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.
