Excel and AutoCAD® - A match made in heaven - Part 3
One of my readers has requested some help regarding the drawing of windows. I'm not 100% sure of his exact requirements, but it's a good opportunity for me to build on what I've already shown you. This will be a brief post - the main content of which is some annotated VB code (below). This simply takes the previous Excel and AutoCAD® post a little further by creating a practical implementation.
What this does is allows the user to enter an X,Y,Width and Height coordinate in Excel, in columns A,B,C and D respectively. Running the code draws them as rectangles in modelspace.
I've decided to compile this as an Excel file, which is downloadable below. It is worth mentioning that I have referenced a specific version of AutoCAD® in this file (Under Tools>References from the VBA editor accessible from Excel using Alt+F11). The code should work if you reference your own version of AutoCAD®.
The source code is here also for you to look at, or paste into your own project. Again, you'll need to create a reference to AutoCAD® as explained in the previous article.
Sub Main() Dim ACAD As AcadApplication 'Create ACAD variable of type AcadApplication On Error Resume Next 'This tells VBA to ignore errors Set ACAD = GetObject(, "AutoCAD®.Application") 'Get a running instance of the class AutoCAD®.Application On Error GoTo 0 'This tells VBA to go back to NOT ignoring errors If ACAD Is Nothing Then 'Check to see if the above worked Set ACAD = New AcadApplication 'Set the ACAD variable to equal a new instance of AutoCAD® ACAD.Visible = True 'Once loaded, set AutoCAD® to be visible End If ACAD.ActiveDocument.Utility.Prompt "Hello from Excel!" 'Print a message to the AutoCAD® command line Dim Coords(7) As Double 'This is an array of double precision floating point numbers Dim n As Integer 'Create the variable n as the type Integer For n = 1 To 10 'Loop this code, incrementing the value of n from 1 to 10 'Variables for X,Y,Width and Height Dim X As Double Dim Y As Double Dim Width As Double Dim Height As Double 'Store the values from Excel in memory - We could just use Sheet1.Cells(), but 'this makes it much more readable for us as programmers, and it is faster to retrieve 'data from variables than from the worksheet. X = Sheet1.Cells(n, 1) Y = Sheet1.Cells(n, 2) Width = Sheet1.Cells(n, 3) Height = Sheet1.Cells(n, 4) 'Lightweight polylines are defined by a series of 2D coords in an array 'Bottom Left Corner Coords(0) = X Coords(1) = Y 'Bottom right Corner Coords(2) = X + Width Coords(3) = Y 'Top Right Corner Coords(4) = X + Width Coords(5) = Y + Height 'Top Left Corner Coords(6) = X Coords(7) = Y + Height 'Create a polyline-type variable - its initial value will be empty (non-existent) Dim PL As AcadLWPolyline 'Create a polyline based on these coordinates 'The Set statement is used when creating a reference to objects (complex data types) 'The value of PL will be a reference to our new polyline Set PL = ACAD.ActiveDocument.ModelSpace.AddLightWeightPolyline(Coords) 'Add a point in AutoCAD® at this location 'Make PL a closed polyline PL.Closed = True Next End Sub
Hope this helps! And if you want to reap the benefit of spectacular hints and tips like this regularly, subscribe below! Not to mention my outstandingly witty writing style...errmm...so yes please do subscribe!
Hi Will, can't wait to get to work and give this a go, i will give u an update as soon as i get back. Many thanks for your direction on this(and the rapid reply), i now understand that i need to collect all the specific data/ options relating to the drawing task and the preceding excel calculations in order to have all the parts before i assemble the whole. I could send you a cad file of my intended drawing and an example of my basic form by way of explanation if you are interested. I shall start work on this and keep you informed of progress. Many thanks again, I have always known that you can do pretty much anything with autocad but had always assumed progamming was beyond my understanding, thanks for opening my eyes to the possibilities. I have subscribed, and will recommend associates check out your site. Best regards, Stuart
Thanks for your interest Stuart - feel free to send me anything to look at, I'm always interested in what you guys are up to!
Will, I have autocad script which is in text. How I can run autocad script through VB6.
That depends - if it's VB6 code, then it should be as simple as copying and pasting into the VBA editor. If it's something else, I'd need to see an example first :-)
This looks a lot like the AutoCAD® Configurator which I designed for my work, which works with Dynamic Blocks (DB). Excel holds the name of the DB, the insertion point (X & Y) and the names and values of the Height and Width Parameters of the DB. The VBA-routine copies the DB from the library to the new AutoCAD® Drawing and inserts the DB at the specified insertion point. The VBA-routine then loops through all the parameters of the DB and when it finds the correct Height (or Width) Parameter it will update that value. The configurator runs from May 2009 (that's almost 2 1/2 years).
i try your file and it doesnt works for me. i have a problem of "windows try to ending an ole aplication"
And vba excel debuggs to the line "Set ACAD = New AcadApplication 'Set the ACAD variable to equal a new instance of AutoCAD®"
i have windows 7 64 bits, autocad 2012 lite, excell 2010 !
I hope you can help me this week end !
Thanks a lot
I'm sorry, but the automation of AutoCAD® only works for the full version, and not AutoCAD® Lite. If you have a look at earlier versions of this article though, you may find something that works for you, such as the copy/paste methods.
Hi Will !! your codes are great !! would you know how to clear the command line (to make sure the user is not in the middle of a command) before entering "ACAD.ActiveDocument.Utility.Prompt "Hello from Excel!". Because if the user is in the middle of a command, the computer freezes and wait for the user to press escape in CAD before proceeding with the transfer. Thanks for your help in advance !
Very useful example.
I am very new to autocad automation. Recently, I was able to create an autocad dwg file with some vb6 code as follows
Private Sub Command1_Click() Dim acd As AcadApplication Dim acdg As AcadDocument Set acd = CreateObject("AutoCAD®.Application") Set acdg = acd.Application.Documents.Add Dim Height As Double Dim P(0 To 2) As Double Dim Ps(0 To 8) As Double Dim CCenter(0 To 2) As Double Dim strt(0 To 2) As Double Dim endt(0 To 2) As Double Dim TxtObj As AcadText Dim dblRadius As Double Dim objEnt As AcadSpline Height = 1 dblRadius = 12 P(0) = 2: P(1) = 2: P(2) = 0 Ps(0) = 0: Ps(1) = 1: Ps(2) = 0 Ps(3) = 2: Ps(4) = 8: Ps(5) = 0 Ps(6) = 12: Ps(7) = 15: Ps(8) = 0 strt(0) = 1: strt(1) = 1: strt(2) = 0 endt(0) = 1: endt(1) = 1: endt(2) = 0 Set TxtObj = acdg.ModelSpace.AddText("eyes are windows to the soul", P, Height) CCenter(0) = 0: CCenter(1) = 1: CCenter(2) = 0 Set objEnt = acdg.ModelSpace.AddSpline(Ps, strt, endt) acdg.SaveAs App.Path & "\new folder\test.dwg" acd.Documents.Close acd.Quit Set acd = Nothing End Sub
When I run the code, it creates an autocad file with a text and spline drawn inside New Folder
But no matter how hard I try I do not find any clue to do the same thing in vb.net.
can you please help?
Try doing it in chunks. First try to get AutoCAD® to draw the geometry you want - take a look at some of my examples for how to create geometry using managed .net code. Then you can worry about saving the files etc afterwards.
It was very kind of you! I didn't (honestly) expected such prompt reply.
I always try to program in chunks but as I told you before, I am in trouble with the declaration parts
Dim acd As AcadApplication Dim acdg As AcadDocument Set acd = CreateObject(“AutoCAD®.Application”) Set acdg = acd.Application.Documents.Add
No idea how to do that in vb.net. Everything is so much changed in vb.net. :(
Thanks a lot :) :)
No problem :-)
Hi there I've got a question. I want to change a text in autocad using excel. I used tables but it is not good enough. I have tried to make a block with text in acad, now I need a way to edit this bock's attributes with excell. I will appreciate it if you could give me a way
The way to do this would be to (somehow) get a reference to the block object, and update update the attribute properties as required by Excel.
My name is Edis both beginner and I'm in VBA in Autocad as well. Your site is very good for all of us. This post is exactly what I need in my work. But when you run this code displays the following error message:
Run-Time error'-2147024156 (800702e4) ': Automation error.
The code was as follows:
Sub Main () The Dim ACAD AcadApplication 'Create variable of type ACAD AcadApplication On Error Resume Next 'This tells VBA to ignore errors Set ACAD = GetObject (, "AutoCAD®.Application.17") 'Get the running instance of the class AutoCAD®.Application On Error GoTo 0 'This tells VBA to go back to NOT ignoring errors If ACAD Is Nothing Then 'Check to see if the above worked Set ACAD = New AcadApplication 'Set the ACAD variable equal to a new instance of AutoCAD®
The error appears on the last line code.
I'm using Windows 7, AutoCAD® 2007 and Excel 2010. Could you help me, please.
It looks like the program is trying to launch a new instance of AutoCAD® (because it's previously failed to get a running instance), but that is failing.
Have you checked that you've created a reference to the relevant libraries under Tools>References from the VBA editor accessible from Excel using Alt+F11 ?
Hey, I'm really not sure how to get this to work in AutoCAD®. I'm a beginner and new to coding entirely so it's a lot to wrap my brain around. I've downloaded your sample file but I am unclear as to how I run the VBA created in excel, in CAD?
All you need to do is open the file you downloaded in Excel, go to the code editor for Excel, and run the Main() subroutine. I think I go into detail about how exactly to do that in one of my other Excel & AutoCAD® tutorials, so it might be worthwhile looking at those.
I am trying to edit some excel OLE objects in Autocad using vb.net. After hours of searching and endless forums, I am yet to find a simple method to do this. It would be really helpful if u could help me on this. The ole objects are embedded to the autocad file.
hi , i have done some drawing in auto cad using autolisp programm , now i want to improvewise , so i need vb code to fetch data from excell and give it as inputs to autolisp code.
to explain in a nut shell i mean say for instance i have used a variable say"a" , a need some input from the user to generate a drawing , so i would like to sink like i wil open a excell file and enter value in cells (1,1) this value must be assigned to the variable a . in autolisp i will call a using my lisp code ,
so now i ask you like can u take the inputted value in cell (1,1) in excell and asssign it to a variable named a in autocad ,,
so that wen i run autolisp code in auto cad i dont have to get input input from user if i simply open code i must get drawing drawn in autocad .
Is there anyway that this code can be modified that this rectangle would be created in cad as a block ?
I have a problem here. How can I make a both way link between Excel and AutoCad. What I mean is if I make a Block attribute in AutoCad drawing then if I click the block attribute, excel spreadsheet will come out. And other way around if I made any changes in block attribute, it will automatically change also in my excel spreadsheet. ( I apologize possible mistakes in my grammar)
I am attempting to use the ATTEXT command in conjunction with a Template text file to allow me to extract attributes of different types of blocks in large system layout drawings. I am ultimately importing the extracted data in Excel to develop a simple BOM of a sub-set of the 1000s of parts in the layout drawing which is currently performed using a time-consuming manual method.
Some of the blocks are simple static blocks. The attribute data is extracted from these block as expected. Some of the blocks are dynamic with stretchable geometry. The attribute data is extracted correctly as long as the user performs REGEN. Some of the blocks are dynamic with adjustable visibility. These are the blocks which do not perform as expected. In some cases the visibility simply changes between different versions of the part. In these cases ATTEXT extracts the attribute data for all options when I only want the currently visible option. In some cases the visibility changes a sub-assembly by creating a linear array of one of a number of nested blocks that form the dynamic block. In these cases the dynamic block is completely ignored by ATTEXT.
How can I get a complete and accurate attribute extract for these dynamic blocks so that their currently selected visibility is represented.
I know I can explode the dynamic blocks and but I do not want to lose the dynamic capabilities of the blocks as the layout may require modification of those blocks.
Is this something that I can achieve from within Excel using VBA, as an alternative.