Replacing a hardcoded date

[John Menken]

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

 

[Tim Williams]

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 there: it will work fine
without.

Tim

 

[John Menken]

Thank you.
when I try running it throws the following error:

************************************
Run-time error '1004':

Microsoft Excel cannot access the file 'C:\Documents and
Settings\g701942\My Documents\Supplier Resource\1\8\'. There are
several possible reasons:


• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a

 

[John Menken]

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