Excel File Troubles

[Bill Tillman]

I am trying to become an expert on interfacing between Excel and AutoCAD in a relatively short period of time. So for now I am stumbling through many of these issues. This morning I found what seems to be a nice collection of LISP code which can work with Excel files, it's called GetExcel.lsp. I've learned a lot by studying the code so far but there are some problems I can't seem to figure. First off, the dreaded (vl-load-com) issue. It was puzzling to me why developers leave this out of the code until I read Lee Macs insightful posting about ACADDOC.lsp.

 

I have a very simple spreadsheet with values in only 3 cells. When I call the GetExcel part of the code I keep getting this message:

 

; error: Automation Error. Excel cannot access 'Template'. The document may be

read-only or encrypted.

 

The file in question is neither open by anyone else, nor is it set to R/O. I can post the code I'm using but it's rather lengthy and is available at this URL:

 

http://web2.iadfw.net/terrycad/LISP/GetExcel.lsp

 

My goal is to learn how to open any Excel file, read in the required data, and then be able to create a quick drawing based on the values being dimensions. I think I can handle that part if I can just find a way to get the data from Excel into my AutoCAD file.

 

Thanks again and I appreciate everyone's patience. I realize this is brutal explaining to a novice from experienced programmers here at this forum.

[Lee Mac]

Hi Bill,

 

You've set yourself quite a challenge! In the past I've written quite a few applications which interface with Excel, some freeware, some not, but in my opinion, one of the most difficult parts about working with Excel is the size of the Excel API coupled with the layout of the MSDN documentation which can be like navigating through a maze at times.

 

Here are some examples from theSwamp which may help you towards your goal:

 

http://www.theswamp.org/index.php?topic=31441.0

http://www.theswamp.org/index.php?topic=10101.0

http://www.theswamp.org/index.php?topic=35157.0

[Lee Mac]

Here is the MSDN Documentation:

 

Excel Object Model Reference

Excel Object Model Map

[Bill Tillman]

Thanks alot. I'm in a real pickle in that I've been out of work for the last few months and need a job badly. A local firm has asked me to assist and if I can prove that at least I can get the basics done, I might land a long term project with them. They want to interface between Exel and AutoCAD. My programming skills aren't the best but their improving everyday so here's to catching this train.

[BlackBox]

Good luck, Bill!

[Bill Tillman]

It's starting to gel for me. I have gotten the GetExcel code to return the list *ExcelData@ and it contains this:

 

(("1" "2" "3")) which is something like what I expected because cells A1, B1 and C1 contain the values 1,2 and 3 respectively. I was having a brain fart earlier and got that part wrong.

 

So I want to parse this now and isolate the three items in this list so I can assign them to variables. In my haste I tried:

 

(setq pt1 (car *ExcelData@))

 

which I am assuming will return "1" or just 1 as its the first item in the list. But instead pt1 ends up being ("1" "2" "3") with only one set of the paranthesis being removed.

 

Okay I'm seeing the light now. If I ask for (car *ExcelData@) I will get the list with the outside parens removed because that's actually the first item in the list. Then I ask for (car pt1) and I get "1"....hooray, but how can I get rid of the quotes? Or is it necessary to convert this to a number as I plan on using these values as dimensons eventually to draw some simple shapes like rectangles and lines, etc..

[Lee Mac]

(mapcar 'atof (car *ExcelData@))

[Bill Tillman]

(setq pt1 (mapcar 'atof (car *ExcelData@)))
 (setq pt2 (car pt1))
 (setq pt3 (cadr pt1))
 (setq pt4 (caddr pt1))

 

This gets me where I wanted to be today. The three values now assigned to something I can work with in AutoCAD and do some drawings. Now I'm dangerous. As they say, I now know enough to really screw things up....!

 

Today, a simple spreadsheet....Tomorrow, the World!

[ScribbleJ]

Bill do you happen to have AutoCad Map 3D? If so the task at hand can be much easier. I will leave it at that until you reply other than Map 3D has the same functionality as ArcGIS software does in respect to database files (i.e. converted Excel files). Once you get your data into Map 3D you can make thematic maps with it.

[Bill Tillman]

No, I'm not using 3D Civil, I more of an architectural/structural user. So plain vanilla AutoCAD is what I have to work with.

 

I had so many distractions today but was able to get back and play around some more with the GetExcel.lsp program. Turns out there is another module in it called GetCell which does the parsing of the data as well and you can simply ask for it to:

 

(setq pt6 (GetCell "C1"))

 

and it will give you the value in cell "C1". Still, I'm learning alot more about how to interface between Excel and AutoCAD. Each day I know 100% more than I did yesterday.

[ScribbleJ]

AutoCad Map 3D is part of Civil 3D but it is also has its own version with the same title of AutoCad Map 3D. I think it is only about $200 more than vanilla AutoCad.

[elmoleaf]

Good luck.

I used that getexcel lisp as my starting point also.

I modified it a bit to take x,y and z values from excel and assign them to the xscale, yscale and zscale of a block.

By this method I was able to automatically generate attributed blocks within a drawing that represent each piece of equipment listed in an excel spreadsheet.