Excel Macro to name file 3

[tdonahue]

I could use a macro to automatically name a file, which a person would enter in a message box and the current date.

 

[TonyJollans]

Hi Psalm6229,

A bit more info please! What sort of File? Do you want to rename an existing file, or create a copy of one, or save a new file? Is the file you want to name, open? Which application are you using?

Enjoy,
Tony

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

 

[tdonahue]

I have an existing blank excel file that has ten sheets in it. The users get the files mixed up so I want a macro that names the excel file the name of the existing file "proforma" and adds the current date to the end of the name proforma. Example proforma0301. I also want the excel file to remain in a default directory, named C:\Excel Files

 

[PHV]

Take a look at the Format function:
? Format(Date, "mmdd")
0302

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi Psalm6229,

I'm still unclear as to what you want to do, but maybe this will help. To save the current Workbook in Excel with a name based on User input, you could do this ..

[blue]myname = InputBox("Name?")
ActiveWorkbook.SaveAs "C:\Excel Files\" & myname & Format(Date, "mmdd") & ".xls"[/blue]

Alternatively, to base the name on the existing workbook name you could do this ..

[blue]

p = InStr(ActiveWorkbook.Name, ".")
If p = 0 Then
    myname = ActiveWorkbook.Name
Else
    myname = Left(ActiveWorkbook.Name, p - 1)
End If
ActiveWorkbook.SaveAs "C:\Windows\Desktop\" & myname & Format(Date, "mmdd") & ".xls"

[/blue]

Enjoy,
Tony

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

 

[tdonahue]

Tony,

I think this is what I need. I will try this tonight and see if it works for me.

 

[tdonahue]

Tony,

I tried both sets of code. I guess what I really want is the first code without the input box, because I know the name of the file, which is proforma. Do you have any sugestions?

Tom

 

[PHV]

Try this:
ActiveWorkbook.SaveAs "C:\Excel Files\Proforma" & Format(Date, "mmdd") & ".xls"


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tdonahue]

Thanks Tony and PHV !!! I tried the last suggestion by PHV and it works great !!! It is exactly what I need !!


Tom

 

[bez999]

Hello

I also have a rename file question.
I have a spreadsheet that reads external text files in VBA to populate my spreadsheet. As part of the process I want to rename those external text files after I have read their data.

Can I do this?

Thanks for your time.

Regards
Chris Bezant

 

[TonyJollans]

Hi Chris,

To rename a file:

[blue]Name "C:\Path\To\File.txt" As "C:\Path\To\NewName.txt"[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[