User Profile
coltartjmcsa3
Copper Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: Excel VBA and OneDrive SaveCopyAs
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 Archive19KViews0likes1CommentRe: Excel VBA and OneDrive SaveCopyAs
JKPieterse 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.25KViews0likes4CommentsRe: Excel VBA and OneDrive SaveCopyAs
JKPieterse 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.25KViews0likes6CommentsRe: Excel VBA and OneDrive SaveCopyAs
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.26KViews0likes12CommentsExcel 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 SubSolved27KViews0likes14Comments
Recent Blog Articles
No content to show