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

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 Whe=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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

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
-----Original Message-----
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 Whe=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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.

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

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 Whe=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)

"haisat" wrote in message
...
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 Whe=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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/