CODEWITHSUNDEEP
excelvbasharepoint
Rhyfelwr
Rhyfelwr

Reputation: 329

Importing a list from SharePoint to Excel as a Range (or with a fixed Table name)

so I have the code below that pulls a list from SharePoint and imports it to Excel. My problem is, it imports it as a Table not as a Range and I cannot automatically convert it to range as the Table name is different every time I run the script (Table1, Table2, ... and so on).

My question is the following: Can I import the list from SharePoint as an Excel Range straight away, so that I don't have to convert it? If not, can I make it so every time I run the import procedure the name of the imported Table is fix?

Here's my code:

   Sub SharePoint_Import()
        Dim objMyList As ListObject
        Dim objWksheet As Worksheet
        Dim strSPServer As String
        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

Upvotes: 0

Views: 525

Answers (2)

FunThomas
FunThomas

Reputation: 29171

You can query a Sharepoint list using ADODB. Check the following code (untested because I don't have Sharepoint available, but have used something similar in the past)

Const SERVER As String = "xxxx.xxxxx.xxx.net/xxxx/xxxx" 'SP server
Const LISTNAME As String = "{1234567-1234-1234-1234-1234567891}" 'SP List ID

Dim connStr As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset

connStr = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;" _
        & "DATABASE=" & SERVER _
        & ";LIST=" & LISTNAME & ";"
conn.Open connStr
Set rs = conn.Execute("select * from list")

Dim rData As Worksheet
Set rData = thisworknbook.Sheets("rawdata") 'reset import sheet
rData.UsedRange.ClearContents
rData.Range("A1").CopyFromRecordset rs
conn.Close

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

Do this

[1] Change RData.UsedRange.ClearContents to RData.Cells.Clear. This will clear the sheet completely. Removes formats, text, tables etc..

[2] Once the table is imported, add this code. Since there is going to be only one table, you can use the table number to work with it.

Dim tbl As ListObject
Set tbl = RData.ListObjects(1)

Dim tblRng As Range
Set tblRng = tbl.Range

'~~> Convert table to range
tbl.Unlist

'~~> Comment this in case you do not
'~~> want to remove the formatting
With tblRng
    .Interior.ColorIndex = xlColorIndexNone
    .Font.ColorIndex = xlColorIndexAutomatic
    .Borders.LineStyle = xlLineStyleNone
End With

Upvotes: 1

Related Questions