使用VBA向SharePoint文件夹写入文本文件时遇异常问题

问题场景 #

使用MS Project VBA代码可正常在桌面文件夹创建文本文件，但有时已连接SharePoint文件夹且能在资源管理器中浏览，却无法通过FSO（FileSystemObject）在其中创建文件。已使用SharePointURLtoUNC函数将SharePoint URL转换为UNC路径，但问题依旧。

用户代码如下：

Dim t                       As Task
Dim td                      As TaskDependency
Dim str                     As String
Dim sep                     As String
Dim i                       As Long
Dim fso                     As Object
Dim f                       As Object
Dim SharepointAddress       As String
Dim LocalAddress            As String
Dim Address                 As String

' Create Local and SharePoint address.
LocalAddress = Environ("USERPROFILE") & "\Desktop\Test"
SharepointAddress = "https://Company.sharepoint.com/the_rest_of_the_path"

' Preparing the sharepoint address
SharepointAddress = SharePointURLtoUNC(SharepointAddress)

' print the sharepoint address to immediate window for checking
Debug.Print SharepointAddress

' Use the Address variable for switching between the local address and sharepoint address
Address = SharepointAddress

' If the user pressed the cancel button exit
If MsgBox("Your project data Is being exported. Press OK To continue", vbOKCancel) = vbCancel Then
    Exit Sub

Else
    ' Create a fso object
    Set fso = CreateObject("Scripting.FileSystemObject")
 
    ' If the folder exists write the text file to the local or sharepoint address
    If fso.folderexists(Address) Then
        Address = Address & "\mydata.txt"
        Set f = fso.opentextfile(Address, 2, True, -1)      ' The problem is here
    Else
        MsgBox("Connection Is Not established, please try again later")
        Exit Sub
    End If
    
End If

可能原因 #

1. 权限/认证问题：虽能浏览SharePoint路径，但可能无写入权限，或NTLM认证缓存过期导致临时权限丢失
2. FSO兼容性局限：FileSystemObject是老组件，对现代SharePoint的UNC映射支持有限，尤其是使用Modern Auth的站点
3. 路径转换不完整：SharePointURLtoUNC函数可能未正确处理特殊字符、空格或站点特定路径格式
4. 同步冲突：OneDrive同步客户端可能正在同步目标文件夹，导致文件创建被锁定

解决方案 #

1. 验证权限与重新建立连接 #

• 手动在SharePoint UNC路径下创建文件，确认是否具备写入权限
• 用net use命令重新建立网络连接，刷新认证状态：

  net use Z: \\Company.sharepoint.com@SSL\DavWWWRoot\the_rest_of_the_path /user:yourdomain\yourusername yourpassword
  

• 若使用OneDrive同步，确保文件夹处于“联机”状态且同步完成

2. 替换FSO为VBA原生/更兼容的文件操作 #

FSO对SharePoint支持有限，改用以下方法：

方法A：VBA原生Open语句

替换原FSO创建文件的代码块：

If fso.folderexists(Address) Then
    Address = fso.BuildPath(Address, "mydata.txt") ' 自动处理路径分隔符
    ' 用原生语句创建并写入文件
    Open Address For Output As #1
    Print #1, "Exported project data content" ' 替换为实际内容
    Close #1
Else
    MsgBox("Connection Is Not established, please try again later")
    Exit Sub
End If

方法B：ADODB.Stream处理编码与写入

适合需要指定编码的场景：

Dim stream As Object
Set stream = CreateObject("ADODB.Stream")

If fso.folderexists(Address) Then
    Address = fso.BuildPath(Address, "mydata.txt")
    stream.Charset = "UTF-8"
    stream.Open
    stream.WriteText "Your project data content here"
    stream.SaveToFile Address, 2 ' 2=覆盖已有文件
    stream.Close
Else
    MsgBox("Connection Is Not established, please try again later")
    Exit Sub
End If
Set stream = Nothing

3. 优化路径处理与错误调试 #

• 用fso.BuildPath拼接文件路径，避免手动添加分隔符的错误
• 添加错误捕获，明确问题类型：

  On Error Resume Next
  Set f = fso.opentextfile(Address, 2, True, -1)
  If Err.Number <> 0 Then
      MsgBox "创建文件失败: " & Err.Description & " (错误代码: " & Err.Number & ")"
      Exit Sub
  End If
  On Error GoTo 0
  

  根据错误代码排查：比如错误70为权限拒绝，错误76为路径未找到

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