By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,009 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,009 IT Pros & Developers. It's quick & easy.

Save Template With New Name Off User Defined Entry

P: 22
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_Payperiod (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!
Jun 3 '08 #1
Share this Question
Share on Google+
1 Reply


kadghar
Expert 100+
P: 1,295
...

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:

Expand|Select|Wrap|Line Numbers
  1. dim Obj1 as object
  2. set  obj1 = createobject("excel.application")
  3. obj1.workbooks.open ("c:\myBook.xls")
  4. obj1.cells(1,1) = "now this cell has been changed"
  5. obj1.activebook.saveas ("c:\myBook2.xls")
  6. obj1.workbooks.close
I think this might point you in the right direction. Give it a try, and good luck

Kad
Jun 3 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.