Grid all sheets in an Excel workbook at once in Surfer automation
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:
- Copy the script below, or download the attached BAS file: GridAll_loop sheets.bas.
- In a Windows Explorer window, navigate to C:\Program Files\Golden Software\Surfer.
- Double click on Scripter.exe to launch Scripter.
- Press Ctrl+A to select all of the existing lines then press Delete.
- 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.
- 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.
Comments
0 comments
Please sign in to leave a comment.