Engineering Work Project

[elowther]

Hi everyone, my name is Evan. I am currently working on a project for work and I believe I am in way over my head and I am need of everyone's expertise in AutoCAD VBA.

What I am trying to do is write a code that allow me to take data from excel and use it to draw a drawing in AutoCAD.

I am breaking down this code into lots of smaller components. The first thing I am trying to do is is just have my code draw a line between points but currently I cant even get points to show up in autocad. My experience level is literally almost zero so any help would be more then appreciated.

Thanks Everyone,
Evan

[Tommy]

Hi elowther Welcome to VBAX!

There are several samples on this website that will give you different ways to code for this. I myself have posted several things that do what you are asking. There is one that I wrote a script to plot a set of points for a surveyor.
There is one that we had a set of locations and we plotted a course for a helicopter to fly from one point (oil well platforms) to another that was printed on a form.
There was another one the we had information in excel to draw a boat hull in 3d in acad.

I can look and post the links if you would like, they are pretty good samples to get started. Also look in the help files they will have a lot of working samples.

The most important part here is what exactly do you want to do.

[elowther]

Alright, we I guess I will start with a better discription of my project.

I am trying to write some code that will allow the user to input a Tool Number. Then take that tool number and insert it into excel, where the excel document will take that toolnumber and reference a different sheet in excel where it will take all the data it needs about that tool in order to provide me with specs of the tool.

I then want autocad to make a drawing of that tool from the specs provided by excel. (This is the hard part)

I originally did all the non autocad code in Microsoft Access but when I tried to transfer the code from access to acad it didnt work. The biggest issue is I have never done anycode that deals with acad and I spent my entire day yesterday searching the internet for help on this and I could not find any good references, examples or help in allowing me to even draw anything in cad from VBA.

Today I am hoping just to be able to plot points in acad from my VBA code and then draw lines connecting the points.

Also, Tommy and examples or references you could link me would be extremely useful.

Thanks,
Evan

[Tommy]

Ok so we are drawing a tool.

Can you post the workbook? Just as a side note if you have already done all of this in access we can port it to that after we are through with the testing and debugging.

What I think you are wanting to do is in Excel you have data and you want to produce drawings in acad.

Here are some local links
http://www.vbaexpress.com/forum/showthread.php?t=3514
http://www.vbaexpress.com/forum/showthread.php?t=25479
http://www.vbaexpress.com/forum/showthread.php?t=22343

[elowther]

Unfornately there is no workbook. However, I did make some progress in drawing the tool through VBA yesterday however today I have a new problem. I would like to have the pts put in from an excel document.

here is an example of how I am ploting points in autocad from VBA

pt1 = ThisDrawing.Utility.GetPoint (,"Select lower left hand corner.")
pt2 = ThisDrawing.Utility.PolarPoint (pt1, 0, 1.0 )

ThisDrawing.ModelSpace.AddLine pt1, pt2

'So how do I modify the code above in order to have it insert points according to what a excel documents reads?

Thanks
Evan L.

[Tommy]

It depends on where you are, are you in excel and sending the information to acad or are you in acad reading the data from excel?

The links I posted show you either way.

[elowther]

It depends on where you are, are you in excel and sending the information to acad or are you in acad reading the data from excel?

The links I posted show you either way.

I am in acad trying to read data from excel.

[elowther]

One other question, here is my current code but I have issues because it wont let me put in the last point. Its telling type mismatch error. Error line is written in Red

Private Sub CommandButton1_Click()
UserForm1.Hide
Dim pt1, pt2, pt3, pt4, pt5, pt6, pt7, pt8, pt9, pt10 As String
Dim pi As String
Dim dist_1_2, dist_9_10 As String
Dim relief_length As String
Dim relief_height As String
Dim shank_angle_rad As String
Dim blend_angle_rad As String
Dim relief_angle_rad As String
Dim relief_distance As String
Dim shank_angle_distance As String
Dim final_angle As String

pi = 3.14159265359
shank_angle_rad = Val(TextBox5.Text) * (pi / 180)
blend_angle_rad = Val(TextBox6.Text) * (pi / 180)
relief_angle_rad = blend_angle_rad / 2
relief_height = (Val(TextBox3.Text) - Val(TextBox2.Text)) / 2
relief_length = relief_height / Tan(relief_angle_rad)
relief_distance = relief_height / Sin(relief_angle_rad)
shank_angle_distance = Val(TextBox4.Text) / Cos(shank_angle_rad)
dist_1_2 = Val(TextBox1.Text) - Val(TextBox7.Text) - relief_length - Val(TextBox4.Text)
dist_9_10 = Val(TextBox3.Text) - 2 * (Val(TextBox4.Text) * Tan(shank_angle_rad))
final_angle = pi + shank_angle_rad
'Ploting the points of the tool'
pt1 = ThisDrawing.Utility.GetPoint(, "Pick Left Hand Corner")
pt2 = ThisDrawing.Utility.PolarPoint(pt1, 0, dist_1_2)
pt3 = ThisDrawing.Utility.PolarPoint(pt2, relief_angle_rad, relief_distance)
pt4 = ThisDrawing.Utility.PolarPoint(pt3, 0, Val(TextBox7.Text))
pt5 = ThisDrawing.Utility.PolarPoint(pt4, pi / 2, Val(TextBox2.Text))
pt6 = ThisDrawing.Utility.PolarPoint(pt5, pi, Val(TextBox7.Text))
pt7 = ThisDrawing.Utility.PolarPoint(pt6, (pi - relief_angle_rad), relief_distance)
pt8 = ThisDrawing.Utility.PolarPoint(pt7, pi, dist_1_2)
pt9 = ThisDrawing.Utility.PolarPoint(pt1, pi - shank_angle_rad, shank_angle_distance)
pt10 = ThisDrawing.Utility.PolarPoint(pt9, pi / 2, dist_9_10)

