Author Topic: Using Excel to control AutoCAD  (Read 18807 times)

0 Members and 1 Guest are viewing this topic.

GegH1

  • Guest
Using Excel to control AutoCAD
« on: April 14, 2013, 09:56:10 PM »
I have a client who has an excel file he uses to calculate quotes.
He has requested that the excel file be dynamically linked to CAD so that when the cells are updated so is the drawing.
For example, he will be building a box, the box has a width, length, height. He will fill this out on his excel form in front of the client which will give him the costings it will also draw the box in AutoCAD.
When the client thinks it is too much he will be able to adjust the size of the box from the excel sheet and the cost and drawing is updated dynamically.

The drawing will always be a box.

Firstly, is this possible? (someone once said to me 'Anything is possible, the question is whether you have the knowledge to do it'....i'm not that knowledgeable...yet). Secondly, How?

Here's the kicker, he will next ask if he can use LT to do it? initially i would say no, you can't use lsp, .net or database with LT.....but would it be possible with using a lsp enabler like Cadsta Max?

Any help and advice would be awesome, i have to first identify whether it is possible then how much it will cost him to develop.

MexicanCustard

  • Swamp Rat
  • Posts: 705
Re: Using Excel to control AutoCAD
« Reply #1 on: April 15, 2013, 08:36:40 AM »
Is it possible in vanilla AutoCAD and it's verticals? Certainly, I use the ClosedXML library to do this now.  It can also be done in COM if Microsoft Office is installed on the machine with AutoCAD.  I just prefer the non-COM method.

Can it be done in LT?  I cannot answer that since I've never worked with LT.
Revit 2019, AMEP 2019 64bit Win 10

TheMaster

  • Guest
Re: Using Excel to control AutoCAD
« Reply #2 on: April 15, 2013, 10:41:24 AM »
I have a client who has an excel file he uses to calculate quotes.
He has requested that the excel file be dynamically linked to CAD so that when the cells are updated so is the drawing.
For example, he will be building a box, the box has a width, length, height. He will fill this out on his excel form in front of the client which will give him the costings it will also draw the box in AutoCAD.
When the client thinks it is too much he will be able to adjust the size of the box from the excel sheet and the cost and drawing is updated dynamically.

The drawing will always be a box.

Firstly, is this possible? (someone once said to me 'Anything is possible, the question is whether you have the knowledge to do it'....i'm not that knowledgeable...yet). Secondly, How?

Here's the kicker, he will next ask if he can use LT to do it? initially i would say no, you can't use lsp, .net or database with LT.....but would it be possible with using a lsp enabler like Cadsta Max?

Any help and advice would be awesome, i have to first identify whether it is possible then how much it will cost him to develop.

It's possible with AutoCAD, but not with LT out of the box. It's also possible to do entirely with AutoCAD, without the need for excel, using constraints, and requires no programming.

However, doing that sort of thing without great expense requires a reasonable level of experience with AutoCAD and its APIs. If you don't have that, then you're probably going to have to charge a lot more than someone with the prerequisites needed to produce a solution in a reasonable amount of time, and at a reasonable cost.

GegH1

  • Guest
Re: Using Excel to control AutoCAD
« Reply #3 on: April 16, 2013, 05:47:57 AM »
Guys, thankyou for this.

Excel is the pre-requisite for the project as that is what is used for his costings, materials lists, worksheets and ordering.

I do know CAD pretty well and i've done a bit of work with vb.net recently and a fair bit with scripts in the bad old days.

Any ideas where i can go to get started?

fixo

  • Guest
Re: Using Excel to control AutoCAD
« Reply #4 on: April 16, 2013, 06:21:56 AM »

GegH1

  • Guest
Re: Using Excel to control AutoCAD
« Reply #5 on: April 16, 2013, 06:47:45 AM »
That's interesting Fixo, i imagine it wouldn't take a lot to adapt this to manipulate a dynamic block based on numbers in the excel sheet?
Do you think it could do this dynamically as you move from Cell to cell?

TheMaster

  • Guest
Re: Using Excel to control AutoCAD
« Reply #6 on: April 16, 2013, 08:06:06 AM »
Any ideas where i can go to get started?

I'm not sure what you mean.

If you have the prerequisites (e.g., being familiar with the AutoCAD COM API) then it is pretty straight forward, but the programming has to be done on the Excel side, not on the AutoCAD side.

GegH1

  • Guest
Re: Using Excel to control AutoCAD
« Reply #7 on: April 16, 2013, 05:53:22 PM »
I have a lot of experience with AutoCAD and only a little with developing API's for autocad.
Maybe i'm having a brain freeze but i don't see anything straight forward about it. Could you give me a clue?

TheMaster

  • Guest
Re: Using Excel to control AutoCAD
« Reply #8 on: April 16, 2013, 09:43:02 PM »
I have a lot of experience with AutoCAD and only a little with developing API's for autocad.
Maybe i'm having a brain freeze but i don't see anything straight forward about it. Could you give me a clue?

You have to drive the changes to the AutoCAD model from Excel, using whatever API (e.g., Excel VBA, etc) you prefer. The AutoCAD COM API can be accessed from Excel VBA or any ActiveX-enabled API.

When I said 'straight forward', I meant for someone that is reasonably-familiar with AutoCAD's APIs.

GegH1

  • Guest
Re: Using Excel to control AutoCAD
« Reply #9 on: April 16, 2013, 10:35:06 PM »
Now that i'm not familiar with, i've been learning VB.net with AutoCAD but i have not driven autocad from an external program like Excel.

Could you give me a starter for 10 or an example so i can have a go and identify whether the client has the budget or i have the patience to proceed with the project, or at least proceed with the clients project. It sounds like something i'd like to learn anyway.

Thankyou for your help and patience

TheMaster

  • Guest
Re: Using Excel to control AutoCAD
« Reply #10 on: April 17, 2013, 12:53:16 AM »
Now that i'm not familiar with, i've been learning VB.net with AutoCAD but i have not driven autocad from an external program like Excel.

Could you give me a starter for 10 or an example so i can have a go and identify whether the client has the budget or i have the patience to proceed with the project, or at least proceed with the clients project. It sounds like something i'd like to learn anyway.

Thankyou for your help and patience

Sorry, what you're asking for is not something I consider within the bounds of helping peers with problems. You're asking for a bit more, and what you're seeking to do requires at least a year or two of full-time experience.

I'm available for consulting services, but of course, there's a fee involved.

hugha

  • Newt
  • Posts: 103
Re: Using Excel to control AutoCAD
« Reply #11 on: April 17, 2013, 03:33:48 AM »
If you have some familiarity with AutoLISP You may find this useful:

http://www.jefferypsanders.com/autolisp_XL.html


dgorsman

  • Water Moccasin
  • Posts: 2437
Re: Using Excel to control AutoCAD
« Reply #12 on: April 17, 2013, 02:26:12 PM »
Based on the responses, I'm not certain the OP would be restricted to automating AutoCAD from the outside via COM.  This could be done within AutoCAD, consuming the Excel content as data.  Thats generally easier and faster than the former.  The "do this in front of the client" routine where everything updates as the data is changed is a bit much as described; it would be easier to do the work in Excel, switch to an active session of AutoCAD and execute an update or refresh command which would read the new values in Excel and change the drawing accordingly.

Since code is the end product and not the starting point, I think the best place to start with is detailed evaluation of the various processes you need e.g. read Excel file, convert data to something useable (validate for bad info!), create or link to AutoCAD objects, modify AutoCAD objects, and so on.  At this point, you should have a rough idea of the time budget required for the work.  Those procedural notes can be expanded into rough code, which can then be tested to evaluate desired work processes, help determine where user input is required and in what form.  The rough code can then be detailed out to get the final product.

As different interactions with AutoCAD are needed (e.g. create a line, insert a block) they can be investigated using examples here, in the ObjectARX SDK, and elsewhere.
If you are going to fly by the seat of your pants, expect friction burns.

try {GreatPower;}
   catch (notResponsible)
      {NextTime(PlanAhead);}
   finally
      {MasterBasics;}

TheMaster

  • Guest
Re: Using Excel to control AutoCAD
« Reply #13 on: April 17, 2013, 03:15:10 PM »
Based on the responses, I'm not certain the OP would be restricted to automating AutoCAD from the outside via COM.  This could be done within AutoCAD, consuming the Excel content as data.  Thats generally easier and faster than the former.  The "do this in front of the client" routine where everything updates as the data is changed is a bit much as described; it would be easier to do the work in Excel, switch to an active session of AutoCAD and execute an update or refresh command which would read the new values in Excel and change the drawing accordingly.

Since code is the end product and not the starting point, I think the best place to start with is detailed evaluation of the various processes you need e.g. read Excel file, convert data to something useable (validate for bad info!), create or link to AutoCAD objects, modify AutoCAD objects, and so on.  At this point, you should have a rough idea of the time budget required for the work.  Those procedural notes can be expanded into rough code, which can then be tested to evaluate desired work processes, help determine where user input is required and in what form.  The rough code can then be detailed out to get the final product.

As different interactions with AutoCAD are needed (e.g. create a line, insert a block) they can be investigated using examples here, in the ObjectARX SDK, and elsewhere.

I would probably read what the OP wrote again.  It would appear that the 'client' doesn't currently have AutoCAD, is probably only familiar with Excel, and therefore, would most-likely not savor having to learn AutoCAD in order to automate its use using Excel data as a driver.

The other thing you make sound a bit too easy for the OP, is that in order to estimate the cost of doing development work, one must be reasonably-familiar with development and have some experience doing it, knowing what's entailed, what options are available, and so forth.

It wouldn't be easy for someone that's just starting out with AutoCAD development, that hasn't really done much besides hack on samples they've gotten from here and there, and may not understand very well, to be able to tell how many hours of work will be required to do something, if they don't know where to start.

Where I come from, people that represent themselves as professional consultants that are competent and qualified to do certain work, have to sign consulting services agreements that require them to certify in writing that they are qualified and capable of doing the work. And if it turns out that they're not, and the client suffers injury as a result, a professional liability insurance policy would probably come in handy.  I suppose that's why some clients (mostly in the public sector),  require their consultants to carry them.




« Last Edit: April 17, 2013, 03:27:14 PM by TT »

hugha

  • Newt
  • Posts: 103
Re: Using Excel to control AutoCAD
« Reply #14 on: April 17, 2013, 09:28:27 PM »
OP mentioned his client wants an AutoCAD drawing of a box driven from Excel. Going right to basics, if there are only a few parameters then it is perfectly possible to create the drawing from a script (.scr) file written and started by Excel VBA to drive the parameterised portion of the drawing within a selected template (.dwt) using LT.  Some skill with Office VBA required. It's very painstaking work akin to using Excel macros rather than its VBA, there's no room for error and very little error detection or error recovery.

This elementary approach won't easily scale of course to any level of complexity within the generated drawing - there's a point where AutoLISP becomes irresistible and you must leave LT behind (unless the add-on enablers actually do provide the promised programming functionality) for  full-fat AutoCAD, BricsCAD, Graebert's ARES or other programmable workalike.  Much can be done within the *Lisp macro workspace using various extender libraries.

The full flexibility offered by exploiting VBA or .NET automation where good development tools can be brought to bear (as well demonstrated all over this board) comes at the considerable cost of time spent acquiring sufficient competency.  While it's tempting to use the full kit for everything (why not? - it's now simpler) in some smaller cases it's a sledgehammer.

I second the necessity of always having E&O insurance. Don't leave home without it.  Gaining it often exposes deficiencies in how well you are set up to consult.
 

« Last Edit: April 17, 2013, 09:35:41 PM by hugha »