Copy one Excel sheet to another Excel file

Technical support and scripting issues

Moderators: JRL, Dorian (MJT support)

Post Reply
shamigc
Junior Coder
Posts: 37
Joined: Wed Oct 22, 2003 11:38 am
Location: Monterrey, Mexico

Copy one Excel sheet to another Excel file

Post by shamigc » Wed Apr 13, 2005 10:32 am

Hi,
I need to develop a VBscript to copy the first and only sheet from file "Origin" to the file "Destination". The sheet I'm copying could already exist in the "Destination" file, so I should delete it before copying. I want to pass as parameters the variables "Origin" (source file), "Destination" (the file where the sheet should be copied) and "SheetToCopy" that contains the sheet name of the sheet to copy. I developed the following code, but it does not work the delete line:

VBSTART
Sub ExcelCopySheet(Origin,Destination,SheetToCopy)
Dim xlApp
Dim xlBook1, xlBook2

Set xlApp = CreateObject("Excel.Application")
Set xlBook1 = xlApp.Workbooks.Open(Origin)
Set xlBook2 = xlApp.Workbooks.Open(Destination)

xlApp.visible = False
xlApp.DisplayAlerts = False

xlBook2.Sheets(SheetToCopy).Delete
xlBook1.Sheets(1).Copy(xlBook2.Sheets(SheetToCopy))

xlBook1.save
xlBook2.save

xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
VBEND
Let>Origin=d:\file1.xls
Let>Destination=d:\file2.xls
'This a two character number that represents the Month Number
Let>SheetToCopy=04
VBRun>ExcelCopySheet,%Origin%,%Destination%,%SheetToCopy%


Can you help me with this? Also, I want to sort the "Destination" Sheets, How can I do that?

Thanks,
Salvador Hernandez
Thanks,
Salvador Hernandez

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts