Copy an Excel worksheet to an existing workbook - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » COM Scripting » Copy an Excel worksheet to an existing workbook

Page 1 of 1

Topic Options

#183783 - 2007-12-17 04:09 PM Copy an Excel worksheet to an existing workbook
YaleCoder YaleCoder Getting the hang of it Registered: 2001-12-18 Posts: 61 Loc: Jelenia Gora, Poland	I have searched high and low but can't seem to figure out how to do this. I need to open an existing Excel workbook and then insert a populated worksheet from another workbook then save the modified file. Any help is greatly appreciated. Cheers _________________________ "When fascism comes to America, it will be wrapped in the flag, carrying a cross." Sinclair Lewis
Top

#183787 - 2007-12-17 05:16 PM Re: Copy an Excel worksheet to an existing workbook [Re: YaleCoder]
Witto Witto MM club member Registered: 2004-09-29 Posts: 1828 Loc: Belgium	First thing I always suggest: do what you want to do using the macro recorder. The macro that you create will show you almost all the code you need.
Top

#183808 - 2007-12-17 11:21 PM Re: Copy an Excel worksheet to an existing workbook [Re: Witto]
YaleCoder YaleCoder Getting the hang of it Registered: 2001-12-18 Posts: 61 Loc: Jelenia Gora, Poland	I'll give it a shot. Thanks. Still open to seeing some snippets if anyone has coded it _________________________ "When fascism comes to America, it will be wrapped in the flag, carrying a cross." Sinclair Lewis
Top

#183810 - 2007-12-18 12:31 AM Re: Copy an Excel worksheet to an existing workbook [Re: YaleCoder]
Allen Allen KiX Supporter Registered: 2003-04-19 Posts: 4572 Loc: USA	While not kix code, The Scripting Guys have a number of vbs/Excel examples. If you convert to Kixtart and get stuck, please post your code and we can help from there. General Excel Examples: http://www.microsoft.com/technet/scriptcenter/resources/qanda/office.mspx How Can I Add Additional Worksheets to an Excel Workbook? http://www.microsoft.com/technet/scriptcenter/resources/qanda/dec05/hey1215.mspx How Can I Copy a Worksheet to a New Spreadsheet? http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept06/hey0918.mspx _________________________ (... better days ahead)
Top

#183924 - 2007-12-19 10:40 AM Re: Copy an Excel worksheet to an existing workbook [Re: Allen]
YaleCoder YaleCoder Getting the hang of it Registered: 2001-12-18 Posts: 61 Loc: Jelenia Gora, Poland	OK. So I was able to get it working using VBScript. BEGIN CODE: Dim wbSource, wbTarget, xlApp, srcWorksheet, tgtWorksheet Set xlApp = CreateObject("Excel.Application") Set wbTarget = xlApp.Workbooks.Open("c:\temp\xl\target.xls") Set wbSource = xlApp.Workbooks.Open("c:\temp\xl\source.xls") Set srcWorksheet = wbSource.Worksheets(1) Set tgtWorksheet = wbTarget.Worksheets(2) srcWorksheet.Copy , tgtWorksheet wbSource.Close True wbTarget.Close True Set wbSource = Nothing Set wbTarget = Nothing Set srcWorksheet = Nothing Set tgtWorksheet = Nothing Set xlApp = Nothing END CODE This will work for me if I dynamically re-write the value for 'wbTarget ' based on filenames specified by my kix script. I have a huge number of files to modify and this method is rather slow (about 2 seconds per file * 5000+ files). I can't seem to figure out how to convert the above VB syntax to Kix and Google as I may, I can't seem to find anyone else who has done it either. If anyone can point me in the right direction for rewriting this functionality in Kix, it would be greatly appreciated. _________________________ "When fascism comes to America, it will be wrapped in the flag, carrying a cross." Sinclair Lewis
Top

#183938 - 2007-12-19 01:33 PM

Re: Copy an Excel worksheet to an existing workbook [Re: YaleCoder]

Björn

Korg Regular

Registered: 2005-12-07
Posts: 953
Loc: Stockholm, Sweden.

Code:

Dim $wbSource, $wbTarget, $xlApp, $srcWorksheet, $tgtWorksheet
$xlApp = CreateObject("Excel.Application")
$wbTarget = $xlApp.Workbooks.Open("c:\temp\xl\target.xls")
$wbSource = $xlApp.Workbooks.Open("c:\temp\xl\source.xls")
$srcWorksheet = $wbSource.Worksheets(1)
$tgtWorksheet = $wbTarget.Worksheets(2)
; dunno from here right now
cWorksheet.Copy , tgtWorksheet
wbSource.Close=True
wbTarget.Close=True
Set wbSource = Nothing
Set wbTarget = Nothing
Set srcWorksheet = Nothing
Set tgtWorksheet = Nothing
Set xlApp = Nothing

Edited by Björn (2007-12-19 01:33 PM)

_________________________
as long as it works - why fix it?
If it doesn't work - kix-it!

Top

#184391 - 2008-01-03 03:34 PM Re: Copy an Excel worksheet to an existing workbook [Re: Björn]
BradV BradV Seasoned Scripter Registered: 2006-08-16 Posts: 687 Loc: Maryland, USA	YaleCoder, If you have 5000+ files to do, you might want to create an ini file and get your source and target data from that. That is, set keys such as: Code: [Data] number = 5012 source1 = ... target1 = ... source2 = ... target2 = ... Then, you can just loop through the ini (or set a key with the total number of targets). User readprofilestring and you can let it run overnight or over the weekend. $\:\)$ Regards, Brad V
Top

#184410 - 2008-01-04 09:38 AM

Re: Copy an Excel worksheet to an existing workbook [Re: BradV]

Arend_ Offline

MM club member

Registered: 2005-01-17
Posts: 1896
Loc: Hilversum, The Netherlands

Code:

Dim $wbSource, $wbTarget, $xlApp, $srcWorksheet, $tgtWorksheet
$xlApp = CreateObject("Excel.Application")
$wbTarget = $xlApp.Workbooks.Open("c:\temp\xl\target.xls")
$wbSource = $xlApp.Workbooks.Open("c:\temp\xl\source.xls")
$srcWorksheet = $wbSource.Worksheets(1)
$tgtWorksheet = $wbTarget.Worksheets(2)
$srcWorksheet.Copy(, $tgtWorksheet)
$wbSource.Close(1) ;Although I think .Close will do
$wbTarget.Close(1)
$wbSource = ""
$wbTarget = ""
$srcWorksheet = ""
$tgtWorksheet = ""
$xlApp = ""

Top

Page 1 of 1

Previous Topic View All Topics

Index Next Topic

Moderator: Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org