troubleshooting Question

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

Avatar of dearnemo
dearnemo asked on
Microsoft ExcelVB Script
1 Comment1 Solution1260 ViewsLast Modified:
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
Jan Karel Pieterse
Excel and VBA Expert

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros