Onedrive VBA Issues

[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

[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.

[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]

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

[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]

Thanks for the tips :)
Rich