Re: Using VBS Arrays As Data Ranges In Excel
- From: tbader01 <tbader01@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 6 Aug 2007 19:42:00 -0700
Kool, thanks J.Williams! Thanks for the tip of passing the Excel Object as an
argument to the function! I have a graphing script that graphs perfmon data,
which I have been wanting to improve it. Also, thanks for the tip on Excel
visibility part. Those are the kinds of tips I am looking for to make my
scripts simpler.
I know I am brute forcing things in some of my other scripts, but that is
the only way I can get things to work initially!
I have thought about posting some of my working scripts to see how I could
make them much better and more efficient.
Let me give the updated script a try and see how it works out tomorrow...
Thanks again for all your help!
"J.Williams" wrote:
.
"tbader01" <tbader01@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:20641D0F-7E17-4B75-A64D-560BD823D849@xxxxxxxxxxxxxxxx
OK, thanks for figuring out my GIF saving problem!
I was talking to my friend this WE and came to the realization that my
problems stem from me being a scripter and not a programmer, which I think
a
lot of this stuff starts getting into!
I also figured out what my problem was with my single data plot. My
"orca_data" output file contains an entry "NaN", which my 14 line sample
didn't include.
1. This brings me to a problem I have encountered in the past that I never
have been able to figure out on my own.
I have tried to use the SkipLine method, but it skips the next line,
not
the current line read!
2. I also have another question. Should I be creating the Excel object
outside of the graphing function, especially if I was to drop quite a few
files to this script? Is having an Excel object created for each file
dropped
a bad idea?
3. Also, if multiple files are dropped to the script, should/can the wb
object be created outside of the "For file" loop?
4. Part of what I want to be able to do is to make subroutines out of the
SaveAsGif and also the Trendline I added.
1. SkipLine is like ReadLine without assigning the line read to a variable.
To skip "text" data values you first have to read the data and then decide
whether to add it to the data array. I've used IsNumeric to check for
"text" data.
2 & 3. Yes, it's better to create the Excel object and add a Workbook
outside the file loop. The Excel object is then passed as an argument to
the graphing function. You're basically using Excel as a 'scratchpad' for
creating graphs. Multiple files use the same worksheet in the same
workbook, overlaying the data and graph of the previous file.
4. Easy enough. Pass the wb workbook and output file as arguments.
PS - it's not really worth having the funDetermineExcelVisibility function
as you're just setting a single property, and it's invisible by default.
Modified code:
Option Explicit
Const ForReading = 1
Dim objFSO, objShell
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = WScript.CreateObject ("WSCript.shell")
Dim objExcelApp ' Excel application object
Set objExcelApp = WScript.CreateObject("Excel.Application")
objExcelApp.WorkBooks.Add
Dim argTXTFiles
Set argTXTFiles = WScript.Arguments
'If only a single file is dropped, then make Excel visible...
If argTXTFiles.Count = 1 Then
objExcelApp.Visible = True
End If
Dim file
For file = 0 to argTXTFiles.Count - 1
funMakeGraph objExcelApp, argTXTFiles.Unnamed(file)
Next
objExcelApp.ActiveWorkbook.Saved = True 'Prevent 'Save Changes?' prompt
objExcelApp.Quit
WScript.Quit
Function funMakeGraph(objExcel, RRDOutputFile)
'The following constants must be defined (values from MSKB Q135639)...
Const xlColumns = 2
Const xlLineMarkers = 65
Dim wb
Set wb = objExcel.ActiveWorkbook
Dim objTXTFile
Set objTXTFile = objFSO.OpenTextFile( RRDOutputFile, ForReading)
objTXTFile.SkipLine
objTXTFile.SkipLine
Dim LineCount, StringLine, arrLine, dataArray()
LineCount = 0
Do Until objTXTFile.AtEndOfStream
StringLine = objTXTFile.ReadLine
arrLine = Split(StringLine, ":")
'Use IsNumeric to check if the data value is text
' dataArray is redimensioned as (columns,rows) because ReDim allows
' only the size of the last dimension to be changed.
if IsNumeric(arrLine(1)) then
ReDim Preserve dataArray(1,LineCount)
dataArray(0,LineCount) = EPOCH2Date(arrLine(0))
dataArray(1,LineCount) = arrLine(1)
LineCount = LineCount + 1
end if
Loop
objTXTFile.Close
' Declare a range object to hold our data
Dim rng
' Now assign them all in one shot...
' The rng.Value property requires a variant.
' It's a two-dimensional array and shouldn't be fed a string.
Set rng = wb.Activesheet.Range("A1").Resize(LineCount,2)
' We then use the Transpose function to convert the dataArray into
' (row,column) order when assigning it to the resized range.
rng.Value = objExcel.Transpose(dataArray)
' Add a new chart based on the data
wb.Charts.Add
wb.ActiveChart.ChartType = xlLineMarkers
wb.ActiveChart.SetSourceData rng, xlColumns
wb.ActiveChart.Location 2, "Sheet1" 'xlLocationAsObject
'Syntax
'expression.Add(Type, Order, Period, Forward, Backward, Intercept,
DisplayEquation, DisplayRSquared, Name)
'expression Required. An expression that returns a Trendlines object.
'Type Optional Variant. The trendline type.
'Can be one of the following XlTrendlineType constants: The default value is
xlLinear.
Const xlLinear = -4132
Const xlLogarithmic = -4133
Const xlPolynomial = 3
Const xlPower = 4
Const xlExponential = 5
Const xlMovingAvg = 6
'Order - Optional Variant. Required if Type is xlPolynomial. The trendline
order.
' Must be an integer from 2 to 6, inclusive.
'Period - Optional Variant. Required if Type is xlMovingAvg. The trendline
period.
' Must be an integer greater than 1 and less than the number of data
points
' in the series youre adding a trendline to.
'Forward - Optional Variant. The number of periods (or units on a scatter
chart)
' that the trendline extends forward.
'Backward - Optional Variant. The number of periods (or units on a scatter
chart)
' that the trendline extends backward.
'Intercept - Optional Variant. The trendline intercept. If this argument is
omitted,
' the intercept is automatically set by the regression.
'DisplayEquation - Optional Variant. True to display the equation of the
trendline
' on the chart (in the same data label as the R-squared
value).
' The default value is False.
'DisplayRSquared - Optional Variant. True to display the R-squared value of
the
' trendline on the chart (in the same data label as the
equation).
' The default value is False.
'Name - Optional Variant. The name of the trendline as text.
' If this argument is omitted, Microsoft Excel generates a name.
wb.ActiveChart.SeriesCollection(1).Trendlines.Add xlLinear, , 0, LineCount,
False, False
' Save chart as a GIF file
Dim sGif
sGif = Left(RRDOutputFile, InstrRev(RRDOutputFile,".")) & "gif"
wb.ActiveChart.Export sGif
End Function
Function EPOCH2Date(EPOCHVal)
Dim CalcSecsSince1970, offsetMin, Adjust4LocalTZ
Const atb = _
"HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
CalcSecsSince1970 = DateAdd("s", EPOCHVal, "01/01/1970 00:00:00")
offsetMin = objShell.RegRead(atb)
Adjust4LocalTZ = DateAdd("n", -offsetMin, CalcSecsSince1970)
'Use FormatDateTime to get both the Date and Time in the Output...
EPOCH2Date = FormatDateTime(Adjust4LocalTZ,vbGeneralDate)
End Function
- References:
- Using VBS Arrays As Data Ranges In Excel
- From: tbader01
- Re: Using VBS Arrays As Data Ranges In Excel
- From: J.Williams
- Re: Using VBS Arrays As Data Ranges In Excel
- From: tbader01
- Re: Using VBS Arrays As Data Ranges In Excel
- From: J.Williams
- Re: Using VBS Arrays As Data Ranges In Excel
- From: tbader01
- Re: Using VBS Arrays As Data Ranges In Excel
- From: J.Williams
- Using VBS Arrays As Data Ranges In Excel
- Prev by Date: Re: how to read an ini file
- Next by Date: Re: how to read an ini file
- Previous by thread: Re: Using VBS Arrays As Data Ranges In Excel
- Next by thread: VBScript - Calculating a range in Excel
- Index(es):
Relevant Pages
|
