All Forums >> [Scripting] >> WSH & Client Side VBScript >> Creating Excel Chart Using VBS - Setting Axes Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!
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 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
Thanks for replying. When recording a Macro the VBA was very similiar to the code you have posted. I made the required amendments to turn it into VBS. So i had tried this and many variations of it. I did also try to insert the code further up the script (thinking it may have been the order of execution)
Each time i was presented with the error: (88, 6) (null): Unspecified error ** 88 being the line number in this instance.
Ive also tried this variation:
With xlApp.ActiveChart.Axes(xlValue) .MinimumScale = 1 .MaximumScale = 100 End With
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Creating Excel Chart Using VBS - Setting Axes 
Printable Version
Creating Excel Chart Using VBS - Setting Axes - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread