Open Excel and get all of the sheet names for Grapher via automation

To open Excel and get all of the sheet names, then plot a line plot from each sheet:

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 August 2019