Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use Find without hardcoded value but a value from a cell | Excel Worksheet Functions | |||
Replacing a date in date format. | Excel Programming | |||
Peculiar date format behavior when replacing parts of date | Excel Discussion (Misc queries) | |||
Alternative to hardcoded password ? | Excel Programming | |||
Change hardcoded filename | Excel Programming |