Importing coordinate data into TurboCAD Pro
Although TurboCAD can directly import many types of CAD drawing formats, there is no built-in way to import coordinate data in spreadsheet or tabular form. Fortunately it's not too difficult to create VBA code that can do the job. The specifics of how to proceed will vary depending on what format the data has, and on how you want to represent the data in a drawing.
In this article I will describe a general purpose macro that will read data that contains the X and Y coordinate values for vertices. The data will be in CSV (Comma Separated Value) format, and will be plotted in the drawing using a Bezier curve. CSV is a standard file format used to exchange data between many applications. In it's simplest form, a CSV data file is an ASCII text file in which each line (row) of text is a record. Within each record, the data values are separated by commas. Spreadsheet and Database applications can export tables of data to CSV files. You could also create a CSV file using Notepad by manually typing in the records, and changing the file extension to "CSV". (For that matter, the extension can be anything; "TXT", "DAT", etc. As long as the data inside is properly formatted.)
For this example, our CSV file will contain just two values per record; the X and Y coordinates of a vertex. I created a sample file named "SineCurve.csv" which contains 51 records of vertices that define one cycle of a sine function. The data values for this file were generated in Excel and exported in CSV format. Click here to see the data values in CSV format..
Planning the project
As with any VBA project, I'll start out with a plan before I even start up the VB editor. I already decided I am going to be importing my coordinate data in CSV format. I chose this because it's a very common way to exchange this type of data. It is also possible to import data in other formats, such as Excel workbooks, or Access database tables, but it's more involved. Beside, those applications can export to CSV, so this example can still be used.
I want to include a file browsing dialog to facilitate the task of locating the CSV data file. I could have just made a form with a text box and had the user manually type in the path to the file. Providing a standard Windows file open dialog is a little more work, but makes the macro easier to use. I'm going to use an API call to bring up the Windows system common dialog, so that I don't need to include any special library references in my project. There are other ways to incorporate common dialog boxes in VBA macros, and I've shown some examples in the macro ComDlgDemo.tcm.
Once the CSV data file has been located (and the path to the file saved), I will need to open the file to read the data. I could just plot the data as I'm reading it. But I prefer create an array variable to hold the data, read the data from the file to the array, and then close the file. The array will be one dimensional, and each element will be a string containing one record (line of text) from the CSV file.
In order to plot vertices according to the data contained in the CSV records, I have to extract the separate X and Y values from each record. I will use a simple parsing loop to accomplish this, and plot a new vertex each time a new pair of X and Y coordinates is extracted. This example is using 2D coordinate data, but it should be obvious that it is a simple matter to use the same methods for 3d data by including a Z coordinate value.
For this example I chose to plot the coordinate data with a Bezier curve. Depending upon your need, it would be just as easy to use a different graphic type such as a polyline. Or you might want to mark the position of each data point with a circle, sphere, or other object.
Finally, I will include a little bit of error handling, because mistakes happen and sometimes the selected data file might be the wrong format, or contain errors. It's nicer to have the macro tell you there is a problem, and to exit gracefully, than it is to have the thing stop and throw you into debug mode.
To summarize, the plan for my macro looks like this:
Step1: Browse and get path to CSV data file, using common dialog API GetFileOpenName function.
Step 2: Open CSV data file and read data into array variable
Step 3: Parse X and Y coordinate values from data records and use them to draw graphic in drawing.
Coding the project
Now the fun part!
Step 1: Browse and get path to CSV data file, using common dialog API GetFileOpenName function.
I'll admit right now that I do not have in-depth knowledge of the Windows API. When I want to use an API function, I usually find working examples on the internet, and adapt the code to my purpose. Such is the case here, in using the GetFileOpenName function. Since the focus of this article is on how to import CSV data, not on how to use Windows common dialogs, I'm going to skip right through this part. I have imported a code module that I keep around just for this purpose, called modGetOpenFileName.bas into the project. In this module I have defined a function (GetOpenFname) that takes care of opening the file open dialog box. I edited the code in modGetOpenFileName.bas to make "CSV" the default file extension, with selections of "TXT" and "*.*" (All files) in the drop down list. All I have to do is call the function from my main procedure, passing it the name of the string variable I want to use to store the path the the data file. This is the call from the main procedure:
Dim sFname As String 'path to file containing coordinate data
If
GetOpenFname(sFname) Then
MsgBox "File to be
opened:" & vbCrLf & sFname
Else
MsgBox "Dialog was
cancelled"
Exit Sub
End If
The function "GetOpenFname" contained in modGetOpenFileName is passed a reference to sFname. The function returns a True or False value, depending upon if the user selected a valid file name. If true, sFname is assigned the string for the path to the file (assignment made within the function). If false, the procedure is ended.
Step 2: Open CSV data file and read data into array variable
Now that we have the path to a data file, it is a simple matter to read the data into the procedure. A string array is created to hold the data. Each line of text in the file (in this case, it will be a string containing the X and Y coordinates for the vertices, separated by a comma) will be saved as one element of the array:
Dim sCoords() As String 'array
to store coordinate data strings
Dim n
As Integer 'counter used when resizing
sCoords array
On Error GoTo Errhandler01
Open sFname
For Input As #1
n = 1
Do While Not EOF(1)
ReDim
Preserve sCoords(n)
Line
Input #1, sCoords(n)
n = n + 1
Loop
Close #1
This code opens the data file that was selected in Step 1, and reads it line by line, until the end of the file (EOF) is reached . Each time a new line is read, the string array variable sCoords is resized and the new line saved. When all lines of the file have been read, the file is closed.
Notice that an error handler statement has been added. This is in case there is a problem reading the file that was selected in Step 1.
Step 3: Parse X and Y coordinate values from data records and use them to draw graphic in drawing.
Before we can plot a graphic in the drawing, we must separate the values for the X and Y coordinates out of the strings saved in the array sCoords(). This is done using the Mid string function to locate the position of the comma in each string. The Mid function is used again to extract the X and Y values from each side of the comma. For this example, I am using the data to draw a Bezier curve, and adding each new vertex right after each pair of X and Y values is determined.
Dim
cFirst As Long 'variable to store
position of comma in strings
Dim x As Double 'the X coordinate for the vertex to be added
Dim y As Double 'the Y coordinate for the vertex to be added
Dim i As Integer 'index used while parsing
Dim j As Integer 'index used while parsing
Dim Gr As Graphic
Dim Dr As Drawing
Dim Grs As Graphics
On Error GoTo Errhandler02
Set Dr = Application.ActiveDrawing
Set Grs = Dr.Graphics
cFirst = 0
x = 0
y = 0
'following lines read strings in sCoords array
'and extract X and Y values
For i = 1 To UBound(sCoords)
For j = 1 To Len(sCoords(i))
If Mid(sCoords(i), j, 1) = "," Then
cFirst = j
Exit For
Else
End If
Next j
x = Val(Mid(sCoords(i), 1, cFirst - 1))
y = Val(Mid(sCoords(i), cFirst + 1, Len(sCoords(i)) - cFirst + 1))
If i = 1 Then 'first vertex, need to create new graphic
Set Gr = Grs.AddCurveBezier(x, y, 0)
Else 'graphic already exists, need to add more vertices
Gr.Vertices.add x, y, 0
End If
Next i
Dr.ActiveView.Refresh
Set Gr = Nothing
Set Grs = Nothing
Set Dr = Nothing
Exit Sub
Note that a second error handling statement was added. This will redirect the procedure if there are problems such as incorrect formatting of the CSV data, or errors in the data.
This example shows how to read data for a 2D graphic object. If you had CSV data containing X, Y and Z coordinate values, you would follow this same general method, except that when parsing the data strings, you would have to add some lines of code to locate the second comma in each string, and then extract the Z value as well.
There were a couple error handling statements in the code sections above. This is not mandatory, but if not used the user will be dumped to the VBE if there are any problems opening or reading the data file, or in using the data to draw the graphic. Notice that in Errhandler02 there is a clean-up of object references that might have been made before the error occurred. This is done because if this error handler is called, the procedure will exit before the clean-up statements in the main part of the procedure.
Errhandler01:
MsgBox "There was a problem reading the data file."
Exit Sub
Errhandler02:
MsgBox "There was a problem drawing the graphic with the supplied data." & vbCrLf & _
"Check that the data is in the required CSV format."
Set Gr = Nothing
Set Grs = Nothing
Set Dr = Nothing
Exit Sub
The complete macro for this project can be downloaded here: ImportCsv.zip. This zip archive also contains the sample CSV data file "SineCurve.csv".