Follow

How do I open Excel and get all of the sheet names for Grapher via Scripter?

This article contains Grapher sample script that shows how to open Excel and get all of the sheet names, then plot a line plot from each sheet.

 

To run this script:

  1. Copy the script below, or click here to download the BAS file: Create_plots_all_sheets.BAS.
  2. In a Windows Explorer window, navigate to C:\Program Files\Golden Software\Grapher 12\Scripter.
  3. Double click on Scripter.exe to launch Scripter.
  4. Press Ctrl+A to select all of the existing lines then press Delete.
  5. If you copied this script, press Ctrl+V to paste it into Scripter. If you downloaded it, click File | Open, select the BAS file from your downloads directory, and click Open.
  6. Click Script | Run to run the script.
     

*********

'#Reference {00020813-0000-0000-C000-000000000046}#1.7#0#C:\Program Files\Microsoft Office\Office14\EXCEL.EXE#Microsoft Excel 14.0 Object Library#Excel
Sub Main

'Set the path and XLS/XLSX/XLSM file name for the Excel file
' Change this to your directory and file name
Path$ = "C:\testing\"
WorksheetName = "TestWorkbook.xls"

'Open Excel and the Excel file
Dim Excel As Object
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Set xlswks = Excel.Workbooks.Open (Path$+WorksheetName)

'Get the sheet names in a variable
Dim SheetNames()
Dim Counter, numSheets, sht
Counter = 1
numSheets = xlswks.Sheets.Count
ReDim SheetNames(1 To numSheets)
For Each sht In xlswks.Sheets
	SheetNames(Counter) = sht.Name
    Counter = Counter + 1
Next sht

'Open Grapher and create the graphs
Dim Grapher As Object
Set Grapher = CreateObject("Grapher.Application")
Grapher.Visible = True

'Create a new document window
Set Plot1 = Grapher.Documents.Add(grfPlotDoc)

'Create 2D graph and 1st line/scatter plot
Set Graph1 = Plot1.Shapes.AddLinePlotGraph(Path$ + WorksheetName +"!" + SheetNames(1),1,2,3)

'Add each additional plot to the existing graph
For i = 2 To numSheets
	Graph1.AddLinePlot(Path$ + WorksheetName +"!" + SheetNames(i),1,2,3)
Next

'Close Excel - do not save
xlswks.Close (savechanges:=False)
Excel.Quit

End Sub

 

Updated September 22, 2016

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.