Script - Create New Worksheet

J

Jeff C

I am trying to come up with a script that will take a *.xls file and write it
to a new worksheet in another exisiting xls file.

Can anyone assist me?

Thank you in advance
 
D

Dom_Ciccone

Are you familar with VBA, Jeff? This is easily done using a macro but the
code really depends on your situation. If you are copying a single sheet
from a workbook to another book, that's different from copying every sheet or
just selected ones. Will you be using the same recipient workbook every time
or do you wish to choose which workbook you paste the data in to?

If you can supply a little more information, that would help.
 
J

Jeff C

Thank you for your help.
I am familiar with VBA to an extent however most of my experience if with
using MS Access. I am limited in my familiarity with Excel. I have produced
severall scripts automating some data import and export procedures into MS
Access.

I am using Excel from Offie Pro 2003. I want to copy a single sheet (the
only one that exists) from "New.xls" to an new worksheet in "Exisiting.xls".
This will give me Exisiting.xls with a tab for every day. It would be really
great if I could label the tab with the current date or rather with
yesterday's date.

New.xls is overwritten each morning with new data, I want to place the
script executing this copy to new sheet in different file in the task
scheduler so that it is automated every day after the "New.xls" file is
written to the drive.

I appreciate your time.
 
J

Jeff C

Thank you Gord but I could not find code that would work for what I am doing,
or it was to far over my head for me to understand.

I have an xls file with one named worksheet. I want to copy this worksheet
into a second xls file as an added worksheet leaving the existing worksheets
in the workbook intact.
 
G

Gord Dibben

Try this macro.

Sub sheetcopy()

Workbooks("New.xls").Sheets(1).Copy _
Before:=Workbooks("Existing.xls").Sheets(1)
ActiveSheet.Name = Format(Date - 1, "Mmm dd ")
Workbooks("Existing.xls").Close SaveChanges:=True

End Sub


Gord
 
J

Jeff C

Using your suggestion as a start I have the following assembled and running
without err:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("New.xls")
Set objWorkbook1 = objExcel.Workbooks.Open("Exisiting.xls")
'Set objWorksheet = objWorkbook.Worksheets("Sheet1")
'Set objWorksheet2 = objWorkbook1.Worksheets("Sheet3")

objWorkbook.Sheets(1).Copy, objWorkbook1.Sheets(1)
objWorkbook1.ActiveSheet.Name = "Date()"
objWorkbook1.Close SaveChanges = True

Though running, the above will not name the new worksheet and when I use
Format(date-1,"mmddyy") or anything else I get an error on unknown method
Format.

The other problem is that the instance of Excel does not close.

Can you or someone assist further??

Thank you
 
Top