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