Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 16th, 2012, 12:49 PM
Registered User
Join Date: Apr 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Question Copy Excel ListObjects from active workbook to a new Excel workbook using vba

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
Old April 29th, 2012, 12:56 PM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts

Can you try the following
With wbExcel 
.Title = "Demo" 
.SaveAs Filename:="C:\Documents and Settings\shress2\Desktop\Demo.xlsx" 
End With 

   objWksheet.Copy wbExcel.Sheets(1)
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Last edited by Shasur; April 29th, 2012 at 12:57 PM.. Reason: Added text

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 VBA - Need to open a workbook in a fold bablhous BOOK: Excel 2003 VBA Programmer's Reference 0 December 21st, 2006 02:50 PM
XPATH Help, Please- Excel XML Workbook jftaylor BOOK: XSLT Programmer's Reference, 2nd Edition 2 September 28th, 2006 08:47 PM
Closing Excel Workbook rekha_jsr Excel VBA 0 July 9th, 2005 02:40 AM
Excel Workbook created from Access VBA clueless_may Access VBA 0 June 8th, 2004 09:42 AM
Saving Excel workbook with new filename tsegal20 Excel VBA 1 May 21st, 2004 01:55 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.