Using date in cell as variable

[Patrick Simonds]

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?

 

[Stevie_mac]

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.

 

[Ken]

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