Onedrive VBA Issues

richlocus
2StarLounger
Posts: 147
Joined: 03 Oct 2015, 00:30

Onedrive VBA Issues

Post by richlocus »

Hello:
I am having an issue with an application I wrote for a client that uses the DIR command. It reads each Excel (xlsx) file, extracts data without updating the file, and closes the file before it reads the next file in the directory. If the directory contains 20 files, then it opens and closes all 20 files, one file in each cycle.

The client has all his files linked to One Drive, and the little autosave slider button at the top of the 20 Excel files is turned on.

In my VBA code, I use this method to close the files:

wkbMasterWorkbook.Close SaveChanges:=True

It throws an error when executing that statement. How can I avoid this error that provents the program from continuing?

Rich Locus

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Onedrive VBA Issues

Post by HansV »

I don't have a solution for you, but I had some experiences recently that lead me to believe that there are serious problems with trying to run VBA in files saved to OneDrive.
Hopefully someone else knows how to handle that.
Best wishes,
Hans

User avatar
Jay Freedman
Microsoft MVP
Posts: 1309
Joined: 24 May 2013, 15:33
Location: Warminster, PA

Re: Onedrive VBA Issues

Post by Jay Freedman »

I'll start by saying that I didn't get an error when I tested similar code. However, I modified a Word document instead of an Excel workbook, and that document lives in a subfolder of the local OneDrive folder (I don't know where your workbook is).

Still, I think the workaround in the macro below should avoid the error you see. It copies the file from OneDrive to a folder that is not synchronized, makes the change, saves the temporary copy, and finally copies that to the OneDrive folder. By making the changes outside of OneDrive's sync, it avoids any complications that AutoSave might create.

Note that this macro needs a reference to the Microsoft Scripting Runtime in order to do early binding.

Code: Select all

Sub ModFileInOneDrive()
    Dim fs As FileSystemObject
    Dim strPath As String
    Dim strTemp As String
    Dim strFN As String
    Dim oDoc As Document
    Set fs = New FileSystemObject

    strPath = "C:\Users\Jay\OneDrive\Transfer\Documents\"
    strTemp = "D:\temp\holder\"
    
    ' for testing, I modified only one file,
    ' but you can use the properties and methods of
    ' the FileSystemObject to iterate through all
    ' the files in a specified folder
    strFN = "loremy.docx"
    
    fs.CopyFile _
        Source:=strPath & strFN, _
        Destination:=strTemp & strFN
        ' OverWriteFiles defaults to True
    
    ' operate on temp copy
    Set oDoc = Documents.Open(strTemp & strFN)
    oDoc.Range.InsertBefore "Modified this document" & vbCr
    oDoc.Close savechanges:=wdSaveChanges
    
    fs.CopyFile _
        Source:=strTemp & strFN, _
        Destination:=strPath & strFN
    
    ' clean up
    fs.DeleteFile strTemp & strFN
End Sub

richlocus
2StarLounger
Posts: 147
Joined: 03 Oct 2015, 00:30

Re: Onedrive VBA Issues

Post by richlocus »

Jay:

Thanks. Your solution worked. I copied the files to C:\TempWorkaround\ and made the changes outside of the realm of One Drive. I just ordered a book on OneDrive because somehow my client integrated one drive folders into the main file structure of Windows. One drive folders show up just as a regular Windows File Explorer file within the C:\ realm. This client has most of his files residing in OneDrive space and he never backs up his computer.

Thanks again Jay. Now I am able to send an invoice to my client. I spent so much time trying to do a workaround that I'm not billing them for that time.

Rich Locus

User avatar
Jay Freedman
Microsoft MVP
Posts: 1309
Joined: 24 May 2013, 15:33
Location: Warminster, PA

Re: Onedrive VBA Issues

Post by Jay Freedman »

Happy to help!

The "somehow" you mentioned is the standard configuration that Microsoft 365 creates on installation. All of the "special" folders (Documents, Music, Pictures, and Videos) in the C:\Users\[username] folder are automatically wrapped inside the OneDrive folder, which is synced to the user's cloud account.

The assumption is that every Microsoft 365 subscriber, like your client, wants all of their data automatically uploaded to the cloud and not stored locally. If that interferes with customizations, or if you just don't like the idea of all your data being stored somewhere that may be out of reach at an inopportune time, you have to do some reorganization. You can go into OneDrive's settings and specify which folders to sync, or designate some files to be kept both locally and in the cloud. Alternatively, you can move the special folders outside the OneDrive folder and manually copy individual files to the sync folder when you want them to upload. I'd guess that less than 1% of non-corporate users do any of that.

richlocus
2StarLounger
Posts: 147
Joined: 03 Oct 2015, 00:30

Re: Onedrive VBA Issues

Post by richlocus »

Thanks for the tips :)
Rich