Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
File name Save
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
File name Save
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. Elton Law wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
File name Save
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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
File name Save
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" wrote: 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. Elton Law wrote: Dear Expert, 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) 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
File name Save
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: Then How to ? Save part is "work" for Kassie Elton Law wrote: 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" wrote: 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. Elton Law wrote: Dear Expert, 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) 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SAVE and SAVE AS options disappeared from the drop down FILE menu | Excel Discussion (Misc queries) | |||
when i save xls file, debug script is running and canno't save fil | Excel Discussion (Misc queries) | |||
Excell2003 (SP-1) File > Save and File > Save As.. grayed out | Excel Discussion (Misc queries) | |||
Save & Save As features in file menu of Excel | Excel Discussion (Misc queries) | |||
Save Excel file - prompts to save - no Volitile functions used | Excel Worksheet Functions |