We help IT Professionals succeed at work.
Get Started

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

dearnemo asked
Last Modified: 2012-04-18
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
Watch Question
Excel and VBA Expert
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE