Please use code tags (# icon from 'go advanced') when posting code.
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("a1"))
Dim xlApp As Object
Dim wbExcel As Object
Dim wb2 As Workbook
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Add
With wbExcel
.Title = "Demo"
.SaveAs Filename:="C:\Documents and Settings\shress2\Desktop\Demo.xlsx"
End With
xlApp.Visible = True
Set objMyList = Nothing
Set objWksheet = Nothing
End Sub
Bookmarks