我正在尝试通过 VBA 将列表从 SharePoint 导入 Excel。我确实知道服务器名称,但我不确定如何找出LISTNAME
和VIEWNAME
变量,而且我想使用默认(Windows)凭据自动登录到 SharePoint,我如何将其插入到我的代码中?
这是我的代码(出于安全原因,我不得不使用 XXXX 清除一些条目)我很感激您的帮助:
Sub ImportSPList()
Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Const SERVER As String = "https://xxxxxx.xxx.xxxx.net/sites/RiskMgmt/xxxAudit"
Const LISTNAME As String = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx}"
Const VIEWNAME As String = "ALL Datasheet View"
strSPServer = SERVER
Set objWksheet = Worksheets.Add
Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A1"))
End Sub
尝试下面的示例代码,您需要添加更多字段才能在此处阅读它们
代码
Option Explicit
' === SharePoint Site and List Settings ===
Const SERVERUrl As String = "https://xxxxxx.xxx.xxxx.net/sites/RiskMgmt/xxxAudit/"
Const ListName As String = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx}"
'Const VIEWNAME As String = "ALL Datasheet View" ' <-- Currently not used, using the ListName
' === Parameters for using ADO with Late Binding ===
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adUseClient = 3
Const adUseNone = 1
Const adUseServer = 2
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
Const adAddNew = &H1000400
Const adUpdate = &H1008000
Const adSearchForward = 1
' === Field Names in SharePoint List ===
Const ProjectNum As String = "Project Number"
' Add more fields here
' =======================================================================
Sub ImportSPList()
Dim Conn As Object
Dim Rec_Set As Object
Dim Sql As String
Dim objWksheet As Worksheet
Set objWksheet = Worksheets.Add
On Error GoTo ErrHand
' Create the connection object with ADO
Set Conn = CreateObject("ADODB.Connection")
Set Rec_Set = CreateObject("ADODB.Recordset")
' Open the connection and submit the update
With Conn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
"DATABASE=" & SERVERUrl & ";" & _
"LIST=" & ListName & ";"
.Open
End With
' add a Query to select all records from your List
Sql = "SELECT * FROM [MyName_List] ;" ' <--- CHANGE TO YOUR LIST NAME
With Rec_Set
' confirm that recordset is closed
If .State = 1 Then .Close
' Recordset settings parameters
.ActiveConnection = Conn
.CursorType = adOpenDynamic ' adOpenStatic,
.CursorLocation = adUseClient
.LockType = adLockOptimistic ' adLockPessimistic
.Source = Sql
.Open
' check how many # of records returned
If .RecordCount < 1 Then ' no records
' do someting >> maybe MSGBOX
Else ' at least 1 record found >> read the row's data
Do While Not .EOF
objWksheet.Range("A2").Value = .Fields(ProjectNum).Value ' read the value of field "Project Number" from list to cell
' add more fields below
.MoveNext
Loop
End If
.Close
End With
Set Rec_Set = Nothing
CleanExit:
If Conn.State = 1 Then
Conn.Close
Set Conn = Nothing
End If
MsgBox "Finished reading data from SharePoint list", vbOKOnly
ErrHand:
Debug.Print Err.Number, Err.Description
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句