Copy Excel ListObjects from active workbook to a new Excel workbook using vba

dearnemo
dearnemo used Ask the Experts™
on
Here's my code. Basically with this code, I have the SharePoint 2010 list items imported to Excel workbook in new sheet which is sheet4. Since I m using vba macro to do it, I need to feed this macro enabled xlsx file to third party tool. That third party tool doesn't take macro enabled excel file. So I want to write a vba code that would simply copy the existing ListObjects to a new Excel workbook which I can save using xlsx extension and easily provide as an i/p to my third party tool. Below, I have created a Demo xlsx file. I want to copy the sheet4 of current workbook to Sheet1 in Demo xlsx file. How do I do it? Thanks.

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel and VBA Expert
Commented:
It is easier to just copy out your worksheet with the listobject and save-as that new workbook:
objWksheet.Copy
xlApp.ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\shress2\Desktop\Demo.xlsx", FileType:=51

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial