I found this Import Sharepoint List into Excel Using VBA Only that people have been successfully using to automate the exporting SharePoint 2010 list items to Excel 2010. Currently I m also using web interface 'export to excel' in SharePoint. But I want to avoid point-click and want to run a script that automates this process. Below is my code. I m getting run time error: Cannot connect to the server at this time.Changes to your data cannot be saved. Since I m new to vba, it will be great if anybody could point me to right instructions to make it work. Any suggestions/help is much appreciated. Thanks.

Sub TestMacro()
Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Const SERVER As String = "http://pwd-gsops5/"
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

1 Answer 1


the Const Server As String should not include http:// as it will be provided when strSPServer variable is built.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.