'Connecting the lines to draw the actual tool'
ThisDrawing.ModelSpace.AddLine pt1, pt2
ThisDrawing.ModelSpace.AddLine pt2, pt3
ThisDrawing.ModelSpace.AddLine pt3, pt4
ThisDrawing.ModelSpace.AddLine pt4, pt5
ThisDrawing.ModelSpace.AddLine pt5, pt6
ThisDrawing.ModelSpace.AddLine pt6, pt7
ThisDrawing.ModelSpace.AddLine pt7, pt8
ThisDrawing.ModelSpace.AddLine pt1, pt9
ThisDrawing.ModelSpace.AddLine pt9, pt10

[Tommy]

You have pt10 defined as a string, as a matter of fact a lot of your variables are strings, they need to be doubles or variants.

[elowther]

What should be labed what? And exactly what does it mean to be considered a String, Variant, or Double?

[Tommy]

A string is a variable that will contain numbers and text. A double will contain a number only with a decimal, a long or integer will contain whole numbers, a variant can contain both and will bite you in the but the first chance it gets.

When you set up the tables in access didn't you have to put a data type in to define the columns/fields of data? The same goes for this and all programming.

I will post later some code to connect to excel and read some cells.

[elowther]

The only code I got to work in access was something that opened an excel document and took excell cells and read those values. So not much at all. The more I work on this project the more I feel that instead of reading individual values, it might be more efficent to input entire tables from Excel into a Acad Drawing. Also one other quick thing

The portion of code I am working on now is first opening a Cad Drawing template (working) and then opening my excel spreadsheet which has all the values (not working).

Can anyone give me a quick fix for the excel portion?


Option Explicit

Sub OpenTemplateDrawing()
Dim AcadApp As AcadApplication
Dim MyDwg As AcadDocument
Dim DWG As String

Set AcadApp = GetObject(, "AutoCAD.Application")
Set MyDwg = AcadApp.Documents.Open("C:\Documents and Settings\elowther\Desktop\Template 2000")

End Sub

Sub OpenExcelSpreedsheat()
Dim ExcelApp As Object
Dim MyDwg As AcadDocument

Set ExcelApp = GetObject(, "Excel.Application")
Set MyDwg = ExcelApp.Documents.Open("C:\Documents and Settings\elowther\Book1")

End Sub

[Tommy]

Since you are starting in acad there is no need to create the object, you already are there.
[VBA]
Option Explicit

Sub OpenTemplateDrawing()
Application.Documents.Open ("C:\Documents and Settings\elowther\Desktop\Template 2000")
End Sub

Sub OpenExcelSpreedsheat()
Dim ExcelApp As Object
Dim MyDwg As Workbook
On Error GoTo ExitSub
Set ExcelApp = CreateObject("Excel.Application")
If ExcelApp Is Nothing Then
MsgBox "Excel doesn't like us!" '<- means excel didn't open
Else
Set MyDwg = ExcelApp.Documents.Open("C:\Documents and Settings\elowther\Book1")
End If
On Error GoTo 0
Exit Sub
ExitSub:
Err.Clear
MsgBox "Book1 Does not exist!"
On Error GoTo 0
ExcelApp.Quit
End Sub

[/VBA]

EDIT: I NEED TO LEARN TO TEST BETTER BEFORE POSTING

[elowther]

Thanks Tommy,

but my code is giving me an error saying "User Defined Type Not Defind" for this line

Dim MyDwg As Workbook

[Tommy]

Did you add the reference to Excel?

[elowther]

Apparently not. How do I do that.

[Tommy]

If I was coding this exercise I would do it this way:
[VBA]Option Explicit
Sub Main()
Dim ExcelApp As Excel.Application
OpenTemplateDrawing
Set ExcelApp = GetExcel
OpenWorkBook ExcelApp, "C:\Documents and Settings\elowther\Book1.xls"

End Sub
Sub OpenTemplateDrawing()
Application.Documents.Open ("C:\Documents and Settings\elowther\Desktop\Template 2000")
End Sub

Function GetExcel() As Excel.Application
Set GetExcel = CreateObject("Excel.Application")
If GetExcel Is Nothing Then
MsgBox "Excel doesn't like us!" '<- means excel didn't open
End If
GetExcel.Visible = True
End Function
Sub OpenWorkBook(Xl As Excel.Application, WB As String) '<-have the full path with the name of the workbook
Xl.Workbooks.Open WB
End Sub[/VBA]

[Tommy]

You go to tools, in the drop down menu select References, in the dialog box that opens scroll down until you see Microsoft Excel xx.xx object library and select that. The xx.xx is a version number.

[elowther]

Thank you Tommy!

After referencing the Excel, things started to work ALOT better! Also, you did mention yesterday about uploading some examples of inserting excel cells and excel tables into a drawing of AutoCAD. I was wondering if you would be able to get to that sometime today. That would be a major help for me and my project!

[Tommy]

I believe that you were the only one talking about inserting tables into acad. I have already posted links to 3 different threads here on this site for you to look through. This should give you a lot of help in getting started. I will be more than happy to assist you further as soon as there is something to work with. ex a worksheet that defines the tool you want to draw.