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. Click here to download the BAS file: Create_plots_all_sheets.BAS
  2. Click Automation | Scripts | Run, select the BAS file from your downloads directory, and click Open.

OR:

  1. Copy the script below.
  2. Open Grapher and turn on the Script Manager by clicking View | Display | Script Manager.
  3. Press Ctrl+A to select all of the existing lines in the Script Manager and then press DELETE.
  4. Press Ctrl+V to paste it into the Script Manager.
  5. Click the Start/Resume icon () in the Script Manager.

 

*********

'#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 October 11, 2018

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.