VBA使用Open For Output As #1导出文件至SharePoint失败求助

原代码 #

Sub csv_output()
    Dim irow, i As Integer
    Dim strFile_Path, strFile_Path2 As String
    Dim export_path As String

Sheets("Sheet1").Select
online_path = "https://ABC.sharepoint.com/sites/Shared%20Documents/report.csv"

'edit path
file_Path = "C:\Users\" & Environ$("username") & "\Documents" & "\report.csv"

Sheets("Sheet1").Activate
    Open file_Path For Output As #1

    For irow = 17 To 20

    For i = 1 To 127
        Print #1, Sheets("Sheet1").Cells(irow, i) & ";";;
    Next i
   Print #1, Sheets("Sheet1").Cells(irow, i + 1)
   Next irow

'try save as to Sharepoint folder
Workbooks("report.csv").SaveAs Filename:=online_path
Close #1
End Sub

遇到的问题 #

尝试两种方法导出CSV到SharePoint均失败：

• 方法一：直接执行Open on_path For Output As #1，报错运行时错误'52'：错误的文件名或编号
• 方法二：先本地生成文件再保存到SharePoint，几种写法均出错：
  • SaveAs #1 Filename:=online_path：编译错误，缺少表达式
  • Workbooks("report.csv").SaveAs Filename:=online_path：运行时错误'9'，下标越界
  • Workbooks(#1).SaveAs Filename:=online_path：编译错误，缺少表达式

问题原因 #

1. Open For Output仅支持本地文件路径，无法直接识别SharePoint的HTTPS URL，所以方法一直接报错。
2. 方法二中，Open #1生成的文件并未在Excel中打开，Workbooks("report.csv")找不到对应工作簿导致下标越界；#1是文件句柄，不是Excel工作簿对象，不能用Workbooks(#1)调用。

正确实现方案 #

方案1：本地生成后复制到SharePoint（推荐） #

利用FileCopy函数将本地文件复制到SharePoint的WebDAV路径（而非HTTPS URL），WebDAV路径格式通常为\\[站点域名]@SSL\DavWWWRoot\[站点路径]\。

修改后的代码：

Sub csv_output_to_sharepoint()
    Dim irow, i As Integer
    Dim localPath As String
    Dim sharePointPath As String
    
    ' 本地临时文件路径
    localPath = "C:\Users\" & Environ$("username") & "\Documents\report.csv"
    ' 替换为你的SharePoint WebDAV路径
    sharePointPath = "\\ABC.sharepoint.com@SSL\DavWWWRoot\sites\Shared%20Documents\report.csv"
    
    ' 1. 本地生成CSV
    Open localPath For Output As #1
    For irow = 17 To 20
        For i = 1 To 127
            ' 单个分号避免多余空内容
            Print #1, Sheets("Sheet1").Cells(irow, i) & ";";
        Next i
        ' 输出最后一列，无需分号
        Print #1, Sheets("Sheet1").Cells(irow, 128)
    Next irow
    Close #1
    
    ' 2. 复制到SharePoint（先删除已有文件避免冲突）
    On Error Resume Next
    Kill sharePointPath
    On Error GoTo 0
    FileCopy localPath, sharePointPath
    
    ' 可选：删除本地临时文件
    Kill localPath
End Sub

方案2：直接用Excel对象保存到SharePoint #

如果你的Excel版本支持直接写入SharePoint HTTPS路径，可以跳过Open For Output，直接将指定区域另存为CSV：

Sub save_sheet_to_sharepoint_csv()
    Dim onlinePath As String
    onlinePath = "https://ABC.sharepoint.com/sites/Shared%20Documents/report.csv"
    
    ' 复制需要导出的单元格范围
    Sheets("Sheet1").Range("A17:ZZ20").Copy
    ' 创建临时工作簿
    Dim tempWB As Workbook
    Set tempWB = Workbooks.Add
    tempWB.Sheets(1).Paste
    ' 保存为CSV到SharePoint
    tempWB.SaveAs Filename:=onlinePath, FileFormat:=xlCSV, Local:=True
    tempWB.Close SaveChanges:=False
End Sub

注意事项 #

• 确保拥有目标SharePoint文件夹的读写权限
• WebDAV路径可通过手动在文件管理器中打开SharePoint文件夹后复制获取
• 用Open For Output生成CSV时，避免多余的分号或空输出

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