Importing SharePoint List to Excel via VBA

Question

I am trying to import a list from SharePoint to Excel via VBA. I do know the Server name for certain, but I am not sure how to find out the LISTNAME and VIEWNAME variables, also I would like to automatically log in to SharePoint with default (Windows) credentials, how could I insert that into my code?

Here is my code (for security reasons I had to clear some entries with XXXX) I'd appreciate the help:

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

It depends how you want to bring the data over. I use "ADODB.Connection" and "ADODB.Recordset" to connect to the list in SharePoint, and then read the values (or update them) from Excel. — Shai Rado
– Shai Rado, Commented Jan 14, 2019 at 13:31
Is that possible? I used ADODB connection for SQL queries into excel, I am not sure how to set it up for SharePoint. Would you be able to post an example code? — Rhyfelwr
– Rhyfelwr, Commented Jan 14, 2019 at 13:40

Shai Rado · Accepted Answer · 2019-01-14 14:35:12Z

Try the sample code below, you will need to add more of your fields to read them here

Code

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

Rhyfelwr · Accepted Answer · 2019-01-16 09:46:35Z

2

I managed to fix it this way, turns out the server address was wrong, but also "VIEWNAME" can be left as blank:

Sub SharePoint_Import()
    Dim objMyList As ListObject
    Dim objWksheet As Worksheet
    Dim strSPServer As String
    Dim RData As Worksheet
    Const SERVER As String = "xxxx.xxxxx.xxx.net/xxxx/xxxx" 'SP server
    Const LISTNAME As String = "{1234567-1234-1234-1234-1234567891}" 'SP List ID
    Const VIEWNAME As String = "" 

        Set RData = Sheets("rawdata") 'reset import sheet
        RData.UsedRange.ClearContents

    strSPServer = "https://" & SERVER & "/_vti_bin" '<- _vti_bin is necessary
    Set objWksheet = RData

      Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A1"))
      Set objMyList = Nothing
      Set objWksheet = Nothing

End Sub

answered Jan 16, 2019 at 9:46

Rhyfelwr

3292 gold badges7 silver badges23 bronze badges

1 Comment

Xev Over a year ago

Hi @Rhyfelwr may I know where to get the listname and viewname in SharePoint? Do I need to have admin rights or something to access these information?

Collectives™ on Stack Overflow

Importing SharePoint List to Excel via VBA

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related