File name Save

[Elton Law]

Dear Expert,
A1 has a text "Performance2009_"
A2 has a formula =Today()
A3 is day(A2) = 11 (in this case)
A3 is month(A2) = 4 (in this case)
A3 is year(A2) = 2009 (in this case)
How can I use concatenate function to make up to "Performance2009_11042009"
please ?
Second question is ....
Say B1 can successfully make up to "Performace2009_11042009" ....
How can I use marco (VB) to save the file name same as cell B1 (ie
Performace2009_11042009) ?
Thanks in advance

 

[Dave Peterson]

If you're going to use the current date, you don't need to use cells on the
worksheet.

Dim myFileName as string
with worksheets("SomeSheetnamehere")
myfilename = .range("a1").value & format(date, "mmddyyyy") & ".xls"
.parent.saveas filename:="C:\somepathhere\" & myfilename, _
fileformat:=xlworkbooknormal
end with

You could concatentate cells, too:
myfilename = .range("a1").value & _
format(.range("a2").value, "mmddyyyy") & ".xls"

or

myfilename = .range("a1").value & format(.range("a3").value, "00") _
& format(.range("a4").value, "00") _
& format(.range("a5").value, "0000") & ".xls"

(I figured you meant a3:A5.)

ps.

I'd use the date in yyyymmdd order. So I can sort the names in windows explorer
in chronological order more easily.

pps. All untested, uncompile. Watch for typos.

 

[Kassie]

In A6 enter the following formula
=A1&A3&.A4&A5

Press <Alt<F11>
Click on Insert, Module
Enter the following code
Sub FileSaver()
Dim vName as variant
vName = Range("A6")
Activeworkbook.saveas (vName)
End Sub

to run the macro, either link to a button, or simply press <Alt><F8>, select
FileSaver and click on Run.

--
HTH

Kassie

Replace xxx with hotmail

 

[Elton Law]

Hi,
I have corrected A3 to A5.

A1 has a text "Performance2009_"
A2 has a formula =Today()
A3 is day(A2) = 11 (in this case)
A4 is month(A2) = 4 (in this case)
A5 is year(A2) = 2009 (in this case)

I used Concatenate or =A1&A2&A3&A4&A5
But it comes up with Performance2009_1142009 rather than
Performance2009_11042009
There is a 0 missing in month field.
Then How to ?
Save part is "work" for Kassie
Thaks

 

[Dave Peterson]

Why bother with the extra cells. Just use VBA's Date function.

=a1&text(a2,"00")&text(a3,"00")&text(a4,"0000")

But if you look at those earlier suggestions, there are easier ways.

I don't understand this portion: