' Matrix2xyz.bas
'
' This script converts a worksheet in matrix format to an
' XYZ dat file, suitable for gridding in Surfer. It assumes
' that the X values are in the first row in contiguous cells,
' beginning in column B, and that Y values are in the first column
' in contiguous cells beginning in row 2. Z values are assumed to
' be in an array of contiguous cells beginning in cell B2.
'
'
Option Explicit
Sub Main
Dim srfApp, wks As Object
Dim inFile, xyzName, xyzDir, xyzFile, tmpFile, opts, row, y As String
Dim x() As String
Dim center(1) As Double
Dim xMin, xMax, yMin, yMax, collar(9) As Double
Dim i,j,xcnt As Integer
' Start Surfer
Set srfApp = CreateObject("Surfer.Application")
'srfApp.Visible = True
' Get the file to convert
inFile = GetFilePath("","xls","","Open Excel worksheet containing array",0)
' Need to handle cancel from file dialog
If inFile = "" Then Exit Sub
' Generate name for the temp .dat file
tmpFile = Left(inFile,Len(inFile)-4) + "_temp.dat"
' Get the output file name.
' Default to input name + "_xyz"
i = InStrRev(inFile,"\")
j = InStrRev(inFile,".")
xyzName = Mid(inFile,i+1,j-i-1) + "_xyz.dat"
xyzDir = Left(inFile,i)
xyzFile = GetFilePath(xyzName,"dat",xyzDir,"Save XYZ As",3)
' Need to handle cancel from file dialog
If xyzFile = "" Then Exit Sub
' Open the input data file
Set wks = srfApp.Documents.Open(inFile)
' Save it to a temporary .dat file and quit Surfer
wks.SaveAs(tmpFile,,srfSaveFormatCsv)
srfApp.Quit
' Open the temp .dat
Open tmpFile For Input As #1
' Open the output xyz.dat file
Open xyzFile For Output As #2
' Read the first row of X values and store in array
Line Input #1, row
xcnt = UBound(Split(row,","))
ReDim x(xcnt)
For i = 1 To xcnt
x(i) = Split(row,",")(i)
Next i
' Read through the rest of the temp.dat file and
' write x,y,z values to the output file.
Do Until EOF(1)
Line Input #1, row
y = Split(row,",")(0)
For i = 1 To xcnt
Print #2,x(i);",";y;",";Split(row,",")(i)
Next i
Loop
' Close the temp .dat and delete it.
' Close the output file
Close #1
Kill tmpFile
Close #2
End Sub