ADODB连接到SharePoint上的Excel文件
问题描述:
我试图获得一个adodb连接到SharePoint上的文件(我们有SharePoint 2013,据我所知)检索并从我的本地驱动器上的另一个Excel文件上传一些数据给它。 我可以做到这一点,当这两个文件在我的本地驱动器与简单的adodb连接,打开等。但我不明白如何做与DB文件被上传到SP相同。ADODB连接到SharePoint上的Excel文件
我需要一些帮助来了解如何使其工作。
我知道我可以使用VBA从SP打开文件,但在这种情况下,我无法与它建立adodb连接。
谢谢
答
我不完全确定ADODB和SharePoint。你可能会得到这个工作,但它可能比它的价值更麻烦。你当然可以签入和签出文件!
Sub CheckOut()
Dim docCheckOut As String
'docCheckOut = "//office.bt.com/sites/Training/Design Admin/Training Plan/adamsmacro.xlsm"
docCheckOut = "http://excel-pc:/ExcelList.xlsb"
Call UseCheckOut(docCheckOut)
End Sub
Sub UseCheckOut(docCheckOut As String)
' Determine if workbook can be checked out.
If Workbooks.CanCheckOut(docCheckOut) = True Then
Workbooks.CheckOut docCheckOut
Else
MsgBox "Unable to check out this document at this time."
End If
End Sub
您还可以轻松地打开和关闭工作簿。
Sub OpenAndCloseWBFromSharePointFolder()
'If nobody has the file checked out
If Workbooks.CanCheckOut("http://excel-pc/ExcelList.xlsb") = True Then
Application.DisplayAlerts = False
'Open the file on the SharePoint server
Workbooks.Open Filename:="http://excel-pc/ExcelList.xlsb", UpdateLinks:=xlUpdateLinksNever
'Close the workbook
Workbooks("ExcelList.xlsb").Close
Application.DisplayAlerts = True
Else
Application.DisplayAlerts = False
'Open the File to check if you already have it checked out
Workbooks.Open Filename:="http://excel-pc/ExcelList.xlsb", UpdateLinks:=xlUpdateLinksNever
'See if doc can be checked in
If Application.Workbooks("ExcelList.xlsb").CanCheckIn Then
'Check In, Save and Close
Application.Workbooks("ExcelList.xlsb").CheckIn SaveChanges:=True, Comments:="Checked-In before Delete"
'Open the file again
Workbooks.Open Filename:="http://excel-pc//ExcelList.xlsb"
'Close the workbook
Workbooks("ExcelList.xlsb").Close
End If
End If
End Sub
你甚至可以更新特定单元格中的值!
Sub UpdateSpecificCells()
'If nobody has the file checked out
If Workbooks.CanCheckOut("http://excel-pc//ExcelList.xlsb") = True Then
Application.DisplayAlerts = False
'Open the file on the SharePoint server
Workbooks.Open Filename:="http://excel-pc//ExcelList.xlsb", UpdateLinks:=xlUpdateLinksNever
ActiveSheet.Cells(2, 7).Value = 100
ActiveSheet.Cells(3, 7).Value = 200
ActiveSheet.Cells(4, 7).Value = 300
'Close the workbook
Workbooks("ExcelList.xlsb").Save
Workbooks("ExcelList.xlsb").Close
End If
End Sub
甚至可以用VBA创建文件夹,并用VBA列出文件和文件夹。我很想知道是否有人用ADODB解决方案发布回来!与此同时,这可能值得一看。
https://sharepoint.stackexchange.com/help/on-topic可能会感兴趣的。 – pnuts
ADO无法通过HTTP工作 - 如果您有权访问该文件,则可以使用WebDav路径。 –