Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using date in cell as variable
On my spreadsheet cell C2 displays a date in the (04/27/05) format. What I
need to do is have a macro which will save the document to C:\My Documents\????\XXXXXX. Where ???? is the year in cell C2 and the XX-XX-XX is the date (04-27-05). Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using date in cell as variable
Sure...
Sub SaveFile() Dim sFileName As String, sTest As String sTest = Sheets("Sheet1").Range("C2").Text If IsDate(sTest) = False Then MsgBox "Sheet1 C2 is not a valid date", vbCritical, "" Exit Sub End If sFileName = "C:\My Documents\" & _ Format(sTest, "yyyy\\dd-MM-yy") & _ ".xls" ActiveWorkbook.SaveAs sFileName End Sub ....but the Folders must exist. Regards - Steve. "Patrick Simonds" wrote in message ... On my spreadsheet cell C2 displays a date in the (04/27/05) format. What I need to do is have a macro which will save the document to C:\My Documents\????\XXXXXX. Where ???? is the year in cell C2 and the XX-XX-XX is the date (04-27-05). Is this possible? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using date in cell as variable
Patrick Simonds wrote: On my spreadsheet cell C2 displays a date in the (04/27/05) format. What I need to do is have a macro which will save the document to C:\My Documents\????\XXXXXX. Where ???? is the year in cell C2 and the XX-XX-XX is the date (04-27-05). Is this possible? Patrick This works for me: Sub test() m = "c:\my documents\" m = m & Year(Range("c2").Value) m = m & "\" & Format(Range("c2").Value, "mm-dd-yy") m = m & ".xls" ActiveWorkbook.SaveAs Filename:=m, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=Fals End Sub It requires that the folder c:\my documents\ already exists. It could be created on the fly with another line or two if necessary. Good luck Ken Norfolk, Va |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How capture a date variable within a text cell? | Excel Discussion (Misc queries) | |||
Setting a Date variable | Excel Programming | |||
Assign a Date Variable within VBA Q | Excel Programming | |||
calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions | |||
Compare Date in Cell VS a Variable Date to Hide Row | Excel Programming |