Access VBA自动化可以将Word文档保存到Sharepoint，但不能保存到Excel电子表格

Question

我有一个Access数据库，它可以创建文件并将其保存到内部网络上的Sharepoint。在两种情况下，文件是Word文档，将它们保存到Sharepoint可以很好地工作。其中两个文件是Excel电子表格，使用save或savecopy保存它们是无效的。最后一个文件是PowerPoint，保存它也会失败。

Word保存的工作方式如下：

Set objWord = New Word.Application
Set objDoc = objWord.Documents.Add
objWord.Visible = True
Set objSelection = objWord.Selection
Set objRange = objDoc.Range
Creation of document
Dim SPAddress As String
SPAddress = GetSPAddress(iProjectId) & "ProjectFactSheet_" & sSiteName & "_" & Format(Now(), "yyyymmddhhnnss") & ".docx"
objDoc.SaveAs SPAddress

失败的Excel保存如下所示：

Set oExcelApp = CreateObject("Excel.Application")
oExcelApp.Visible = True
oExcelApp.AskToUpdateLinks = False
oExcelApp.DisplayAlerts = False
oExcelApp.Workbooks.Open ("c:apps" & strReportName)
SPAddress = GetSPAddress()
oExcelApp.ActiveWorkbook.SaveAs SPAddress & strReportName

• savecopyas也会得到一个错误

此操作失败，错误为无法访问文件名。该文件可能已损坏，位于没有响应或只读的服务器上。

Public Function GetSPAddress(Optional lngQuaID As Long) As String
GetSPAddress = ""
If IsNull(lngQuaID) Or lngQuaID = 0 Then
GetSPAddress = DLookup("SPBasePath", "AppData")
Else
If IsNull(DLookup("[SPPath]", "QualificationLog", "[QuaID] = " & lngQuaID)) Then
GetSPAddress = DLookup("SPBasePath", "AppData")
Else
GetSPAddress = DLookup("[SPPath]", "QualificationLog", "[QuaID] = " & lngQuaID)
End If
End If
End Function

Answer 1

我唯一能想到的建议是在打开报告时设置一个工作簿变量，这样你就可以绝对确定保存了哪个文件(尽管这本身不应该导致错误…(

Dim wb As Object   '<<<
Set oExcelApp = CreateObject("Excel.Application")
oExcelApp.Visible = True
oExcelApp.AskToUpdateLinks = False
oExcelApp.DisplayAlerts = False
Set wb = oExcelApp.Workbooks.Open("c:apps" & strReportName) '<<<
SPAddress = GetSPAddress()
wb.SaveAs SPAddress & strReportName '<<<