thomas.szwed
08-19-2008, 04:16 AM
Hi there,
I have the following sub that basically;-
- looks to two paticular cells on my spreadsheet
- uses these and a set piece of text to autofill the filename box of the 'Save As' screen
What i am looking to do is convert the format of the data from a cell the code looks to, into a different type.
Here is the code;
Sub SaveToFile()
Dim filename As Variant
With ActiveSheet
If .Range("D3").Value = "" Or .Range("G9").Value = "" Then
MsgBox "Both Name and Effective Date must be entered in order to Save", vbOKOnly + vbExclamation, "Save File"
Else
filename = Range("D3").Value
filename = Mid(filename, InStr(filename, " ") + 1) & " " & _
Left(filename, InStr(filename, " ") - 1) & _
", Contract, " & Range("G9").Text
filename = Application.GetSaveAsFilename(filename, "Microsoft Excel Files (*.xls), *.xls")
If filename <> False Then
ActiveWorkbook.SaveAs filename
End If
End If
End With
End Sub
The cell is G9 and always contains a date in the format 28 July 2003. I would like the code to convert it into 28/07/03 and use that in the rest of the code?
Could someone tell me how i would build this in?
Thanks in advance!
I have the following sub that basically;-
- looks to two paticular cells on my spreadsheet
- uses these and a set piece of text to autofill the filename box of the 'Save As' screen
What i am looking to do is convert the format of the data from a cell the code looks to, into a different type.
Here is the code;
Sub SaveToFile()
Dim filename As Variant
With ActiveSheet
If .Range("D3").Value = "" Or .Range("G9").Value = "" Then
MsgBox "Both Name and Effective Date must be entered in order to Save", vbOKOnly + vbExclamation, "Save File"
Else
filename = Range("D3").Value
filename = Mid(filename, InStr(filename, " ") + 1) & " " & _
Left(filename, InStr(filename, " ") - 1) & _
", Contract, " & Range("G9").Text
filename = Application.GetSaveAsFilename(filename, "Microsoft Excel Files (*.xls), *.xls")
If filename <> False Then
ActiveWorkbook.SaveAs filename
End If
End If
End With
End Sub
The cell is G9 and always contains a date in the format 28 July 2003. I would like the code to convert it into 28/07/03 and use that in the rest of the code?
Could someone tell me how i would build this in?
Thanks in advance!