If you have a single Excel file containing multiple worksheets with X, Y, Z data, and you want to create grids from each set of data, the script below can be used to do this.

To run this script:

1. Copy the script below, or download the attached BAS file: GridAll_loop sheets.bas.
2. In a Windows Explorer window, navigate to C:\Program Files\Golden Software\Surfer.
3. Double click on Scripter.exe to launch Scripter.
4. Press Ctrl+A to select all of the existing lines then press Delete.
5. If you copied this script, press Ctrl+V to paste it into Scripter. If you downloaded it, click File | Open, select the BAS file from your downloads directory, and click Open.
6. Click Script | Run to run the script.

' GridAll_loop sheets.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()
   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
    Debug.Clear

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

' Get the input data file - point input box to the directory with the data file
    inFile = GetFilePath("","xls;xlsx","","Choose input data file",0)
' Need to handle cancel from file dialog
    If inFile = "" Then Exit Sub
    Debug.Print inFile
    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 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
        Debug.Print SheetNames(Counter)
        Counter = Counter + 1
    Next sht

'Close Excel - do not save
    xlswks.Close (savechanges:=False)
    Excel.Quit
On Error GoTo FileError
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
        Sheet$ = "sheet = " + SheetNames(i)

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

        ' You can uncomment line below to make explicit changes to Gridding options.  Make
        ' sure to comment out the line above or files will be gridded twice.
        '
        ' For more inforomation about using these options and thier definitions please see
        ' the Surfer Help File, available by using the menu command HELP | ABOUT and then
        ' searching for "GridData4".
        '
        'SurferApp.GridData4(DataFile:= inFile, DataFileOptions:=Sheet$,  xCol:=1, yCol:=2, zCol:=3, Algorithm:=srfKriging, ShowReport:=False, OutGrid:=GridFile, OutFmt:=srfGridFmtS7)

    Next i
    SurferApp.Quit
    Exit Sub

    'Print a meaningful error message for each file that did not grid correctly
    FileError:
    Debug.Print  "Error:    " + data_file + "                       " + Err.Description
    Resume Next
End Sub
' Helpful error hints:
'
'a) If the script appears to do nothing, make sure file_directory is valid.
'b) "xMin must be < xMax" usually means there is no griddable data.  Check if there really is data, if it is in columns ABC, and that it is formatted as number and not as text or general.
'c) "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.