ACCESS文件连接从EXCEL到SharePoint (SQL数据库)

Question

我有一个访问连接文件(.accdb)，允许我使用EXCEL (Office 365)查询SharePoint库。除了添加到SharePoint库的新文件之外，所有的工作都很棒。我可以查询并返回这些新文件/条目的所有数据，但由于某种原因无法获得这些新条目的文件名。如有任何帮助，不胜感激。

Const SQLIMSSHAREPOINTCONNECTION = "Provider=Microsoft.ACE.OLEDB.12.0;数据来源= C:MORGANMACROSIMS_SHAREPOINT_db.accdb">

子例程:

Dim i As Long
Dim cn As Object
Dim rs As Object
Dim StrSql As String
Dim thePieces1() As String
Dim thePieces2() As String
Dim theFullFilename As String
Dim theShortFilename As String

StrSql = "SELECT [Name], [ID], [PartNumber(s)], [DateCompleted], [DocumentType], " & _
"[WorkOrder(s)], [PurchaseOrder(s)], [SalesOrder(s)], [CustomerName(s)], [WorkCenter]" & _
" FROM [Inspection Reports]"
Set cn = CreateObject("ADODB.Connection")
cn.Open SQLIMSSHAREPOINTCONNECTION
Set rs = CreateObject("ADODB.RECORDSET")
rs.ActiveConnection = cn
rs.Open StrSql
If Not (rs.BOF And rs.EOF) Then
i = 1
On Error Resume Next
Do While Not rs.EOF
i = i + 1
theFullFilename = ""
theShortFilename = ""
ReDim thePieces1(10)
thePieces1 = Split(rs.Fields(0), "#")
theFullFilename = thePieces1(1)
thePieces1() = Split(theFullFilename, "/")
theShortFilename = thePieces1(UBound(thePieces1))
Range("A" & i).Value = rs.Fields(1)      ' ID
Range("B" & i).Value = theShortFilename  ' Filename
Range("C" & i).Value = rs.Fields(2)      ' PartNumber(s)
Range("D" & i).Value = rs.Fields(3)      ' DateCompleted
Range("E" & i).Value = rs.Fields(4)      ' DocumentType
Range("F" & i).Value = rs.Fields(5)      ' WorkOrder(s)
Range("G" & i).Value = rs.Fields(6)      ' PurchaseOrder(s)
Range("H" & i).Value = rs.Fields(7)      ' CustomerName
Range("I" & i).Value = rs.Fields(8)      ' WorkCenter
rs.MoveNext

Loop

Answer 1

我不清楚你的sharepoint访问Excel连接如何工作，但你的记录集(rs)可能需要刷新或查询后，你更新了sharepoint数据。