Save Template With New Name Off User Defined Entry

[Christina123]

I don't actually need the code for this...yet. Essentially I need to know if what I want to do can be done.

I have two work books. One is a report the other contains the data that the report is formulated from.

The work book with the data has 21 sheets, one for each day of the week, three shifts a day. This is a planning tool for one of the departments I work for. The planner enters whether the job has been planned, the work order type and how many hours (by trade) have been planned. The supervisors enter the 'crisis' work orders, the actual hours on the planned jobs and any 'crisis' hours (by trade) that have been used.

Each of these data files is named Department_Payp eriod (the pay period is a number which represents the week in the year)

What I need to happen is save the report file as a template (so I don't have to redo all of the code or anything) and when it opens prompt me (or the user) to enter in the Pay Period number of the week that they are running the report for. I then need it to save the excel file as the pay period number. I also need it to open the corresponding data file and then extract data.

I need to have this be all automated, which is why I am thinking VB will work.

I don't know VB for excel at all, I'm using Excel Office Professional 2003, so far all I have been able to accomplish is opening a work book.


Thanks!

[kadghar]

...

What I need to happen is save the report file as a template (so I don't have to redo all of the code or anything) and when it opens prompt me (or the user) to enter in the Pay Period number of the week that they are running the report for. I then need it to save the excel file as the pay period number. I also need it to open the corresponding data file and then extract data.

I need to have this be all automated, which is why I am thinking VB will work.

I don't know VB for excel at all, I'm using Excel Office Professional 2003, so far all I have been able to accomplish is opening a work book.
...
Thanks!

Hi, yes, its very possible,
VBA for Excel allows you to interact very well with excel, I'll give you some lines to open, make a couple of changes and SaveAs a worksheet:

[CODE=vb]dim Obj1 as object
set obj1 = createobject("e xcel.applicatio n")
obj1.workbooks. open ("c:\myBook.xls ")
obj1.cells(1,1) = "now this cell has been changed"
obj1.activebook .saveas ("c:\myBook2.xl s")
obj1.workbooks. close[/CODE]

I think this might point you in the right direction. Give it a try, and good luck

Kad