1

I want to pull information from a SharePoint 2016 list in VBA. Below Code is what I found and that is working without depending on a view. Unfortunately the SQL query uses the list Name. A user could Change the list Name and then the Code would not work anymore. If I could read the list Name via GUID then the list could be renamed...

So I have three options:

  1. Read the list name in VBA via GUID
  2. Get a different VBA Code to pull Information without view or list Name
  3. Write in the documentation not to change the library Name (lame!)

Conditions:

  • I need to retrieve information from a SharePoint list via the item ID
  • It has to be VBA
  • If there is a better solution in SOAP or REST I also take it, but have no clue on how to...

Here the working VBA Code I found with MS ActiveX Data Objects 6.0 in References to pull list data:

' Some public constants, but they can be procedure Level

' if you don't think you're going to need them elsewhere.

Public Const sDEMAND_ROLE_GUID As String = "{012CFE06-DAD2-4294-AE45-2FD140D7BB55}"

Public Const sSHAREPOINT_SITE As String = "https://www.sharepoint.com/site/"

Sub TestPullFromSharepoint()

' This pulls the data from a SharePoint list. Don't forget a reference

' to the MS ActiveX Objects in "References".

' Variable declarations.

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim sConn As String

Dim sSQL As String

Dim ID As String

ID = 14 'as example

' Build the connection string: use the ACE engine, the database is the URL if your site

' and the GUID of your SharePoint list is the LIST.

sConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;" & _

"DATABASE=" & sSHAREPOINT_SITE & ";" & _

"LIST=" & sDEMAND_ROLE_GUID & ";"

' Create some new objects.

Set cn = New ADODB.Connection

Set rs = New ADODB.Recordset

' Open the connection.

With cn

.ConnectionString = sConn

.Open

End With

' Build your SQL the way you would any other. You can add a WHERE clause

' if you need to filter on some criteria.

sSQL = "SELECT tbl.[name] FROM [Library Name] as tbl where tbl.[id] = 14"

' Open up the recordset.

rs.Open sSQL, cn, adOpenStatic, adLockOptimistic

' Copy the recordset data to a worksheet.

'ThisWorkbook.Worksheets("Tabelle1").Range("a1").CopyFromRecordset rs

 MsgBox rs(0)

End Sub

Thanks for your help

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.