Automatic File and Worksheet Naming 1

[SpeedThink]

Is anyone aware of a macro that will "automatically" name a worksheet and a file based on the contents that are typed within a range on my worksheet?

I create a daily report by importing data from an Oracle application to an Excel Template. The data from the Oracle application is saved in a text file such as "PVAR_1234.prn."

Therefore, upon importing the data onto the source worksheet, I would like to name the worksheet "Source PVAR_1234." I am currently altering the worksheet name manually. The name of the Oracle text file will always contain "PVAR" - the only change to the name on a daily basis is the 4 numbers. In other words, the following day the file will be similar to "PVAR_2345.prn."

Therefore, is it possible to enter the desired worksheet name and the desired filename onto the template worksheet and use VBA to automatically name the worksheet and file accordingly to coincide with the data that is contained in the two cell ranges?

Example:

Cell A1 Source_PVAR_1234
Cell A2 Daily Adjustment Report May20_April28

 

[Molby]

Sheets("sheet1").name = Range("a1").value

and

Workbooks("Yourworkbook.xls").SaveAs Filename:=Range("a2").Value & ".xls

 

[SpeedThink]

Molby,

I was able to "automatically" name the worksheets by using the following:

Sub AutoNameWorksheet()
Sheets("sheet1").Name = Range("a1").Value
Sheets("sheet2").Name = Range("a2").Value
End Sub

As far as automatically naming a workbook and saving on the network, the following issues arise:

I am accessing an Excel template initially and then saving as a excel workbook. Also, another option would be to present the end user with a dialog box to prompt the user for the desired worksheet name and the desired workbook name and then save the template as an Excel Workbook on the desired network path.

I am working on the following macros. Any additional insight will be greatly appreciated.

Sub AutoNameFile()
myFile = "Daily_Adjustment_Report" & Range("b1").Value
ActiveWorkbook.SaveAs "C:\Reports\" & myFile
End Sub


Sub AutoNameFile1()
Template("Daily_Adjustment_Report.xlt").SaveAs Filename:=Range("b1").Value & ".xls"
ActiveWorkbook.SaveAs "C:\Reports\" & myFile
End Sub

 

[Molby]

Why is it an issue is you are saving a template as a workbook? As soon as you do saveas, the template will remain unchanged.

If you are picking the values from A1 and A2, why the need for the user to decide what the names should be. Are you thinking along the lines of a textbox where they can enter the names themselves?

That would be something like
nam$ = Textbox("Please enter the name of the workbook")
You could then use nam$ to name the file. Otherwise you may think about having several values which can be populated by a combobox. The user can then pick one of those.

Otherwise your code looks fine.

Let me know if you want any additional info.

 

[SpeedThink]

Points noted,

Thanks for the insight.

Everything is functioning great. At the present time, I don't have any error-handling routines. However, compared to where I started, this allows for the production of the report in less than 15 minutes rather than 30 minutes!!

In the near future, I hope to produce the report in less than 2 minutes by exploring the following code!:

* Auto - import from Oracle text file to the Excel Template
* Auto e-mail, using lotus notes, to more than 30 end-users
* Auto paging someone if a range on my worksheet exceeds a certain value and/or paging the 30 end-users with the contents of a certain range on my worksheet using Lotus Notes (Note, Lotus Notes have a paging feature whereby one can send a page to someone else who is listed in the Lotus Notes Directory).

Any further insight is appreciated.

Maybe, I will create another thread for the items just stated.

 

[Molby]

The best thing you can do for some is these steps is to either record yourself performing the action (in the case of importing the text file) or search through the forums here. I can't help you with Lotus Notes as I only have access to Outlook.

If you get stuck going forward then start another thread, there will be plenty of other people who can help.

 

[SpeedThink]

Thanks.

 

[TonyJollans]

Hi SpeedThink,

It looks like Molby helped you out there, and your response is appreciated (you might be surprised by the number of people who don't even show such common courtesy). As you are not a regular visitor, you may not be aware that the usual way we say 'Thanks' round here is by awarding stars - all you have to do is click on the link (Thank Molby for this valuable post!) in the bottom left hand corner of one of his posts.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.