Excel and AutoCAD® - A match made in heaven - Part 3

William Forty

May 3, 2011

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

WindowMaker

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!
Will

Comments

Stuart
2011-05-03 23:30:07

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

Will
2011-05-04 09:50:16

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!

Nandish
2011-06-17 06:05:58

Will, I have autocad script which is in text. How I can run autocad script through VB6.

Nandish

Will
2011-06-21 09:21:55

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

Teun Ham
2011-10-06 08:15:30

Nice example!

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

theye
2011-10-07 17:28:21

hi will

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

theye,

Will
2011-10-08 07:22:31

Theye,

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.

Will

DANY
2012-03-08 19:32:02

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 !

Dany !!

MliC
2012-04-12 10:59:24

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 &amp; "\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?

Best Regards

Will
2012-04-12 17:13:46

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.

MliC
2012-04-12 20:12:55

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

Will
2012-05-25 10:44:02

No problem :-)

Masoud
2012-08-31 04:39:45

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

Will
2012-10-02 11:39:30

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.

Edis Nobrega
2013-02-27 19:46:02

Hello Will:

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.

Thank you,

EDIS.

Will
2013-03-18 13:09:42

Hi,

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 ?

Will

Edis Nobrega
2013-04-03 21:35:45

thank you.

Alex
2014-07-17 16:53:59

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?

Will
2014-07-21 11:45:27

Hi Alex,

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.

Will

Gokul
2014-08-18 09:19:59

Hi WilI

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.

Deepan Sakravarthy
2014-09-24 06:13:15

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 .

Ali
2015-07-15 21:43:52

Is there anyway that this code can be modified that this rectangle would be created in cad as a block ?

Carlos
2015-09-18 20:56:23

Hi Will.

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)

Thanks, carlos

Andre
2016-11-04 16:11:04

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.

← Previous

AutoCAD® Fractal - A bit of fun...

AutoCAD® 2012 System Requirements