Replace workbooks in a Grapher project but keep same worksheets

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:

1. Click here to download the BAS file: ReplaceDataKeepSheets.BAS. 
2. Open Grapher and turn on the Script Manager by clicking View | Display | Script Manager.
3. Click the Open () icon in the Script Manager, select the downloaded BAS file, and click Open.
4. 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.
5. 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