Heres a little history to what im doing. In the abscence of MOM 2005 and the
Availability Management Pack i have written a bit of code, that will query AD
for all server objects and using the Uptime.exe resource kit tool i perform a
query of the returned objects. I then use 'findstr' to pull out the Server
Availability line from the text files and collate these into one text file. I
then open the text file and import it into excel and create a line chart
based on the results. Here lies my problem everything works well apart from i
do not know how to set the Axes for the chart/graph. I have recorded a macro
but i am unable to fathom out how to include the code from the macro into the
vbscript below to set the axes. I need the Axes minimum value to be 0 and the
maximum value to be 100.
The code below is something i pulled from the Microsoft site with a few
modifications.
Id be very greatful if someone could show me how i can achieve setting the
Axes min/max values
' ------------------------------------------------------- Create Chart/Graph
sExcelPath = "C:\GetUptime\Output\Uptime.xls"
' Create Excel Application object
Set xlApp = CreateObject("Excel.Application")
'Show
xlApp.Visible = True
' Create a new workbook
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.ActiveSheet
'Get data NB Delimited
Set rs = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")
xlCategory=1
xlColumns=2
strFolder = "C:\GetUptime\Output\"
strSQL = "Select * From Uptime.txt"
strCon = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strFolder & ";" & _
"Extensions=asc,csv,tab,txt;"
cn.Open strCon
rs.Open strSQL, cn
'Fill data into sheet
With xlSheet
For f = 0 To rs.Fields.Count - 1
.Range("A1").Offset(0, f).Formula = rs.Fields(f).Name
Next
i = 0
Do While Not rs.EOF
For j = 0 To rs.Fields.Count - 1
.Range("A1").Offset(i, j).Formula = rs.Fields(j)
Next
i = i + 1
rs.MoveNext
Loop
'Number format
.Range("B:B").NumberFormat = "0.0"
End With
' Create the chart
xlapp.Charts.Add
With xlapp.ActiveChart
.ChartType = 65
.HasTitle = True
.SetSourceData xlSheet.Cells(2, 2).CurrentRegion
.PlotBy = xlColumns ' xlColumns
.Location 1
.HasDataTable = False
.HasLegend = False
With .ChartTitle
.Characters.Text = "Server Availability " & Now
.Font.Size = 12
.Font.Name = "Trebuchet MS"
End With
With .Axes(xlCategory)
.HasTitle = True
With .AxisTitle
.Font.Name = "Trebuchet MS"
.Font.Size = 10
.Characters.Text = "Server"
End With
End With
End With
' Save the spreadsheet and close the workbook.
xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.SaveAs sExcelPath
' Give the user control of Excel
xlApp.UserControl = True
' Clean Up
Set xlApp = Nothing
WScript.echo " System Uptime Scan Complete."