If you have a Grapher project that uses specific worksheets in specific workbooks, and you want to change those workbooks while keeping the worksheet names the same, you can do so with this script.
To run this script:
- Click here to download the BAS file: ReplaceDataKeepSheets.BAS.
- Open Grapher and turn on the Script Manager by clicking View | Display | Script Manager.
- Click the Open () icon in the Script Manager, select the downloaded BAS file, and click Open.
- In the User-defined variables section in the script, update the path$, projfile$, and newwkbk$ to your file path, your Grapher GRF file name, and your new workbook name.
- Click the Start/Resume () icon in the Script Manager.
Sub Main 'Create Grapher as an Object Dim Grapher As Object Set Grapher = CreateObject("Grapher.Application") Grapher.Visible = True '======================================== 'User-defined variables '======================================== path$ = "C:\temp\" projfile$ = "OriginalProject.grf" newwkbk$ = "NewWorkbook.xlsx" '======================================== 'Open an existing document Set PlotDoc = Grapher.Documents.Open(path$+projfile$) 'Loop through all plots in all graphs For i=1 To PlotDoc.Shapes.Count If PlotDoc.Shapes.Item(i).Type=grfShapeGraph Then Set Graph = PlotDoc.Shapes.Item(i) For j=1 To Graph.Plots.Count Set Plot = Graph.Plots.Item(j) 'Searches through the worksheet for each plot and finds the ! character that denotes the sheet name. char = InStrRev(Plot.Worksheet,"!") 'Save what's after the ! character as the sheet name wksheet$ = Right(Plot.Worksheet,Len(Plot.Worksheet)-char) 'Set New Worksheets for plots Plot.Worksheet = path$ +newwkbk$ +"!"+wksheet$ Next j End If Next i End Sub
Updated October 1, 2019
Please sign in to leave a comment.