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.

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



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:
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

[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.

[elowther]

Ok, I will attach the documents I am working. First Attachment is the AutoCAD drawing that I will be trying to insert all the tables from the excel doc into. Second is the actual excel document that I am currently trying to have my Code as for a tool number and insert it into the "UserInput Box". I will also attach my current code. This only error it is having right now is with the line that is suppose to input the inputbox's toolnumber into excel.

ok...how do I attach an excel doc and a autocad drawing?

[Tommy]

When you go to post a comment select go advanced then at the top of the form there is a paper clip select that and it will take you to an area that lets you upload the files. It will not let you upload a drawing so rename it to a .txt extension and i can deal with it from there.

[elowther]

9293

[elowther]

Alright, here is a zip file of both documents 9294

and her is my current code.

Option Explicit
Function InputToolNum()
Dim UserInput As String
UserInput = InputBox("Please Input Tool Number.")

End Function

Sub Main()
'Open the excel spreadsheet
Dim ExcelApp As Excel.Application
OpenTemplateDrawing
Set ExcelApp = GetExcel
OpenWorkBook ExcelApp, "G:\Quattro Pro\EXEL\270XXL.xls"

'Inputs Tool Number into Excel'
Dim UserInput As String
Dim ThisWorkBook As Object
ThisWorkBook.Cells(5, 4).Value = UserInput

End Sub
Sub OpenTemplateDrawing()
'Open the AutoCAD Drawing Template
Application.Documents.Open ("J:\Bbl2000x")

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

[elowther]

Nevermind, I got that cell insertion part to work. Now to display the tables in the drawing!

[Tommy]

Evan here is a acad project file. it will load the drawing and the spread sheet. then it will read the spreadsheet for the tools numbers and the user will have the opportunity to select the tool they desire to draw. I would have started drawing but I don't know what fields to pick up and what to do then.

[elowther]

Evan here is a acad project file. it will load the drawing and the spread sheet. then it will read the spreadsheet for the tools numbers and the user will have the opportunity to select the tool they desire to draw. I would have started drawing but I don't know what fields to pick up and what to do then.

I would like it to read and insert Cell(C30:C69) and Cell(F30:F69). Also, did you do this in AutoCAD 2011 because I am having a hard time opening it on AutoCAD 2013.

[elowther]

Ok, so i was able to get it open. But I am not quite sure how to run it exactly

[elowther]

Actually, I have a more important question for anyone who can answer. I know I can select certain cells in excel and copy them and then paste them into my AutoCAD drawing. But I would like to know how to do that with code in autocad?

[Tommy]

There is no advantage to do that. All you are doing is embedding the spreadsheet into the acad drawing. It is called OLE linking and embedding. I would help more but I have a job to finish detailing.

[elowther]

There is no advantage to do that. All you are doing is embedding the spreadsheet into the acad drawing. It is called OLE linking and embedding. I would help more but I have a job to finish detailing.

Ok, I will look into the OLE linking and embedding. Thanks for all your help Tommy, I would not have gotten anywhere without your advice.