AutoCAD to Excel Linking Queries

[CrustyFur]

I've been working with some code I've found on the Internet and have successfully managed to link AutoCAD to Excel.

 

I have a couple of questions though. Firstly, are the functions that are loaded from Excel.exe using code such as...

 

(vlax-import-type-library

:tlb-filename tbl ;Table

:methods-prefix "xlm-" ;methods

:properties-prefix "xlp-" ;properties

:constants-prefix "xlc-" ;constants

)

 

...documented anywhere? Apropos isn't much help. Excel doesn't seem to have anything.

 

Secondly, I can clear a range of cells on a cell by cell basis (using xlp-put-Value2) but this takes a while. Is there a function to clear a range, say A4:C500, in one go?

 

Cheers, Chris

[Lee Mac]

The methods aren't documented too well at all.

 

There is some info found in here:

http://midpointcad.com/au/docs/lakose_The_Visual_LISP_Developers_Bible.pdf

 

If you are still stuck, you may get a better response at theSwamp.org

 

As an aside, Please don't double post, it makes the Mods job even harder and just confuses things.

 

Lee

[Commandobill]

On another note. From my experience the Excel functions are not necessary to link autocad and excel.

[Lee Mac]

Good point Bill,

 

See this for an example:

http://www.cadtutor.net/forum/showthread.php?t=37127

[Commandobill]

(setq    xlApp (vlax-get-or-create-object "Excel.Application")
         xlCells       (vlax-get-property
                          (vlax-get-property
                           (vlax-get-property
                            (vla-open
                             (vlax-get-property xlApp "Workbooks")
                            "c:\\book1.xls")
                           "Sheets")
                          "Item" 1)
                         "Range" "A1:A4"))
(vlax-map-collection xlcells 'vla-clear)

clears all contents. obviously edit the name and range

[Commandobill]

Good point Bill,

 

See this for an example:

http://www.cadtutor.net/forum/showthread.php?t=37127

 

Hey! where was my special thanks?

[Lee Mac]

Hey! where was my special thanks?

 

Haha

[CrustyFur]

Lee, that's a very useful document, thanks. I've only skipped through it at the moment as I'm on holiday (but still thinking work...!)

[CrustyFur]

(setq    xlApp (vlax-get-or-create-object "Excel.Application")
         xlCells       (vlax-get-property
                          (vlax-get-property
                           (vlax-get-property
                            (vla-open
                             (vlax-get-property xlApp "Workbooks")
                            "c:\\book1.xls")
                           "Sheets")
                          "Item" 1)
                         "Range" "A1:A4"))
(vlax-map-collection xlcells 'vla-clear)

clears all contents. obviously edit the name and range

 

Excellent! That's going to help me a lot. Thanks.

[Lee Mac]

Lee, that's a very useful document, thanks. I've only skipped through it at the moment as I'm on holiday (but still thinking work...!)

 

Yeah, its pretty good - but I think it sometimes just skirts around topics instead of delving too far into them. But a good read all the same.

[CrustyFur]

(setq    xlApp (vlax-get-or-create-object "Excel.Application")
         xlCells       (vlax-get-property
                          (vlax-get-property
                           (vlax-get-property
                            (vla-open
                             (vlax-get-property xlApp "Workbooks")
                            "c:\\book1.xls")
                           "Sheets")
                          "Item" 1)
                         "Range" "A1:A4"))
(vlax-map-collection xlcells 'vla-clear)

clears all contents. obviously edit the name and range

 

Ah, another question for you if you don't mind... whilst I can make sense of the code above, the bit I'm missing is where you learnt it from in the first place. Do you know if the processes of getting worksheets, cell and ranges documented anywhere? I'm sure there's a whole load of other stuff that would be of use to me.

 

The Visual LISP Developers Bible that Lee recommended does actually say that 'this is not very clearly documented...' I'd worked that much out!

 

Regards, Chris

[Lee Mac]

I suppose a vlax-dump of each object helps - but it still leads to trial and error.

 

I learnt the methods from Bill, and also Terry Miller's "GetExcel" program here, but I would be interested to see if the methods/hierarchy were documented anywhere.

 

Lee

[Lee Mac]

This perhaps offers a bit more of an insight I suppose:

 

http://msdn.microsoft.com/en-us/library/aa198460(office.10).aspx

 

EDIT:

 

Or this is better:

http://msdn.microsoft.com/en-us/library/aa272260(office.11).aspx

[Commandobill]

Vla takes all of it's hierarchy from vba. If you open Excel and go into it's Visual Basic Editor Help it's all layed out for you right there just like in the Visual Lisp Help file

[Lee Mac]

Vla takes all of it's hierarchy from vba. If you open Excel and go into it's Visual Basic Editor Help it's all layed out for you right there just like in the Visual Lisp Help file

 

 

Nice one Bill, so it is

[Commandobill]

and so I've told you b4

 

http://www.cadtutor.net/forum/showthread.php?t=36816&page=4

[Lee Mac]

:oops: :oops:

[CrustyFur]

Vla takes all of it's hierarchy from vba. If you open Excel and go into it's Visual Basic Editor Help it's all layed out for you right there just like in the Visual Lisp Help file

 

I'd been down that route before and searched for 'put-value' but as no results came up, thought I may be in the wrong place. I will dig a little deeper and also get one of our VBA programmers to have a look.

 

Thanks again, Chris

[Lee Mac]

To put a value in a cell, you will need to get the Cells object, then use something like:

 

(vlax-put-property xlCells "Item" row col "test_string")

 

Where row and col are integers, and xlCells is the cells object.

[Commandobill]

I'd been down that route before and searched for 'put-value' but as no results came up, thought I may be in the wrong place. I will dig a little deeper and also get one of our VBA programmers to have a look.

 

Thanks again, Chris

 

Thats because 'put-value' isn't anything. Value is a property that belongs to a range object. With properties you can vla-get or vla-put.Thus vla-put-value. If you were to look up 'Value' you would have found that information. I wouldnt be surprized if Lee could point you to a tutorial for the difference between "Collections, Objects, Properties, Methods, Events, etc.." Once you have that down it's a cake-walk