Export data from SharePoint 2013 to Excel

[Horus23]

Hi Team,

I'm trying to export data from SP 2013 through VBA in Excel. I found this useful thread but since it's Dead, I'd to ask the question again.

http://www.mrexcel.com/forum/excel-...export-sharepoint-2010-list-excel-2010-a.html

This is the code by the user, but I want to know few things as not aware of them:

=> LISTNAME - how to get this information
=> VIEWNAME - how to get this information


Sub TestMacro()

Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String

Const SERVER As String = "http://abcd/"
Const LISTNAME As String = "{A486016E-80B2-44C3-8B4A-8394574B9430}"
Const VIEWNAME As String = ""

' The SharePoint server URL pointing to the SharePoint list to import into Excel.

strSPServer = "http://" & SERVER & "/_vti_bin"

' Add a new worksheet to the active workbook.

Set objWksheet = Worksheets.Add

' Add a list range to the newly created worksheet and populated it with the data from the SharePoint list.

Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _ Array(strSPServer, LISTNAME, VIEWNAME),
True, , Range("a2"))

Set objMyList = Nothing
Set objWksheet = Nothing

End Sub

If I know the above two information, hopefully I should be able to run the code.

 

[Horus23]

I also stumbled upon this link but this seem to be out of world as it got too many jargon words and I can't understand what are the variables that I need to input to get the the right result.

Import Sharepoint 2010 list data from Excel table using VBA - Stack Overflow

 

[Horus23]

Can someone help with this????

 

[qi1777]

Can someone help with this????

It's really easy...I found this info about doing it

I think the site is very rudimentary but the blog post is excellent.. Too bad there is not more content..


"Sub MacroABCReport()‘Choose your variables
Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Const SERVER As String = “somesite.sharepoint.com”
‘These GUIDs will come from your SharePoint site
‘You can deobfuscate list and view GUIDs at the following site
‘URL Decoder/Encoder
Const LISTNAME As String = “{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}”
Const VIEWNAME As String = “{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}”
strSPServer = “http://” & SERVER & “/_vti_bin”
‘Remove Old Data
Worksheets(“Data”).Select
Columns(“A:AG”).Select
‘Column selection will be dependent on SharePoint list data
‘if you select incorrect columns it will give error when refreshing the data
Selection.ClearContents
‘Import Data from SharePoint
‘Change value next to Range to begin insert of data
Set objWksheet = Worksheets(“Data”)
Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, Array(strSPServer, LISTNAME, VIEWNAME), False, , Range(“A2”))
End Sub
After you have created a blank workbook, place this script in the Visual Basic ..."


https://sharepointcatblog.wordpress.com/getting-sharepoint-data-through-vb-script-for-excel/