hi,
i`m having a bit of a problem converting the style of the date to another one.
What i`m doing is getting the contents from a cell then trimming it, then assigning it to a variable. I would like the end result to be diplayed as dd-mm-yyyy.
Initially the content of the cell is something like 12/06/06 11:15:03 AM.
I trim it down so only 12/06/06 is displayed, after this i am unable to change it to 12-06-06.
my code is as follows:
Sub ParseSaveAsName()
Dim sFileName As String
Dim datt As String
Dim directory As String
Dim strDate1 As Date
Dim strDate2 As Date
'find the date created cell
Cells.Find(What:="Date Created", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'look at the next cell down which should contain the date
ActiveCell.Offset(1, 0).Activate
'assign the cell value to a variable.
Dim variable1 As String
variable1 = ActiveCell.Value
variable1 = Left(variable1, 10)
'find the end date cell
Dim endcell As Integer
endcell = Range("B1").End(xlDown).Row
'select the datecreated row again.
Dim datecreatedcolumn As Integer
Dim variable2 As String
Cells.Find(What:="Date Created", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
datecreatedcolumn = ActiveCell.Column
Cells(endcell, datecreatedcolumn).Select
'assign to variable
variable2 = ActiveCell.Value
variable2 = Left(variable2, 10)
strDate1 = variable1
strDate1 = Format(variable1, "dd-mm-yyyy")
strDate2 = variable2
strDate2 = Format(variable2, "dd-mm-yyyy")
MsgBox (strDate1)
MsgBox (strDate2)
'directory = ("\\thorntonap002\livedata\Epos_admin\Helpdesk\Polling reports\Chris' Polling Report\" + variable1 + " - " + variable2 + " generated polling report.xls")
'sFileName = Application.GetSaveAsFilename(directory)
'They have cancelled
'If sFileName = "False" Then Exit Sub
'ActiveWorkbook.SaveAs sFileName, FileFormat:=xlNormal
End Sub
any ideas???
thanks
Bookmarks