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

by Will on 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

Enter Your Mail Address

Be Sociable, Share!

{ 18 comments }

Stuart May 3, 2011 at 11:30 pm

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 May 4, 2011 at 9:50 am

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 June 17, 2011 at 6:05 am

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

Nandish

Will June 21, 2011 at 9:21 am

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 October 6, 2011 at 8:15 am

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 October 7, 2011 at 5:28 pm

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 October 8, 2011 at 7:22 am

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 March 8, 2012 at 7:32 pm

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 April 12, 2012 at 10:59 am

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?

Best Regards

Will April 12, 2012 at 5:13 pm

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 April 12, 2012 at 8:12 pm

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 May 25, 2012 at 10:44 am

No problem :-)

Masoud August 31, 2012 at 4:39 am

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 October 2, 2012 at 11:39 am

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 February 27, 2013 at 7:46 pm

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 March 18, 2013 at 1:09 pm

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

Alex July 17, 2014 at 4:53 pm

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 July 21, 2014 at 11:45 am

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

Comments on this entry are closed.

Previous post:

Next post: