Grid all sheets in an Excel workbook at once in Surfer automation

Updated November 20, 2024 00:14

This script automates the process of gridding data from all worksheets in a selected Excel file. It reads data from each sheet, generates corresponding grid files, and saves them in the same directory as the Excel file, naming the grid files based on their respective sheet names. This script is particularly useful for users who need to process and grid multiple datasets efficiently without manually setting up each grid operation.

To run this script:

In a Windows Explorer window, navigate to C:\Program Files\Golden Software\Surfer.
Double click on Scripter.exe to launch Scripter.
Copy and paste the script below into Scripter's code window (be sure to delete any existing lines first), or simply download the attached BAS file and open it in Scripter.
Add or change any gridding parameters within GridData6 as needed. If you skip this step, the script will still run using default parameter values.
To test this script with sample data, click here to download a sample dataset.
Click Script | Run to run the script.
You will then encounter a dialog in which you must select your Excel file. Once you've selected the file, click Open.
The generated grid files will be saved in the same directory as the Excel file.

' GridAllExcelSheets.bas grids all of the worksheets in a selected Excel file
' Grid files are saved in the same directory as the Excel file and named according to the sheet name.
' See bottom of script for common errors

Function SurferApplication()
    ' This function returns the current instance of Surfer if one is already running; 
    ' otherwise, it creates a new instance.

   On Error Resume Next
   Set SurferApplication = GetObject( ,"Surfer.Application")
   If Err.Number <> 0 Then
      Set SurferApplication = CreateObject("Surfer.Application")
   End If
   On Error GoTo 0
End Function
Sub Main
    
    ' Clear any output in the Imediate window
    Debug.Clear

    ' Define the Surfer application object and create a new plot
    Dim SurferApp As Object
    Set SurferApp = SurferApplication()
    SurferApp.Visible = True

    ' Prompt the user to select the input data file
    inFile = GetFilePath("", "xls;xlsx", "", "Choose input data file", 0)
    Debug.Print inFile

    ' If no input file was selected, exit the script
    If inFile = "" Then Exit Sub

    ' Extract the file's directory from the file path
    FileDir = Left(inFile,InStrRev(inFile,"\"))
    Debug.Print "File directory= "; FileDir

    ' Open Excel and the Excel file
    Dim Excel As Object
    Set Excel = CreateObject("Excel.Application")
    Excel.Visible = True
    Set xlswks = Excel.Workbooks.Open (inFile)

    ' Get the sheet names in an array
    Dim SheetNames() As String
    Dim numSheets As Long, Counter As Long, sheet As Object
    numSheets = xlswks.Sheets.Count
    ReDim SheetNames(1 To numSheets)
    Counter = 1
    For Each sheet In xlswks.Sheets
        SheetNames(Counter) = sheet.Name
        Debug.Print SheetNames(Counter)
        Counter = Counter + 1
    Next sheet

    ' Close Excel without saving
    xlswks.Close (savechanges:=False)
    Excel.Quit

    ' Set up an error handling process
    On Error GoTo FileError

    ' For each excel sheet...
    For i = 1 To numSheets
    
        ' Define output grid file directory & name
        GridFile = Left(inFile, Len(inFile)-(Len(inFile)-InStrRev(inFile,".")+1) ) + "_" + SheetNames(i) + ".grd"
        Debug.Print "Output Grid= "; GridFile

        ' Get the sheet name
        sheetName$ = "sheet = " + SheetNames(i)

        ' Grid the data file with the current Surfer defaults (but do not fill the screen with grid reports)
        SurferApp.GridData6(DataFile:= inFile, DataFileOptions:= sheetName$, ShowReport:=False, OutGrid:=GridFile)

    Next i

    ' Close the Surfer application
    SurferApp.Quit
    Exit Sub

    'Print a meaningful error message for each file that did not grid correctly
    FileError:
    Debug.Print  "Error:    " + sheetName + " - " + Err.Description
    Resume Next
End Sub

' Helpful error hints:
'a) "xMin must be < xMax" usually occurs when there is no griddable data.  Check if there really is data, if it is in columns A, B, and C, and that it is formatted as numeric data (not as text or general).
'b) "Insufficient data in worksheet" means there are less than three griddable Z values in the worksheet.
'd) "Inadequate data (all data lie in a horizontal plane)" means that all Z values are the same.
'e) "Unknown worksheet import format" the file format was incompatible with Surfer.  Common if the file was an Excel 2007 spreadsheet and your Surfer version is 8 or lower.

Related articles