Creating Excel Chart Using VBS - Setting Axes
Darren (UK)
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."
ThatsIT.net.au
.MinimumScale = 1
.MaximumScale = 15
End With
"Darren (UK)" <Darr...@discussions.microsoft.com> wrote in message
news:D2395AA4-AF7A-49A3...@microsoft.com...
Darren (UK)
Thanks for replying, i had previously tried that and also
With .Axes(xlValue)
.MinimumScale = 1
.MaximumScale = 1
End With
on each occasion it returns the error:
(null): Unspecified error
Im not sure if it is the execution order if so at what point in the script
would i need to add this in.
Secondly do i need to add xlValue as a constant. Again if so what value does
this need to be?
Darren
Darren (UK)
It was the:
xlConstant = 2 that was missing. Added and now working Fine.
Thanks for your assistance.
Dave Patrick
Const xlValue = 2
--
Regards,
Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
ThatsIT.net.au
> Yes....
>
> It was the:
>
> xlConstant = 2 that was missing. Added and now working Fine.
>
> Thanks for your assistance.
sorry should of noticved that.
but there is a ewasy way of programming office apps.
record a macro and look at the code. then just add you object to it
ActiveChart.Axes(xlValue)
becomes
xlObj.ActiveChart.Axes(xlValue)