' 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.