' 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 grfApp, 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 grfApp = CreateObject("Grapher.Application") 'grfApp.Visible = True ' Get the file to convert inFile = GetFilePath("","csv","","Open CSV 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 = grfApp.Documents.Open(inFile) ' Save it to a temporary .dat file and quit Surfer wks.SaveAs(tmpFile,,wksFileFormatCsv) grfApp.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 Close tmpFile Kill tmpFile Close #2 End Sub