how to make macro not dependent on datafile which has date as its part.

[haisat]

Hi,
I am very new to Excel Macros and VBScript. But I need to write a
Macro very urgently. This macro just takes a datafile and draws a
graph. I am able to do up this point if datafile name is constant. But
datafile name keeps changing with date(in follwing script..
swapmemory_results.101703 is the datafile. here 101703 is the date
which causes change of filename everyday). Could anybody help me out
how to make macro not dependent on date. I am pasting the code
here....



Sub test2()
'
' test2 Macro
' Macro recorded 10/17/2003 by ibm user
'
' Keyboard Shortcut: Ctrl+u
'
ChDir "C:\new Applications\Tower-G"
Workbooks.OpenText FileName:= _
"C:\new Applications\Tower-G\swapmemory_results.101703",
Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Cells.Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("swapmemory_results.101703").Range( _
"A1:A29"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"swapmemory_results.101703"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Swap Memory usage - GLITR"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time
in min"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "kb"
End With
End Sub


Thanks in advance

 

[bruce]

try this;
A
Put a formula in one cell of the worksheet that gives the
file name (short or long - your decision).
B
In your code substitute a variable for the file name and
early in your code let that variable equals the value
(not the formula) in the cell where the filename formula
resides.

good luck

 

[Bob Phillips]

Haisat,

If it's always today's date, change the file string to include that, like

Sub test2()
Dim sDate As String
Dim sfile As String

sDate = Format(Date, "ddmmyy")
sfile = "swapmemory_results." & sfate

ChDir "C:\new Applications\Tower-G"
Workbooks.OpenText Filename:="C:\new Applications\Tower-G\" & sfile, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 1)
Cells.Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData _
Source:=Sheets(sfile).Range("A1:A29"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, _
Name:=sfgile
With ActiveChart
HasTitle = True
ChartTitle.Characters.Text = "Swap Memory usage - GLITR"
With Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = "Time in min"
.HasTitle = True
.AxisTitle.Characters.Text = "kb"
End With
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)