Feb 06 2022 10:10 PM
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
Feb 07 2022 08:49 AM
Feb 08 2022 04:06 AM
Feb 08 2022 05:44 AM
Feb 08 2022 09:21 AM
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?
Feb 08 2022 08:33 PM
@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.
Feb 08 2022 08:43 PM
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.
Feb 09 2022 01:05 AM
Feb 09 2022 02:11 AM
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.
Feb 09 2022 04:35 AM
SolutionFeb 09 2022 06:19 AM
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.
Feb 09 2022 08:25 AM