Abstract

Microsoft Microsoft OneDrive is a file hosting service provided by Microsoft. With the help of this service, files can be uploaded and also retrieved and edited from other services.

However, this service requires some time to synchronize files. The VBA subroutine Check_OneDrive_Sync checks whether a program has already been fully synchronized (i.e. is ready to run).

When creating or editing many files, it is sometimes advisable to temporarily disable OneDrive synchronization. This is made possible by the VBA subroutine ManageOnedriveSync.

Program Code Check_OneDrive_Sync

Please notice: This sub uses (requires) module (external link!) LibFileTools.

Please read my Disclaimer.

Sub Check_OneDrive_Sync()
'Checks whether report folder has been fully synchronized from Sharepoint to OneDrive.
'Die if not. Requires LibFileTools: https://github.com/cristianbuse/VBA-FileTools
'Version When        Who             What
'      1 24-Feb-2025 Bernd Plumhoff  Initial version.
'      2 12-Aug-2025 Bernd Plumhoff  Environ("OneDrive") instead of Environ("Username")
'      3 17-Mar-2026 Bernd Plumhoff  GetLocalPath(ThisWorkbook.path) can be empty.
    Dim sAppFolder      As String
   
    sAppFolder = GetLocalPath(ThisWorkbook.path)
    If sAppFolder = "" Then sAppFolder = ThisWorkbook.path
    If Right(sAppFolder, 1) <> "\" Then sAppFolder = sAppFolder & "\"
    If UCase(sAppFolder) = UCase(Environ("OneDrive") & "\") Then
        Call MsgBox("Sorry, this report folder has not yet" & vbCrLf & _
            "fully synchronized from Sharepoint." & vbCrLf & _
            "Please try again later.", vbOKOnly, "Error")
        End
    End If
 
End Sub

Program Code ManageOnedriveSync

The code presented here is an extension of the version shown on (external link!) Stackoverflow, allowing important input and editing files to be synchronized before OneDrive is turned off.

A sample call:

Call ManageOnedriveSync(1, Array("Eingabedatei_1.csv", _
    "Eingabedatei_2.xlsx", _
    "Bearbeitungsdatei_1.xlsx"))

Naturally, before disabling OneDrive, only pure input files and files that need to be updated must be synchronized. For less experienced VBA programmers, you can even provide this example call with deliberate errors, encouraging them to actively think about which files their VBA application absolutely requires to be synchronized.

Please notice: This sub uses (requires) module (external link!) LibFileTools.

Please read my Disclaimer.

Sub ManageOnedriveSync(ByVal action As Integer, ParamArray touchpath() As Variant)
'Source: https://stackoverflow.com/questions/68099793/vba-start-stop-onedrive-sync-client
'Shutdown: ManageOnedriveSync 1
'Start: ManageOnedriveSync 0
'Requires LibFileTools: https://github.com/cristianbuse/VBA-FileTools
'Version When        Who   What
'      2 04-Mar-2025 Bernd Optional touchpath() will get synced before we switch off OneDrive sync
'      3 22-Jul-2025 Bernd Calling IsWorkbookOpen because input file might be open
'      4 19-Aug-2025 Bernd touchpath(i) can be an array or collection / variant
  
    Dim bTest            As Boolean
    Dim waitTillComplete As Boolean
    Dim bytInput         As Byte
    Dim errorcode        As Integer
    Dim FileNum          As Integer
    Dim i                As Integer
    Dim j                As Integer
    Dim style            As Integer
    Dim commandAction    As String
    Dim path             As String
    Dim shell            As Object
   
    waitTillComplete = False
    style = 1
    Set shell = VBA.CreateObject("WScript.Shell")
    Select Case action
    Case 1
      If LBound(touchpath) <= UBound(touchpath) Then
        'ParamArray is not empty
        For i = LBound(touchpath) To UBound(touchpath)
          If ArrayDim(touchpath(i)) > 0 Then
            For j = LBound(touchpath(i)) To UBound(touchpath(i))
              If Not IsWorkbookOpen(CStr(touchpath(i)(j))) Then
                FileNum = FreeFile
                Open touchpath(i)(j) For Input As #FileNum
                bytInput = Asc(Input(1, #FileNum))
                Close #FileNum
              End If
            Next j
          ElseIf VarType(touchpath(i)) = vbVariant Or _
            VarType(touchpath(i)) = vbObject Then
            For j = 1 To touchpath(i).Count
              If Not IsWorkbookOpen(CStr(touchpath(i)(j))) Then
                FileNum = FreeFile
                Open touchpath(i)(j) For Input As #FileNum
                On Error Resume Next
                bytInput = Asc(Input(1, #FileNum))
                On Error GoTo 0
                Close #FileNum
              End If
            Next j
          Else
            If Not IsWorkbookOpen(CStr(touchpath(i))) Then
              FileNum = FreeFile
              Open touchpath(i) For Input As #FileNum
              On Error Resume Next
              bytInput = Asc(Input(1, #FileNum))
              On Error GoTo 0
              Close #FileNum
            End If
          End If
        Next i
      End If
      commandAction = "/shutdown"
    End Select
    path = Chr(34) & "C:\Program Files\Microsoft OneDrive\Onedrive.exe" & _
      Chr(34) & " " & commandAction
    errorcode = shell.Run(path, style, waitTillComplete)
 
End Sub
 
Function IsWorkbookOpen (fileName As String)
  Dim ff As Long
  Dim ErrNo As Long
 
  On Error Resume Next
  ff = FreeFile()
  Open fileName For Input Lock Read As #ff
  Close ff
  ErrNo = err
  On Error GoTo 0
  Select Case ErrNo
  Case 0:    IsWorkbookOpen = False
  Case 70:   IsWorkbookOpen = True
  Case Else: Error ErrNo
  End Select
End Function