Hi all
At present I have a set of scripts that capture data from a MOM database and then export these figures to a word document. i would like to change this to be an Excel doc. I have made some changes to the script but cannot get the output to appear in the XLS file that gets created. If possible could someone have a look and see what im missing.
im under the impression that its probably the "objsel" part of the script thats the issue. Below is the original 'Word' vb script and below that is the modified but non working version. (it captures the data opens and creates the xls file but does not replace the variables with actual figures). The variables stored in the excel file look like this :
'On Error Resume Next
Dim strStatsFolder,strChartsFolder
Dim strCounterName, strHoursAgo ,strServer
Dim objSQLConnection,objSQLRecSet1,objSQLRecSet2,objSQLRecSet3,objSQLRecSet4,objSQLRecSet5
Dim oArgs
Set oArgs = WScript.Arguments
If oArgs.Count < 3 Then
Wscript.Echo "Please supply Start_Date(dd/mm/yyyy) End_Date(dd/mm/yyyy) and report_suffix"
Wscript.Echo "e.g. 01/03/2007 07/03/2007 20070313"
Wscript.Quit
Else
strDateStart = oArgs(0)
strDateEnd = oArgs(1)
strReportSuff = oArgs(2)
strPerfObject = "LogicalDisk"
strCounterName = "% Free Space"
End If
strSQLDateStart = Mid(strDateStart,7,4)&"-"&Mid(strDateStart,4,2)&"-"&Mid(strDateStart,1,2)&"T00:00:00"
strSQLDateEnd = Mid(strDateEnd,7,4)&"-"&Mid(strDateEnd,4,2)&"-"&Mid(strDateEnd,1,2)&"T23:59:59"
strPrevWE = DateAdd("d",-1,dateserial(Mid(strDateStart,7,4),Mid(strDateStart,4,2),Mid(strDateStart,1,2)))
Dim DebugMode
Set objSQLConnection = WScript.CreateObject("ADODB.Connection")
Set objSQLRecSet1 = WScript.CreateObject("ADODB.Recordset")
Set objSQLRecSet2 = WScript.CreateObject("ADODB.Recordset")
strConnectString = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=HAVWA506;DATABASE=OnePoint;UID=xxxxxxx;PWD=xxxxx"
strSQLread1 = "select distinct(performanceinstancename) as INSTANCENAME from sdkperformanceview with(nolock) " & _
"where ComputerName IN ('DUNWF501') " & _
" and timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='"&strPerfObject&"' and " & _
"performancecountername='"&strCounterName&"' and " & _
" performanceinstancename in ('D:','G:')" & _
" order by performanceinstancename;"
strSQLread2 = "select count(distinct(performanceinstancename)) as INSTANCENAMECNT from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and ComputerName IN ('DUNWF501') and " & _
" performanceinstancename in ('D:','G:') and " & _
" performancecountername='"&strCounterName&"' and performanceobjectname='"&strPerfObject&"';"
With objSQLConnection
.CommandTimeOut = 900
.ConnectionString = strConnectString
.Open
End With
Wscript.Echo "Extracting SQL Data"
'Wscript.Echo "................... Instances"
Set objSQLRecSet1 = objSQLConnection.Execute(strSQLread1)
'Wscript.Echo "................... Instance Count"
Set objSQLRecSet2 = objSQLConnection.Execute(strSQLread2)
Wscript.Echo "Processing SQL Data"
Dim arrInstances()
'Dim arrSamples()
intInstancesCount = 0
intSampCount = 0
'
' find size of drive list & create array
'
intNumInstances = objSQLRecSet2.Fields("INSTANCENAMECNT")
wscript.echo "Number of instances = "&intNumInstances
If intNumInstances < 1 Then
intNumInstances = 1
End If
ReDim preserve arrInstances(intNumInstances)
Do While Not objSQLRecSet1.eof
strDisk = objSQLRecSet1.Fields("INSTANCENAME")
If IsNull(strDisk) Then
strDisk = strCounterName
End If
arrInstances(intInstancesCount) = GetStats(strDisk,strSQLDateStart,strSQLDateEnd)
intInstancesCount = intInstancesCount + 1
objSQLRecSet1.movenext
Loop
Dim WordDoc,WordSel,objDoc,HeadSel
Const WreplaceAll=2
Set WordDoc = CreateObject("Word.Application")
WordDoc.Visible = False
Set objDoc = WordDoc.Documents.Open("\\xxxxx\shared\OperInfr\Enterprise Systems Management\Capacity and Performance Management\Windows\SGN Report\RS_cap_plan_report_1_template.doc")
Set WordSel = WordDoc.Selection
On Error Resume Next
For Each strInstance in arrInstances
strRep = ReplaceTokens(strInstance)
Next
WordSel.Find.Text = "$PREV$"
WordSel.Find.Forward = TRUE
WordSel.Find.MatchWholeWord = TRUE
WordSel.Find.Replacement.Text = strPrevWE
WordSel.Find.Execute ,,,,,,,,,,2
WordSel.Find.Text = "$START$"
WordSel.Find.Forward = TRUE
WordSel.Find.MatchWholeWord = TRUE
WordSel.Find.Replacement.Text = strDateStart
WordSel.Find.Execute ,,,,,,,,,,2
WordSel.Find.Text = "$END$"
WordSel.Find.Forward = TRUE
WordSel.Find.MatchWholeWord = TRUE
WordSel.Find.Replacement.Text = strDateEnd
WordSel.Find.Execute ,,,,,,,,,,2
'Now process the sections and blat the footers
For Each objSection in objDoc.Sections
For Each objFooter in objSection.Footers
Set WordSel = objFooter.Range
WordSel.Find.Text = "$END$"
WordSel.Find.Forward = TRUE
WordSel.Find.MatchWholeWord = FALSE
WordSel.Find.Replacement.Text = strDateEnd
WordSel.Find.Execute ,,,,,,,,,,2
Next
Next
objDoc.SaveAs("\\dfswz002\shared\OperInfr\Enterprise Systems Management\Capacity and Performance Management\Windows\SGN Report\RS_cap_plan_report_1_"&strReportSuff&".DOC")
WordDoc.Documents.Close False
WordDoc.Quit
WScript.Echo "Processing Complete"
WScript.Quit 0
Private Function ReplaceTokens(strStats)
Dim arrToken
arrToken = Split(strStats,",")
Dim arrFind,arrPos
arrFind = Array("$DF"&Left(arrToken(0),1)&"11$","$DF"&Left(arrToken(0),1)&"12$", _
"$DF"&Left(arrToken(0),1)&"13$","$DF"&Left(arrToken(0),1)&"14$", _
"$DF"&Left(arrToken(0),1)&"15$")
arrPos = Array(2,4,6,8,9)
Const WreplaceAll = 2
intPos = 0
For Each strFind in arrFind
WordSel.Find.Text = strFind
WordSel.Find.Forward = TRUE
WordSel.Find.MatchWholeWord = TRUE
WordSel.Find.Replacement.Text = arrToken(arrPos(intPos))
WordSel.Find.Execute ,,,,,,,,,,WreplaceAll
intPos = intPos + 1
Next
ReplaceTokens = 1
End Function
Private Function GetStats(strInstance,strSQLDateStart,strSQLDateEnd)
strSQLreadA = "select timesampled as TIMESTART,sampledvalue as VALSTART from sdkperformanceview with(nolock) " & _
"where timesampled = ( select MIN(timesampled) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadB = "select timesampled as TIMEEND,sampledvalue as VALEND from sdkperformanceview with(nolock) " & _
"where timesampled = ( select MAX(timesampled) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadC = "select timesampled as TIMEMAX,sampledvalue as VALMAX from sdkperformanceview with(nolock) " & _
"where sampledvalue = ( select MAX(sampledvalue) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadD = "select timesampled as TIMEMIN,sampledvalue as VALMIN from sdkperformanceview with(nolock) " & _
"where sampledvalue = (select MIN(sampledvalue) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
Dim objSQLRecSetA,objSQLRecSetB,objSQLRecSetC,objSQLRecSetD
Set objSQLRecSetA = objSQLConnection.Execute(strSQLreadA)
Set objSQLRecSetB = objSQLConnection.Execute(strSQLreadB)
Set objSQLRecSetC = objSQLConnection.Execute(strSQLreadC)
Set objSQLRecSetD = objSQLConnection.Execute(strSQLreadD)
intValStart = FixRound(objSQLRecSetA.Fields("VALSTART"),2)
intTimStart = objSQLRecSetA.Fields("TIMESTART")
intValEnd = FixRound(objSQLRecSetB.Fields("VALEND"),2)
intTimEnd = objSQLRecSetB.Fields("TIMEEND")
intValMax = FixRound(objSQLRecSetC.Fields("VALMAX"),2)
intTimMax = objSQLRecSetC.Fields("TIMEMAX")
intValMin = FixRound(objSQLRecSetD.Fields("VALMIN"),2)
intTimMin = objSQLRecSetD.Fields("TIMEMIN")
intDelta = Round((intValEnd - intValStart),2)
plusSign = "+"
If intDelta < 0 Then
plusSign = ""
End If
WScript.Echo strInstance&","&intTimStart&","&intValStart&","&intTimEnd&","&intValEnd&","&intTimMax&","&intValMax&","&intTimMin&","&intValMin&","&plusSign&intDelta
GetStats = strInstance&","&intTimStart&","&intValStart&","&intTimEnd&","&intValEnd&","&intTimMax&","&intValMax&","&intTimMin&","&intValMin&","&intDelta
End Function
Private Function FixRound(numVal,numPlaces)
'
' Return Number Rounded to a Fixed Number of decimal places padded with trailing zeroes
'
minus = "-"
If numVal >= 0 Then
minus = ""
End If
numRound = Round(numVal,numPlaces)
' get fixed portion (before point)
numFix = Fix(numRound)
'get decimal part
numDec = Mid(CStr(Abs(numRound)-Abs(numFix)),3)
'pad decimal part to fixed length
pad = ""
For i = 1 to numPlaces
pad = pad&"0"
Next
numDec = Left(numDec&pad,numPlaces)
FixRound = (numFix&"."&numDec)
If FixRound > 0 And minus = "-" Then
FixRound = minus&FixRound
End If
End Function
=====================================
modified Script
'On Error Resume Next
Dim strStatsFolder,strChartsFolder
Dim strCounterName, strHoursAgo ,strServer
Dim objSQLConnection,objSQLRecSet1,objSQLRecSet2,objSQLRecSet3,objSQLRecSet4,objSQLRecSet5
Dim oArgs
Set oArgs = WScript.Arguments
If oArgs.Count < 3 Then
Wscript.Echo "Please supply Start_Date(dd/mm/yyyy) End_Date(dd/mm/yyyy) and report_suffix"
Wscript.Echo "e.g. 01/03/2007 07/03/2007 20070313"
Wscript.Quit
Else
strDateStart = oArgs(0)
strDateEnd = oArgs(1)
strReportSuff = oArgs(2)
strPerfObject = "LogicalDisk"
strCounterName = "% Free Space"
End If
strSQLDateStart = Mid(strDateStart,7,4)&"-"&Mid(strDateStart,4,2)&"-"&Mid(strDateStart,1,2)&"T00:00:00"
strSQLDateEnd = Mid(strDateEnd,7,4)&"-"&Mid(strDateEnd,4,2)&"-"&Mid(strDateEnd,1,2)&"T23:59:59"
strPrevWE = DateAdd("d",-1,dateserial(Mid(strDateStart,7,4),Mid(strDateStart,4,2),Mid(strDateStart,1,2)))
Dim DebugMode
Set objSQLConnection = WScript.CreateObject("ADODB.Connection")
Set objSQLRecSet1 = WScript.CreateObject("ADODB.Recordset")
Set objSQLRecSet2 = WScript.CreateObject("ADODB.Recordset")
strConnectString = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=HAVWA506;DATABASE=OnePoint;UID=xxxxxx;PWD=xxxxxxx"
strSQLread1 = "select distinct(performanceinstancename) as INSTANCENAME from sdkperformanceview with(nolock) " & _
"where ComputerName IN ('DUNWF501') " & _
" and timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='"&strPerfObject&"' and " & _
"performancecountername='"&strCounterName&"' and " & _
" performanceinstancename in ('D:','G:')" & _
" order by performanceinstancename;"
strSQLread2 = "select count(distinct(performanceinstancename)) as INSTANCENAMECNT from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and ComputerName IN ('DUNWF501') and " & _
" performanceinstancename in ('D:','G:') and " & _
" performancecountername='"&strCounterName&"' and performanceobjectname='"&strPerfObject&"';"
With objSQLConnection
.CommandTimeOut = 900
.ConnectionString = strConnectString
.Open
End With
Wscript.Echo "Extracting SQL Data"
'Wscript.Echo "................... Instances"
Set objSQLRecSet1 = objSQLConnection.Execute(strSQLread1)
'Wscript.Echo "................... Instance Count"
Set objSQLRecSet2 = objSQLConnection.Execute(strSQLread2)
Wscript.Echo "Processing SQL Data"
Dim arrInstances()
'Dim arrSamples()
intInstancesCount = 0
intSampCount = 0
'
' find size of drive list & create array
'
intNumInstances = objSQLRecSet2.Fields("INSTANCENAMECNT")
wscript.echo "Number of instances = "&intNumInstances
If intNumInstances < 1 Then
intNumInstances = 1
End If
ReDim preserve arrInstances(intNumInstances)
Do While Not objSQLRecSet1.eof
strDisk = objSQLRecSet1.Fields("INSTANCENAME")
If IsNull(strDisk) Then
strDisk = strCounterName
End If
arrInstances(intInstancesCount) = GetStats(strDisk,strSQLDateStart,strSQLDateEnd)
intInstancesCount = intInstancesCount + 1
objSQLRecSet1.movenext
Loop
Dim objExcel,objsel,objworkbook,HeadSel
Const WreplaceAll=2
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
Set objworkbook = objExcel.workbooks.Open("\\dfswz002\shared\OperInfr\Enterprise Systems Management\Capacity and Performance Management\Windows\SGN Report\unprocessed\RS_cap_plan_report_1_template.xls")
Set objsel = objExcel.Selection
On Error Resume Next
For Each strInstance in arrInstances
strRep = ReplaceTokens(strInstance)
Next
objsel.Find.Text = "$PREV$"
objsel.Find.Forward = TRUE
objsel.Find.MatchWholeWord = TRUE
objsel.Find.Replacement.Text = strPrevWE
objsel.Find.Execute ,,,,,,,,,,2
objsel.Find.Text = "$START$"
objsel.Find.Forward = TRUE
objsel.Find.MatchWholeWord = TRUE
objsel.Find.Replacement.Text = strDateStart
objsel.Find.Execute ,,,,,,,,,,2
objsel.Find.Text = "$END$"
objsel.Find.Forward = TRUE
objsel.Find.MatchWholeWord = TRUE
objsel.Find.Replacement.Text = strDateEnd
objsel.Find.Execute ,,,,,,,,,,2
'Now process the sections and blat the footers
For Each objSection in objDoc.Sections
For Each objFooter in objSection.Footers
Set objsel = objFooter.Range
objsel.Find.Text = "$END$"
objsel.Find.Forward = TRUE
objsel.Find.MatchWholeWord = FALSE
objsel.Find.Replacement.Text = strDateEnd
objsel.Find.Execute ,,,,,,,,,,2
Next
Next
objworkbook.SaveAs("xxxxxx\shared\OperInfr\Enterprise Systems Management\Capacity and Performance Management\Windows\SGN Report\unprocessed\RS_cap_plan_report_1_"&strReportSuff&".xls")
objExcel.workbook.Close False
objExcel.Quit
WScript.Echo "Processing Complete"
WScript.Quit 0
Private Function ReplaceTokens(strStats)
Dim arrToken
arrToken = Split(strStats,",")
Dim arrFind,arrPos
arrFind = Array("$DF"&Left(arrToken(0),1)&"11$","$DF"&Left(arrToken(0),1)&"12$", _
"$DF"&Left(arrToken(0),1)&"13$","$DF"&Left(arrToken(0),1)&"14$", _
"$DF"&Left(arrToken(0),1)&"15$")
arrPos = Array(2,4,6,8,9)
Const WreplaceAll = 2
intPos = 0
For Each strFind in arrFind
objsel.find.Text = strFind
objsel.find.Forward = TRUE
objsel.find.MatchWholeWord = TRUE
objsel.find.Replacement.Text = arrToken(arrPos(intPos))
objsel.find.Execute ,,,,,,,,,,WreplaceAll
intPos = intPos + 1
Next
ReplaceTokens = 1
End Function
Private Function GetStats(strInstance,strSQLDateStart,strSQLDateEnd)
strSQLreadA = "select timesampled as TIMESTART,sampledvalue as VALSTART from sdkperformanceview with(nolock) " & _
"where timesampled = ( select MIN(timesampled) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadB = "select timesampled as TIMEEND,sampledvalue as VALEND from sdkperformanceview with(nolock) " & _
"where timesampled = ( select MAX(timesampled) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadC = "select timesampled as TIMEMAX,sampledvalue as VALMAX from sdkperformanceview with(nolock) " & _
"where sampledvalue = ( select MAX(sampledvalue) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadD = "select timesampled as TIMEMIN,sampledvalue as VALMIN from sdkperformanceview with(nolock) " & _
"where sampledvalue = (select MIN(sampledvalue) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
Dim objSQLRecSetA,objSQLRecSetB,objSQLRecSetC,objSQLRecSetD
Set objSQLRecSetA = objSQLConnection.Execute(strSQLreadA)
Set objSQLRecSetB = objSQLConnection.Execute(strSQLreadB)
Set objSQLRecSetC = objSQLConnection.Execute(strSQLreadC)
Set objSQLRecSetD = objSQLConnection.Execute(strSQLreadD)
intValStart = FixRound(objSQLRecSetA.Fields("VALSTART"),2)
intTimStart = objSQLRecSetA.Fields("TIMESTART")
intValEnd = FixRound(objSQLRecSetB.Fields("VALEND"),2)
intTimEnd = objSQLRecSetB.Fields("TIMEEND")
intValMax = FixRound(objSQLRecSetC.Fields("VALMAX"),2)
intTimMax = objSQLRecSetC.Fields("TIMEMAX")
intValMin = FixRound(objSQLRecSetD.Fields("VALMIN"),2)
intTimMin = objSQLRecSetD.Fields("TIMEMIN")
intDelta = Round((intValEnd - intValStart),2)
plusSign = "+"
If intDelta < 0 Then
plusSign = ""
End If
WScript.Echo strInstance&","&intTimStart&","&intValStart&","&intTimEnd&","&intValEnd&","&intTimMax&","&intValMax&","&intTimMin&","&intValMin&","&plusSign&intDelta
GetStats = strInstance&","&intTimStart&","&intValStart&","&intTimEnd&","&intValEnd&","&intTimMax&","&intValMax&","&intTimMin&","&intValMin&","&intDelta
End Function
Private Function FixRound(numVal,numPlaces)
'
' Return Number Rounded to a Fixed Number of decimal places padded with trailing zeroes
'
minus = "-"
If numVal >= 0 Then
minus = ""
End If
numRound = Round(numVal,numPlaces)
' get fixed portion (before point)
numFix = Fix(numRound)
'get decimal part
numDec = Mid(CStr(Abs(numRound)-Abs(numFix)),3)
'pad decimal part to fixed length
pad = ""
For i = 1 to numPlaces
pad = pad&"0"
Next
numDec = Left(numDec&pad,numPlaces)
FixRound = (numFix&"."&numDec)
If FixRound > 0 And minus = "-" Then
FixRound = minus&FixRound
End If
End Function
------------------------------------
thanks for reading this far .. any help is greatly appreciated
Phil
At present I have a set of scripts that capture data from a MOM database and then export these figures to a word document. i would like to change this to be an Excel doc. I have made some changes to the script but cannot get the output to appear in the XLS file that gets created. If possible could someone have a look and see what im missing.
im under the impression that its probably the "objsel" part of the script thats the issue. Below is the original 'Word' vb script and below that is the modified but non working version. (it captures the data opens and creates the xls file but does not replace the variables with actual figures). The variables stored in the excel file look like this :
<TABLE dir=ltr cellSpacing=0 cellPadding=2 width=524 border=0><TBODY><TR><TD width="23%" height=16>DUNWF501
</TD></TR><TR><TD width="23%" height=16>
</TD></TR></TBODY></TABLE>
Word script :
</TD><TD width="13%" height=16>D
</TD><TD width="13%" height=16>$DFD11$
</TD><TD width="13%" height=16>$DFD12$
</TD><TD width="13%" height=16>$DFD13$
</TD><TD width="13%" height=16>$DFD14$
</TD><TD width="13%" height=16>$DFD15$</TD></TR><TR><TD width="23%" height=16>
</TD><TD width="13%" height=16>G
</TD><TD width="13%" height=16>$DFG11$
</TD><TD width="13%" height=16>$DFG12$
</TD><TD width="13%" height=16>$DFG13$
</TD><TD width="13%" height=16>$DFG14$
</TD><TD width="13%" height=16>$DFG15$</TD></TR></TBODY></TABLE>
'On Error Resume Next
Dim strStatsFolder,strChartsFolder
Dim strCounterName, strHoursAgo ,strServer
Dim objSQLConnection,objSQLRecSet1,objSQLRecSet2,objSQLRecSet3,objSQLRecSet4,objSQLRecSet5
Dim oArgs
Set oArgs = WScript.Arguments
If oArgs.Count < 3 Then
Wscript.Echo "Please supply Start_Date(dd/mm/yyyy) End_Date(dd/mm/yyyy) and report_suffix"
Wscript.Echo "e.g. 01/03/2007 07/03/2007 20070313"
Wscript.Quit
Else
strDateStart = oArgs(0)
strDateEnd = oArgs(1)
strReportSuff = oArgs(2)
strPerfObject = "LogicalDisk"
strCounterName = "% Free Space"
End If
strSQLDateStart = Mid(strDateStart,7,4)&"-"&Mid(strDateStart,4,2)&"-"&Mid(strDateStart,1,2)&"T00:00:00"
strSQLDateEnd = Mid(strDateEnd,7,4)&"-"&Mid(strDateEnd,4,2)&"-"&Mid(strDateEnd,1,2)&"T23:59:59"
strPrevWE = DateAdd("d",-1,dateserial(Mid(strDateStart,7,4),Mid(strDateStart,4,2),Mid(strDateStart,1,2)))
Dim DebugMode
Set objSQLConnection = WScript.CreateObject("ADODB.Connection")
Set objSQLRecSet1 = WScript.CreateObject("ADODB.Recordset")
Set objSQLRecSet2 = WScript.CreateObject("ADODB.Recordset")
strConnectString = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=HAVWA506;DATABASE=OnePoint;UID=xxxxxxx;PWD=xxxxx"
strSQLread1 = "select distinct(performanceinstancename) as INSTANCENAME from sdkperformanceview with(nolock) " & _
"where ComputerName IN ('DUNWF501') " & _
" and timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='"&strPerfObject&"' and " & _
"performancecountername='"&strCounterName&"' and " & _
" performanceinstancename in ('D:','G:')" & _
" order by performanceinstancename;"
strSQLread2 = "select count(distinct(performanceinstancename)) as INSTANCENAMECNT from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and ComputerName IN ('DUNWF501') and " & _
" performanceinstancename in ('D:','G:') and " & _
" performancecountername='"&strCounterName&"' and performanceobjectname='"&strPerfObject&"';"
With objSQLConnection
.CommandTimeOut = 900
.ConnectionString = strConnectString
.Open
End With
Wscript.Echo "Extracting SQL Data"
'Wscript.Echo "................... Instances"
Set objSQLRecSet1 = objSQLConnection.Execute(strSQLread1)
'Wscript.Echo "................... Instance Count"
Set objSQLRecSet2 = objSQLConnection.Execute(strSQLread2)
Wscript.Echo "Processing SQL Data"
Dim arrInstances()
'Dim arrSamples()
intInstancesCount = 0
intSampCount = 0
'
' find size of drive list & create array
'
intNumInstances = objSQLRecSet2.Fields("INSTANCENAMECNT")
wscript.echo "Number of instances = "&intNumInstances
If intNumInstances < 1 Then
intNumInstances = 1
End If
ReDim preserve arrInstances(intNumInstances)
Do While Not objSQLRecSet1.eof
strDisk = objSQLRecSet1.Fields("INSTANCENAME")
If IsNull(strDisk) Then
strDisk = strCounterName
End If
arrInstances(intInstancesCount) = GetStats(strDisk,strSQLDateStart,strSQLDateEnd)
intInstancesCount = intInstancesCount + 1
objSQLRecSet1.movenext
Loop
Dim WordDoc,WordSel,objDoc,HeadSel
Const WreplaceAll=2
Set WordDoc = CreateObject("Word.Application")
WordDoc.Visible = False
Set objDoc = WordDoc.Documents.Open("\\xxxxx\shared\OperInfr\Enterprise Systems Management\Capacity and Performance Management\Windows\SGN Report\RS_cap_plan_report_1_template.doc")
Set WordSel = WordDoc.Selection
On Error Resume Next
For Each strInstance in arrInstances
strRep = ReplaceTokens(strInstance)
Next
WordSel.Find.Text = "$PREV$"
WordSel.Find.Forward = TRUE
WordSel.Find.MatchWholeWord = TRUE
WordSel.Find.Replacement.Text = strPrevWE
WordSel.Find.Execute ,,,,,,,,,,2
WordSel.Find.Text = "$START$"
WordSel.Find.Forward = TRUE
WordSel.Find.MatchWholeWord = TRUE
WordSel.Find.Replacement.Text = strDateStart
WordSel.Find.Execute ,,,,,,,,,,2
WordSel.Find.Text = "$END$"
WordSel.Find.Forward = TRUE
WordSel.Find.MatchWholeWord = TRUE
WordSel.Find.Replacement.Text = strDateEnd
WordSel.Find.Execute ,,,,,,,,,,2
'Now process the sections and blat the footers
For Each objSection in objDoc.Sections
For Each objFooter in objSection.Footers
Set WordSel = objFooter.Range
WordSel.Find.Text = "$END$"
WordSel.Find.Forward = TRUE
WordSel.Find.MatchWholeWord = FALSE
WordSel.Find.Replacement.Text = strDateEnd
WordSel.Find.Execute ,,,,,,,,,,2
Next
Next
objDoc.SaveAs("\\dfswz002\shared\OperInfr\Enterprise Systems Management\Capacity and Performance Management\Windows\SGN Report\RS_cap_plan_report_1_"&strReportSuff&".DOC")
WordDoc.Documents.Close False
WordDoc.Quit
WScript.Echo "Processing Complete"
WScript.Quit 0
Private Function ReplaceTokens(strStats)
Dim arrToken
arrToken = Split(strStats,",")
Dim arrFind,arrPos
arrFind = Array("$DF"&Left(arrToken(0),1)&"11$","$DF"&Left(arrToken(0),1)&"12$", _
"$DF"&Left(arrToken(0),1)&"13$","$DF"&Left(arrToken(0),1)&"14$", _
"$DF"&Left(arrToken(0),1)&"15$")
arrPos = Array(2,4,6,8,9)
Const WreplaceAll = 2
intPos = 0
For Each strFind in arrFind
WordSel.Find.Text = strFind
WordSel.Find.Forward = TRUE
WordSel.Find.MatchWholeWord = TRUE
WordSel.Find.Replacement.Text = arrToken(arrPos(intPos))
WordSel.Find.Execute ,,,,,,,,,,WreplaceAll
intPos = intPos + 1
Next
ReplaceTokens = 1
End Function
Private Function GetStats(strInstance,strSQLDateStart,strSQLDateEnd)
strSQLreadA = "select timesampled as TIMESTART,sampledvalue as VALSTART from sdkperformanceview with(nolock) " & _
"where timesampled = ( select MIN(timesampled) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadB = "select timesampled as TIMEEND,sampledvalue as VALEND from sdkperformanceview with(nolock) " & _
"where timesampled = ( select MAX(timesampled) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadC = "select timesampled as TIMEMAX,sampledvalue as VALMAX from sdkperformanceview with(nolock) " & _
"where sampledvalue = ( select MAX(sampledvalue) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadD = "select timesampled as TIMEMIN,sampledvalue as VALMIN from sdkperformanceview with(nolock) " & _
"where sampledvalue = (select MIN(sampledvalue) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
Dim objSQLRecSetA,objSQLRecSetB,objSQLRecSetC,objSQLRecSetD
Set objSQLRecSetA = objSQLConnection.Execute(strSQLreadA)
Set objSQLRecSetB = objSQLConnection.Execute(strSQLreadB)
Set objSQLRecSetC = objSQLConnection.Execute(strSQLreadC)
Set objSQLRecSetD = objSQLConnection.Execute(strSQLreadD)
intValStart = FixRound(objSQLRecSetA.Fields("VALSTART"),2)
intTimStart = objSQLRecSetA.Fields("TIMESTART")
intValEnd = FixRound(objSQLRecSetB.Fields("VALEND"),2)
intTimEnd = objSQLRecSetB.Fields("TIMEEND")
intValMax = FixRound(objSQLRecSetC.Fields("VALMAX"),2)
intTimMax = objSQLRecSetC.Fields("TIMEMAX")
intValMin = FixRound(objSQLRecSetD.Fields("VALMIN"),2)
intTimMin = objSQLRecSetD.Fields("TIMEMIN")
intDelta = Round((intValEnd - intValStart),2)
plusSign = "+"
If intDelta < 0 Then
plusSign = ""
End If
WScript.Echo strInstance&","&intTimStart&","&intValStart&","&intTimEnd&","&intValEnd&","&intTimMax&","&intValMax&","&intTimMin&","&intValMin&","&plusSign&intDelta
GetStats = strInstance&","&intTimStart&","&intValStart&","&intTimEnd&","&intValEnd&","&intTimMax&","&intValMax&","&intTimMin&","&intValMin&","&intDelta
End Function
Private Function FixRound(numVal,numPlaces)
'
' Return Number Rounded to a Fixed Number of decimal places padded with trailing zeroes
'
minus = "-"
If numVal >= 0 Then
minus = ""
End If
numRound = Round(numVal,numPlaces)
' get fixed portion (before point)
numFix = Fix(numRound)
'get decimal part
numDec = Mid(CStr(Abs(numRound)-Abs(numFix)),3)
'pad decimal part to fixed length
pad = ""
For i = 1 to numPlaces
pad = pad&"0"
Next
numDec = Left(numDec&pad,numPlaces)
FixRound = (numFix&"."&numDec)
If FixRound > 0 And minus = "-" Then
FixRound = minus&FixRound
End If
End Function
=====================================
modified Script
'On Error Resume Next
Dim strStatsFolder,strChartsFolder
Dim strCounterName, strHoursAgo ,strServer
Dim objSQLConnection,objSQLRecSet1,objSQLRecSet2,objSQLRecSet3,objSQLRecSet4,objSQLRecSet5
Dim oArgs
Set oArgs = WScript.Arguments
If oArgs.Count < 3 Then
Wscript.Echo "Please supply Start_Date(dd/mm/yyyy) End_Date(dd/mm/yyyy) and report_suffix"
Wscript.Echo "e.g. 01/03/2007 07/03/2007 20070313"
Wscript.Quit
Else
strDateStart = oArgs(0)
strDateEnd = oArgs(1)
strReportSuff = oArgs(2)
strPerfObject = "LogicalDisk"
strCounterName = "% Free Space"
End If
strSQLDateStart = Mid(strDateStart,7,4)&"-"&Mid(strDateStart,4,2)&"-"&Mid(strDateStart,1,2)&"T00:00:00"
strSQLDateEnd = Mid(strDateEnd,7,4)&"-"&Mid(strDateEnd,4,2)&"-"&Mid(strDateEnd,1,2)&"T23:59:59"
strPrevWE = DateAdd("d",-1,dateserial(Mid(strDateStart,7,4),Mid(strDateStart,4,2),Mid(strDateStart,1,2)))
Dim DebugMode
Set objSQLConnection = WScript.CreateObject("ADODB.Connection")
Set objSQLRecSet1 = WScript.CreateObject("ADODB.Recordset")
Set objSQLRecSet2 = WScript.CreateObject("ADODB.Recordset")
strConnectString = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=HAVWA506;DATABASE=OnePoint;UID=xxxxxx;PWD=xxxxxxx"
strSQLread1 = "select distinct(performanceinstancename) as INSTANCENAME from sdkperformanceview with(nolock) " & _
"where ComputerName IN ('DUNWF501') " & _
" and timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='"&strPerfObject&"' and " & _
"performancecountername='"&strCounterName&"' and " & _
" performanceinstancename in ('D:','G:')" & _
" order by performanceinstancename;"
strSQLread2 = "select count(distinct(performanceinstancename)) as INSTANCENAMECNT from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and ComputerName IN ('DUNWF501') and " & _
" performanceinstancename in ('D:','G:') and " & _
" performancecountername='"&strCounterName&"' and performanceobjectname='"&strPerfObject&"';"
With objSQLConnection
.CommandTimeOut = 900
.ConnectionString = strConnectString
.Open
End With
Wscript.Echo "Extracting SQL Data"
'Wscript.Echo "................... Instances"
Set objSQLRecSet1 = objSQLConnection.Execute(strSQLread1)
'Wscript.Echo "................... Instance Count"
Set objSQLRecSet2 = objSQLConnection.Execute(strSQLread2)
Wscript.Echo "Processing SQL Data"
Dim arrInstances()
'Dim arrSamples()
intInstancesCount = 0
intSampCount = 0
'
' find size of drive list & create array
'
intNumInstances = objSQLRecSet2.Fields("INSTANCENAMECNT")
wscript.echo "Number of instances = "&intNumInstances
If intNumInstances < 1 Then
intNumInstances = 1
End If
ReDim preserve arrInstances(intNumInstances)
Do While Not objSQLRecSet1.eof
strDisk = objSQLRecSet1.Fields("INSTANCENAME")
If IsNull(strDisk) Then
strDisk = strCounterName
End If
arrInstances(intInstancesCount) = GetStats(strDisk,strSQLDateStart,strSQLDateEnd)
intInstancesCount = intInstancesCount + 1
objSQLRecSet1.movenext
Loop
Dim objExcel,objsel,objworkbook,HeadSel
Const WreplaceAll=2
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
Set objworkbook = objExcel.workbooks.Open("\\dfswz002\shared\OperInfr\Enterprise Systems Management\Capacity and Performance Management\Windows\SGN Report\unprocessed\RS_cap_plan_report_1_template.xls")
Set objsel = objExcel.Selection
On Error Resume Next
For Each strInstance in arrInstances
strRep = ReplaceTokens(strInstance)
Next
objsel.Find.Text = "$PREV$"
objsel.Find.Forward = TRUE
objsel.Find.MatchWholeWord = TRUE
objsel.Find.Replacement.Text = strPrevWE
objsel.Find.Execute ,,,,,,,,,,2
objsel.Find.Text = "$START$"
objsel.Find.Forward = TRUE
objsel.Find.MatchWholeWord = TRUE
objsel.Find.Replacement.Text = strDateStart
objsel.Find.Execute ,,,,,,,,,,2
objsel.Find.Text = "$END$"
objsel.Find.Forward = TRUE
objsel.Find.MatchWholeWord = TRUE
objsel.Find.Replacement.Text = strDateEnd
objsel.Find.Execute ,,,,,,,,,,2
'Now process the sections and blat the footers
For Each objSection in objDoc.Sections
For Each objFooter in objSection.Footers
Set objsel = objFooter.Range
objsel.Find.Text = "$END$"
objsel.Find.Forward = TRUE
objsel.Find.MatchWholeWord = FALSE
objsel.Find.Replacement.Text = strDateEnd
objsel.Find.Execute ,,,,,,,,,,2
Next
Next
objworkbook.SaveAs("xxxxxx\shared\OperInfr\Enterprise Systems Management\Capacity and Performance Management\Windows\SGN Report\unprocessed\RS_cap_plan_report_1_"&strReportSuff&".xls")
objExcel.workbook.Close False
objExcel.Quit
WScript.Echo "Processing Complete"
WScript.Quit 0
Private Function ReplaceTokens(strStats)
Dim arrToken
arrToken = Split(strStats,",")
Dim arrFind,arrPos
arrFind = Array("$DF"&Left(arrToken(0),1)&"11$","$DF"&Left(arrToken(0),1)&"12$", _
"$DF"&Left(arrToken(0),1)&"13$","$DF"&Left(arrToken(0),1)&"14$", _
"$DF"&Left(arrToken(0),1)&"15$")
arrPos = Array(2,4,6,8,9)
Const WreplaceAll = 2
intPos = 0
For Each strFind in arrFind
objsel.find.Text = strFind
objsel.find.Forward = TRUE
objsel.find.MatchWholeWord = TRUE
objsel.find.Replacement.Text = arrToken(arrPos(intPos))
objsel.find.Execute ,,,,,,,,,,WreplaceAll
intPos = intPos + 1
Next
ReplaceTokens = 1
End Function
Private Function GetStats(strInstance,strSQLDateStart,strSQLDateEnd)
strSQLreadA = "select timesampled as TIMESTART,sampledvalue as VALSTART from sdkperformanceview with(nolock) " & _
"where timesampled = ( select MIN(timesampled) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadB = "select timesampled as TIMEEND,sampledvalue as VALEND from sdkperformanceview with(nolock) " & _
"where timesampled = ( select MAX(timesampled) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadC = "select timesampled as TIMEMAX,sampledvalue as VALMAX from sdkperformanceview with(nolock) " & _
"where sampledvalue = ( select MAX(sampledvalue) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
strSQLreadD = "select timesampled as TIMEMIN,sampledvalue as VALMIN from sdkperformanceview with(nolock) " & _
"where sampledvalue = (select MIN(sampledvalue) from sdkperformanceview with(nolock) " & _
" where timesampled >= '"&strSQLDateStart&"' " & _
" and timesampled <= '"&strSQLDateEnd&"' " & _
" and performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ) and " &_
" performanceobjectname='LogicalDisk' and performancecountername = '% Free Space' " & _
" and ComputerName IN ('DUNWF501') and performanceinstancename = '"&strInstance&"' ; "
Dim objSQLRecSetA,objSQLRecSetB,objSQLRecSetC,objSQLRecSetD
Set objSQLRecSetA = objSQLConnection.Execute(strSQLreadA)
Set objSQLRecSetB = objSQLConnection.Execute(strSQLreadB)
Set objSQLRecSetC = objSQLConnection.Execute(strSQLreadC)
Set objSQLRecSetD = objSQLConnection.Execute(strSQLreadD)
intValStart = FixRound(objSQLRecSetA.Fields("VALSTART"),2)
intTimStart = objSQLRecSetA.Fields("TIMESTART")
intValEnd = FixRound(objSQLRecSetB.Fields("VALEND"),2)
intTimEnd = objSQLRecSetB.Fields("TIMEEND")
intValMax = FixRound(objSQLRecSetC.Fields("VALMAX"),2)
intTimMax = objSQLRecSetC.Fields("TIMEMAX")
intValMin = FixRound(objSQLRecSetD.Fields("VALMIN"),2)
intTimMin = objSQLRecSetD.Fields("TIMEMIN")
intDelta = Round((intValEnd - intValStart),2)
plusSign = "+"
If intDelta < 0 Then
plusSign = ""
End If
WScript.Echo strInstance&","&intTimStart&","&intValStart&","&intTimEnd&","&intValEnd&","&intTimMax&","&intValMax&","&intTimMin&","&intValMin&","&plusSign&intDelta
GetStats = strInstance&","&intTimStart&","&intValStart&","&intTimEnd&","&intValEnd&","&intTimMax&","&intValMax&","&intTimMin&","&intValMin&","&intDelta
End Function
Private Function FixRound(numVal,numPlaces)
'
' Return Number Rounded to a Fixed Number of decimal places padded with trailing zeroes
'
minus = "-"
If numVal >= 0 Then
minus = ""
End If
numRound = Round(numVal,numPlaces)
' get fixed portion (before point)
numFix = Fix(numRound)
'get decimal part
numDec = Mid(CStr(Abs(numRound)-Abs(numFix)),3)
'pad decimal part to fixed length
pad = ""
For i = 1 to numPlaces
pad = pad&"0"
Next
numDec = Left(numDec&pad,numPlaces)
FixRound = (numFix&"."&numDec)
If FixRound > 0 And minus = "-" Then
FixRound = minus&FixRound
End If
End Function
------------------------------------
thanks for reading this far .. any help is greatly appreciated
Phil
