To open Excel and get all of the sheet names, then plot a line plot from each sheet:
- Click here to download the BAS file: Create_plots_all_sheets.BAS
- Click Automation | Scripts | Run, select the BAS file from your downloads directory, and click Open.
OR:
- Copy the script below.
- Open Grapher and turn on the Script Manager by clicking View | Display | Script Manager.
- Press Ctrl+A to select all of the existing lines in the Script Manager and then press DELETE.
- Press Ctrl+V to paste it into the Script Manager.
- 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
Comments
Please sign in to leave a comment.