The code snip below saves information to disk. However on a date like
Jan 6, 2012 it saves the file name as "201216...." and I would like it
to be "20120106...." with a two digit month and a two digit day. Is it
possible to modify the code below so that it will do this? Thanks.


'Save the file
Range("A2").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\g701942\My Documents\Supplier
Resource\" & Year(x) & Month(x) & Day(x) & " Weekly Supp Res-Ind Contr
List-full.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

hi John,

y = 2012
m = 1
d = 6
dt = Format(DateSerial(y, m, d), "yyyymmdd")
sFileName = "C:\Documents and Settings\g701942\My Documents\SupplierResource\" & dt & " Weekly Supp Res-Ind ContrList-full.xls"



--
isabelle



Le 2012-01-13 13:47, John Menken a écrit :
The code snip below saves information to disk. However on a date like
Jan 6, 2012 it saves the file name as "201216...." and I would like it
to be "20120106...." with a two digit month and a two digit day. Is it
possible to modify the code below so that it will do this? Thanks.


'Save the file
Range("A2").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\g701942\My Documents\Supplier
Resource\"& Year(x)& Month(x)& Day(x)& " Weekly Supp Res-Ind Contr
List-full.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Isabelle,
Thank you very much for the reply.
I was remiss in mentioning that the date that I am inserting into the
file name is what is collected from a message box.
The code that I use to collect a date is in Figure 1 below.

What I want to then do is insert it as part of the file name as
mentioned above. Your code works to give me the correct number of
digits but it confines it to a single date.
Re-reading my original post, I really wasn't clear in that regard. Is
there anyway to modify your code example to allow for this message box
date? Thank you.



Figure 1.

'Formats text in yellow
'These constants control which columns to check.
Const YOS = 13
Const Region = 11
Const SP1M90D = YOS + 2
Dim x As Variant, L0 As Long
x = InputBox("What is the report date?")
If IsDate(x) Then
For L0 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
With Range("A" & CStr(L0) & ":W" & CStr(L0)).Interior
If Cells(L0, YOS).Value < 1 Then
If Cells(L0, SP1M90D).Value < CDate(x) Then
If Cells(L0, Region).Value = "NA" Then
.Color = vbYellow
Else
.Pattern = xlNone
End If
Else
.Pattern = xlNone
End If
Else
.Pattern = xlNone
End If
End With
Next
End If

if i understand correctly,


x = InputBox("What is the report date?")
If IsDate(x) Then
dt = Format(DateSerial(Year(x), Month(x), Day(x)), "yyyymmdd")
sFileName = "C:\Documents and Settings\g701942\My Documents\SupplierResource\" & dt & " Weekly Supp Res-Ind ContrList-full.xls"


--
isabelle



Le 2012-01-17 13:07, John Menken a écrit :
Isabelle,
Thank you very much for the reply.
I was remiss in mentioning that the date that I am inserting into the
file name is what is collected from a message box.
The code that I use to collect a date is in Figure 1 below.

What I want to then do is insert it as part of the file name as
mentioned above. Your code works to give me the correct number of
digits but it confines it to a single date.
Re-reading my original post, I really wasn't clear in that regard. Is
there anyway to modify your code example to allow for this message box
date? Thank you.



Figure 1.

'Formats text in yellow
'These constants control which columns to check.
Const YOS = 13
Const Region = 11
Const SP1M90D = YOS + 2
Dim x As Variant, L0 As Long
x = InputBox("What is the report date?")
If IsDate(x) Then
For L0 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
With Range("A"& CStr(L0)& ":W"& CStr(L0)).Interior
If Cells(L0, YOS).Value< 1 Then
If Cells(L0, SP1M90D).Value< CDate(x) Then
If Cells(L0, Region).Value = "NA" Then
.Color = vbYellow
Else
.Pattern = xlNone
End If
Else
.Pattern = xlNone
End If
Else
.Pattern = xlNone
End If
End With
Next
End If