VBA写入OneDrive同步Excel文件后同步挂起的解决方法求助

方法1：强制刷新文件系统元数据 #

OneDrive依赖文件系统的变更事件（如修改时间更新）触发同步，VBA保存操作可能未完整触发该事件。通过手动修改文件的最后修改时间，可以强制让OneDrive检测到文件变更。

修改后的代码示例：

Public Sub InsertToSharedDataBase()
    
    Dim sFullFileName As String
    Dim SharedDataBaseWb As Workbook
    Dim SharedDataBaseWs As Worksheet
      
    sFullFileName = "C:\Users\tk\MyCompanyOneDrine\MyFolder\Database.xlsx"
      
    Set SharedDataBaseWb = GetObject(sFullFileName)
    Set SharedDataBaseWs = SharedDataBaseWb.Worksheets("MyWorksheet")
    
    '写入数据
    With SharedDataBaseWs
        .Cells(1, 1).Value2 = "values"
        .Cells(1, 2).Value2 = "values"
        .Cells(1, 3).Value2 = "values"
        .Cells(1, 4).Value2 = "values"
    End With
    
    '保存并关闭工作簿
    SharedDataBaseWb.Close SaveChanges:=True
    
    '强制更新文件修改时间，触发OneDrive同步
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.GetFile(sFullFileName).DateLastModified = Now()
    
    '释放对象
    Set fso = Nothing
    Set SharedDataBaseWs = Nothing
    Set SharedDataBaseWb = Nothing
    
End Sub

方法2：显式打开工作簿+双重触发同步 #

改用Workbooks.Open显式打开文件，完成保存后同时更新修改时间和访问时间，进一步强化文件变更信号：

Public Sub InsertToSharedDataBase()
    
    Dim sFullFileName As String
    Dim SharedDataBaseWb As Workbook
    Dim SharedDataBaseWs As Worksheet
      
    sFullFileName = "C:\Users\tk\MyCompanyOneDrine\MyFolder\Database.xlsx"
      
    '隐藏打开工作簿，避免干扰用户操作
    Set SharedDataBaseWb = Workbooks.Open(Filename:=sFullFileName, Visible:=False)
    Set SharedDataBaseWs = SharedDataBaseWb.Worksheets("MyWorksheet")
    
    '写入数据
    With SharedDataBaseWs
        .Cells(1, 1).Value2 = "values"
        .Cells(1, 2).Value2 = "values"
        .Cells(1, 3).Value2 = "values"
        .Cells(1, 4).Value2 = "values"
    End With
    
    '先保存再关闭，确保写入完成
    SharedDataBaseWb.Save
    SharedDataBaseWb.Close SaveChanges:=False
    
    '更新文件修改和访问时间
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    With fso.GetFile(sFullFileName)
        .DateLastModified = Now()
        .DateLastAccessed = Now()
    End With
    
    '释放对象
    Set fso = Nothing
    Set SharedDataBaseWs = Nothing
    Set SharedDataBaseWb = Nothing
    
End Sub

方法3：主动调用OneDrive同步命令 #

通过VBA执行OneDrive的同步命令，强制触发立即同步。注意替换代码中OneDrive的实际安装路径：

Public Sub InsertToSharedDataBase()
    
    Dim sFullFileName As String
    Dim SharedDataBaseWb As Workbook
    Dim SharedDataBaseWs As Worksheet
    Dim oneDrivePath As String
      
    sFullFileName = "C:\Users\tk\MyCompanyOneDrine\MyFolder\Database.xlsx"
    '替换为你的OneDrive实际安装路径
    oneDrivePath = "C:\Users\tk\AppData\Local\Microsoft\OneDrive\OneDrive.exe"
      
    Set SharedDataBaseWb = GetObject(sFullFileName)
    Set SharedDataBaseWs = SharedDataBaseWb.Worksheets("MyWorksheet")
    
    '写入数据
    With SharedDataBaseWs
        .Cells(1, 1).Value2 = "values"
        .Cells(1, 2).Value2 = "values"
        .Cells(1, 3).Value2 = "values"
        .Cells(1, 4).Value2 = "values"
    End With
    
    '保存并关闭工作簿
    SharedDataBaseWb.Close SaveChanges:=True
    
    '调用OneDrive强制同步
    Shell oneDrivePath & " /syncnow", vbMinimizedNoFocus
    
    '释放对象
    Set SharedDataBaseWs = Nothing
    Set SharedDataBaseWb = Nothing
    
End Sub

核心说明 #

手动打开保存能触发同步，是因为该操作会完整更新文件元数据并触发文件系统的变更通知；而VBA的批量保存操作可能跳过部分通知逻辑。以上方法通过强制更新文件元数据或主动调用同步指令，让OneDrive及时检测到文件变更并完成同步。

内容的提问来源于stack exchange，提问作者JustGreat