Specify the Excel sheet name to load via Surfer automation

Updated November 01, 2021 16:47

To specify a worksheet from within an Excel workbook (XLS or XLSX file), use the DataFileOptions parameter of the current method when loading the data file. Methods that contain this parameter include GridData6, CrossValidate2, AddPostMap2, AddClassedPostMap2, AddVariogram2, SetInputData2, GridResiduals2, and Transform3.

For example, to grid a sheet in an excel workbook, instead of using GridData, use GridData6 and specify the DataFileOptions with the sheet option. A full script showing how to do this is provided below.

To run this script:

Copy the script below.
In a Windows Explorer window, navigate to C:\Program Files\Golden Software\Surfer\Scripter.
Double click on Scripter.exe to launch Scripter.
Press Ctrl+A to select all of the existing lines then press Delete.
Press Ctrl+V to paste the script into Scripter.
Click Script | Run to run the script.

*********

Sub Main
    Dim SurferApp, Plot As Object
    Set SurferApp = CreateObject("Surfer.Application")
    SurferApp.Visible = True
    Set Plot = SurferApp.Documents.Add
    DataFile = "c:\temp\book1.xlsx"
    Outgrid = "c:\temp\Demogrid.grd"
    CVResults = "c:\temp\CrossValidate.dat"

    'Grids the data in a specific sheet in an Excel workbook and creates a grid file
    SurferApp.GridData3(DataFile:=DataFile,DataFileOptions:="sheet=sheet2", OutGrid:=OutGrid)

    'Creates a cross validation report from the data in a specific sheet in an Excel workbook and saves it to a file
    SurferApp.CrossValidate2(DataFile:=DataFile, DataFileOptions:="sheet=sheet2", ResultsFile:=CVResults)

    'Creates a post map from the sheet in the Excel workbook
    Set Map = Plot.Shapes.AddPostMap2(DataFileName:=DataFile, DataFileOptions:="sheet=sheet2")
End Sub

Updated September 27, 2017

Related articles