Cannot figure out Excel Chart in VBScript 2

[LXGoldie]

I have a simple file of dates and numbers:

06/01/07 3.4444
06/02/07 2.9546
06/03/07 1.9834

And I would very much like to make a simple graph of amount over date. This has proven to be more of a challenge than I had imagined; the only clear reference I can find to doing it is a .pdf document about embedding a vbscript inside a SAS program.

Can someone point me to some documentation about how to do this? I can't seem to find it on my own.

Thanks,

LXGoldie

 

[markdmac]

' Launch Excel
          dim app
          set app = createobject("Excel.Application")
    
          ' Make it visible
          app.Visible = true
    
          ' Add a new workbook
          dim wb
          set wb = app.workbooks.add
    
          ' Fill array of values first...
          dim arr(19,9) ' Note: VBScript is zero-based
          for i = 1 to 20
             for j = 1 to 10
                arr(i-1,j-1) = i*j
             next
          next
    
          ' Declare a range object to hold our data
          dim rng
          set rng = wb.Activesheet.Range("A1").Resize(20,10)
    
          ' Now assign them all in one shot...
          rng.value = arr
    
          ' Add a new chart based on the data
          wb.Charts.Add
          wb.ActiveChart.ChartType = 70 'xl3dPieExploded
          wb.ActiveChart.SetSourceData rng, 2 ' xlColumns
          wb.ActiveChart.Location 2, "Sheet1" 'xlLocationAsObject
    
          ' Rotate it around...
          for i = 1 to 360 step 30
             wb.activechart.rotation = i
          next

          ' Give the user control of Excel
          app.UserControl = true

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[Remou]

It is possible get data from a text file.

' 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")

strFolder = "C:\Docs\"
strSQL = "Select * From imp.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("a2").Offset(i, j).Formula = rs.Fields(j)
      Next
      i = i + 1
      rs.MoveNext
   Loop
   'Date format
   .Range("a:a").NumberFormat = "mm/dd/yy"

End With

' Create the chart
Set xlChart = xlApp.Charts.Add
With xlChart
      .SetSourceData xlSheet.Cells(1, 1).CurrentRegion
      .PlotBy = 2 ' xlColumns
      .HasTitle = True
      .HasLegend = False
      With .ChartTitle
            .Characters.Text = "Dates"
            .Font.Size = 12
      End With
End With