Replacing a hardcoded date
 

In the Figure 1 code below, how do I substitute a date that was
captured as a variable for the hard coded date of 20120106 that you
see in at the end of the path? My code to capture a date as a variable
is in Figure 2 below. Thank you.

Figure 1.

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

Figure 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

Replacing a hardcoded date
 

If the date is in the string variable "x" then it would be:

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\g701942\My Documents\Supplier
Resource\" & x & " Weekly Supp Res-Ind Contr List-full.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

You don't need the ChDir() to save the file the it will work fine
without.

Tim


On Jan 11, 2:08*pm, John Menken wrote:
In the Figure 1 code below, how do I substitute a date that was
captured as a variable for the hard coded date of 20120106 that you
see in at the end of the path? My code to capture a date as a variable
is in Figure 2 below. Thank you.

Figure 1.

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

Figure 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

Replacing a hardcoded date
 

Actually, I modified the code to what you see here and that almost
gets me to where I want to be.
There is one little thing missing that I will put in another post.
Many 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