Jump to content

Text to excel sheet

Peter K

By Peter K
in AutoLISP, Visual LISP & DCL

 Share

Recommended Posts

Lee Mac Grand Master

Lee Mac

Posted
Posted
lol honestly i half expected you to come back and tell me how it's not a good thing to go through all of the objects in model space and test to see if they are text objects... lol your welcome. I also figured you hadnt tested it i read your other posts about your computer =/

 

did you fix that?

 

Not yet - still working on it :(

Link to comment
Share on other sites
  • Replies 57
  • Created
  • Last Reply

Top Posters In This Topic

  • Lee Mac

    23

  • Commandobill

    15

  • Peter K

    7

  • cmcm800

    3

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

ronjonp
ronjonp

Wildcards will definitely work ... look at the reference HERE.

cmcm800 Newbie

cmcm800

Posted
Posted

I just ran across this post after a couple of hours of searching for solutions and I hoped either of you could help provide some direction or possibly a solution.

 

We need to extract all of the text (text, mtext and attributes) from the AutoCAD file into a table (excel or csv) and associate it with the filename. The idea is to index all of our autocad files so that we can perform a string search and have every drawing containing the string be shown.

 

I look forward to your direction...! :unsure:

Link to comment
Share on other sites
Lee Mac Grand Master

Lee Mac

Posted
Posted

Give this a shot (borrowed some code from CommandoBill):

 

[b][color=RED]([/color][/b][b][color=BLUE]defun[/color][/b] c:ttx  [b][color=RED]([/color][/b][b][color=BLUE]/[/color][/b] ss xlApp xlBooks xlBook xlSheets xlSheet xlCells column row[b][color=RED])[/color][/b]
 [b][color=RED]([/color][/b][b][color=BLUE]vl-load-com[/color][/b][b][color=RED])[/color][/b]
 [b][color=RED]([/color][/b][b][color=BLUE]if[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] ss [b][color=RED]([/color][/b][b][color=BLUE]ssget[/color][/b] [b][color=#ff00ff]"_X"[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]list[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]cons[/color][/b] [b][color=#009900]0[/color][/b] [b][color=#ff00ff]"*TEXT"[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
   [b][color=RED]([/color][/b][b][color=BLUE]progn[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] xlApp    [b][color=RED]([/color][/b][b][color=BLUE]vlax-get-or-create-object[/color][/b] [b][color=#ff00ff]"Excel.Application"[/color][/b][b][color=RED])[/color][/b]
           xlBooks  [b][color=RED]([/color][/b][b][color=BLUE]vlax-get-property[/color][/b] xlApp [b][color=#ff00ff]"Workbooks"[/color][/b][b][color=RED])[/color][/b]
           xlBook   [b][color=RED]([/color][/b][b][color=BLUE]vlax-invoke-method[/color][/b] xlBooks [b][color=#ff00ff]"Add"[/color][/b][b][color=RED])[/color][/b]
           xlSheets [b][color=RED]([/color][/b][b][color=BLUE]vlax-get-property[/color][/b] xlBook [b][color=#ff00ff]"Sheets"[/color][/b][b][color=RED])[/color][/b]
           xlSheet  [b][color=RED]([/color][/b][b][color=BLUE]vlax-get-property[/color][/b] xlSheets [b][color=#ff00ff]"Item"[/color][/b] [b][color=#009900]1[/color][/b][b][color=RED])[/color][/b]
           xlCells  [b][color=RED]([/color][/b][b][color=BLUE]vlax-get-property[/color][/b] xlSheet [b][color=#ff00ff]"Cells"[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]vla-put-visible[/color][/b] xlApp [b][color=Blue]:vlax-true[/color][color=RED])[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] column [b][color=#009900]1[/color][/b] row [b][color=#009900]1[/color][/b][b][color=RED])[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]foreach[/color][/b] Str [b][color=RED]([/color][/b][b][color=BLUE]mapcar[/color][/b] [b][color=DARKRED]'[/color][/b][b][color=BLUE]vla-get-TextString[/color][/b]
                    [b][color=RED]([/color][/b][b][color=BLUE]mapcar[/color][/b] [b][color=DARKRED]'[/color][/b][b][color=BLUE]vlax-ename->vla-object[/color][/b]
                      [b][color=RED]([/color][/b][b][color=BLUE]mapcar[/color][/b] [b][color=DARKRED]'[/color][/b][b][color=BLUE]cadr[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]ssnamex[/color][/b] ss[b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
       [b][color=RED]([/color][/b][b][color=BLUE]if[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]<[/color][/b] [b][color=#009900]65536[/color][/b] row[b][color=RED])[/color][/b]
         [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] column [b][color=#009900]3[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
       [b][color=RED]([/color][/b][b][color=BLUE]vlax-put-property[/color][/b] xlCells [b][color=#ff00ff]"Item"[/color][/b] row column Str[b][color=RED])[/color][/b]
       [b][color=RED]([/color][/b][b][color=BLUE]vlax-put-property[/color][/b] xlCells [b][color=#ff00ff]"Item"[/color][/b] row [b][color=RED]([/color][/b][b][color=BLUE]1+[/color][/b] column[b][color=RED])[/color][/b]
         [b][color=RED]([/color][/b][b][color=BLUE]strcat[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]getvar[/color][/b] [b][color=#ff00ff]"DWGPREFIX"[/color][/b][b][color=RED])[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]getvar[/color][/b] [b][color=#ff00ff]"DWGNAME"[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
       [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] row [b][color=RED]([/color][/b][b][color=BLUE]1+[/color][/b] row[b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]mapcar[/color][/b]
       [b][color=RED]([/color][/b][b][color=BLUE]function[/color][/b]
         [b][color=RED]([/color][/b][b][color=BLUE]lambda[/color][/b] [b][color=RED]([/color][/b]x[b][color=RED])[/color][/b]
           [b][color=RED]([/color][/b][b][color=BLUE]vl-catch-all-apply[/color][/b]
             [b][color=RED]([/color][/b][b][color=BLUE]function[/color][/b]
               [b][color=RED]([/color][/b][b][color=BLUE]lambda[/color][/b] [b][color=RED]([/color][/b][b][color=RED])[/color][/b]
                 [b][color=RED]([/color][/b][b][color=BLUE]progn[/color][/b]
                   [b][color=RED]([/color][/b][b][color=BLUE]vlax-release-object[/color][/b] x[b][color=RED])[/color][/b]
                   [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] x [b][color=BLUE]nil[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
         [b][color=RED]([/color][/b][b][color=BLUE]list[/color][/b] xlCells xlSheet xlSheets xlBook xlBooks xlApp[b][color=RED])[/color][/b][b][color=RED])[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]alert[/color][/b] [b][color=#ff00ff]"Close Excel file manually"[/color][/b][b][color=RED])[/color][/b][b][color=RED]([/color][/b][b][color=BLUE]gc[/color][/b][b][color=RED])[/color][/b][b][color=RED]([/color][/b][b][color=BLUE]gc[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
   [b][color=RED]([/color][/b][b][color=BLUE]princ[/color][/b] [b][color=#ff00ff]"\n<< No Text Found in Drawing >>"[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
 [b][color=RED]([/color][/b][b][color=BLUE]princ[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]

Link to comment
Share on other sites
cmcm800 Newbie

cmcm800

Posted
Posted

My apologies... I had to edit my last reply.

 

The result is one excel file only reporting the result for text and mtext with their associated filename.

 

No result for the attribute text.

Link to comment
Share on other sites
Lee Mac Grand Master

Lee Mac

Posted
Posted
The result is two files...

 

An excel file containing a row for the text with the drawing filename in the same row and a row for the mtext with a drawing filename in the same row.

 

And a csv file containing two rows for the attribute results with no filename.

 

All works fine for me :huh:

 

It shouldn't save any files.

 

It only opens Excel and writes the values - what you do with the Excel doc is up to you :)

Link to comment
Share on other sites
cmcm800 Newbie

cmcm800

Posted
Posted

My apologies... you replied faster than I could correct my last post.

 

The result is one excel file only reporting the result for text and mtext with their associated filename, but I have no result for the attribute text from the block that I have in the drawing.

 

The last issue that I have beyond extracting the attribute text is to be able to write to the next row the same excel book when I run the lisp again in another autocad file. I see that you are offline... I will play with the lisp (I know only enough to get me into trouble), but look forward to your comments... Thanks!

Link to comment
Share on other sites
Lee Mac Grand Master

Lee Mac

Posted
Posted

The result is one excel file only reporting the result for text and mtext with their associated filename, but I have no result for the attribute text from the block that I have in the drawing.

 

I did not realise that you wanted ATTRIBute text also extracted - I must have missed that in the earlier post, but I am sure that this can be incorporated. :)

 

The last issue that I have beyond extracting the attribute text is to be able to write to the next row the same excel book when I run the lisp again in another autocad file. I see that you are offline... I will play with the lisp (I know only enough to get me into trouble), but look forward to your comments... Thanks!

 

This is easily achieved and I shall incorporate this into the new code :)

Link to comment
Share on other sites
Lee Mac Grand Master

Lee Mac

Posted
Posted

This should do your bidding :D

 

But Excel File must exist prior to running code - so just save an empty Workbook before first run.

 

[b][color=RED]([/color][/b][b][color=BLUE]defun[/color][/b] c:ttx  [b][color=RED]([/color][/b][b][color=BLUE]/[/color][/b] ss file xlApp xlCells[b][color=RED])[/color][/b]
 [b][color=RED]([/color][/b][b][color=BLUE]vl-load-com[/color][/b][b][color=RED])[/color][/b]
 [b][color=RED]([/color][/b][b][color=BLUE]if[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]and[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] ss [b][color=RED]([/color][/b][b][color=BLUE]ssget[/color][/b] [b][color=#ff00ff]"_X"[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]list[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]cons[/color][/b] [b][color=#009900]0[/color][/b] [b][color=#ff00ff]"*TEXT,INSERT"[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
          [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] file [b][color=RED]([/color][/b][b][color=BLUE]getfiled[/color][/b] [b][color=#ff00ff]"Select Excel File"[/color][/b] [b][color=#ff00ff]""[/color][/b] [b][color=#ff00ff]"xls"[/color][/b] [b][color=#009900]8[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
   [b][color=RED]([/color][/b][b][color=BLUE]progn[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] xlApp   [b][color=RED]([/color][/b][b][color=BLUE]vlax-get-or-create-object[/color][/b] [b][color=#ff00ff]"Excel.Application"[/color][/b][b][color=RED])[/color][/b]
           xlCells [b][color=RED]([/color][/b][b][color=BLUE]vlax-get-property[/color][/b]
                     [b][color=RED]([/color][/b][b][color=BLUE]vlax-get-property[/color][/b]
                       [b][color=RED]([/color][/b][b][color=BLUE]vlax-get-property[/color][/b]
                         [b][color=RED]([/color][/b][b][color=BLUE]vlax-invoke-method[/color][/b]
                           [b][color=RED]([/color][/b][b][color=BLUE]vlax-get-property[/color][/b] xlApp [b][color=#ff00ff]"Workbooks"[/color][/b][b][color=RED])[/color][/b]
                             [b][color=DARKRED]'[/color][/b][b][color=BLUE]Open[/color][/b] file[b][color=RED])[/color][/b] [b][color=#ff00ff]"Sheets"[/color][/b][b][color=RED])[/color][/b] [b][color=#ff00ff]"Item"[/color][/b] [b][color=#009900]1[/color][/b][b][color=RED])[/color][/b] [b][color=#ff00ff]"Cells"[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]vla-put-visible[/color][/b] xlApp [b][color=Blue]:vlax-true[/color][color=RED])[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]or[/color][/b] *column* [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] *column* [b][color=#009900]1[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]or[/color][/b] *row* [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] *row* [b][color=#009900]1[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]foreach[/color][/b] Str [b][color=RED]([/color][/b][b][color=BLUE]mapcar[/color][/b] [b][color=DARKRED]'[/color][/b][b][color=BLUE]vla-get-TextString[/color][/b]
                    [b][color=RED]([/color][/b][b][color=BLUE]apply[/color][/b] [b][color=DARKRED]'[/color][/b][b][color=BLUE]append[/color][/b]
                      [b][color=RED]([/color][/b][b][color=BLUE]mapcar[/color][/b]
                        [b][color=RED]([/color][/b][b][color=BLUE]function[/color][/b]
                          [b][color=RED]([/color][/b][b][color=BLUE]lambda[/color][/b] [b][color=RED]([/color][/b]x[b][color=RED])[/color][/b]
                            [b][color=RED]([/color][/b][b][color=BLUE]if[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]eq[/color][/b] [b][color=#ff00ff]"AcDbBlockReference"[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]vla-get-ObjectName[/color][/b] x[b][color=RED])[/color][/b][b][color=RED])[/color][/b]
                              [b][color=RED]([/color][/b][b][color=BLUE]vlax-safearray->list[/color][/b]
                                [b][color=RED]([/color][/b][b][color=BLUE]vlax-variant-value[/color][/b]
                                  [b][color=RED]([/color][/b][b][color=BLUE]vla-getAttributes[/color][/b] x[b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]list[/color][/b] x[b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
                        [b][color=RED]([/color][/b][b][color=BLUE]vl-remove-if[/color][/b]
                          [b][color=RED]([/color][/b][b][color=BLUE]function[/color][/b]
                            [b][color=RED]([/color][/b][b][color=BLUE]lambda[/color][/b] [b][color=RED]([/color][/b]x[b][color=RED])[/color][/b]
                              [b][color=RED]([/color][/b][b][color=BLUE]and[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]eq[/color][/b] [b][color=#ff00ff]"AcDbBlockReference"[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]vla-get-ObjectName[/color][/b] x[b][color=RED])[/color][/b][b][color=RED])[/color][/b]
                                   [b][color=RED]([/color][/b][b][color=BLUE]eq[/color][/b] [b][color=Blue]:vlax-false[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]vla-get-HasAttributes[/color][/b] x[b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
                          [b][color=RED]([/color][/b][b][color=BLUE]mapcar[/color][/b] [b][color=DARKRED]'[/color][/b][b][color=BLUE]vlax-ename->vla-object[/color][/b]
                            [b][color=RED]([/color][/b][b][color=BLUE]mapcar[/color][/b] [b][color=DARKRED]'[/color][/b][b][color=BLUE]cadr[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]ssnamex[/color][/b] ss[b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
       [b][color=RED]([/color][/b][b][color=BLUE]if[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]<[/color][/b] [b][color=#009900]65536[/color][/b] *row*[b][color=RED])[/color][/b]
         [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] *column* [b][color=#009900]3[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
       [b][color=RED]([/color][/b][b][color=BLUE]vlax-put-property[/color][/b] xlCells [b][color=#ff00ff]"Item"[/color][/b] *row* *column* Str[b][color=RED])[/color][/b]
       [b][color=RED]([/color][/b][b][color=BLUE]vlax-put-property[/color][/b] xlCells [b][color=#ff00ff]"Item"[/color][/b] *row* [b][color=RED]([/color][/b][b][color=BLUE]1+[/color][/b] *column*[b][color=RED])[/color][/b]
         [b][color=RED]([/color][/b][b][color=BLUE]strcat[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]getvar[/color][/b] [b][color=#ff00ff]"DWGPREFIX"[/color][/b][b][color=RED])[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]getvar[/color][/b] [b][color=#ff00ff]"DWGNAME"[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
       [b][color=RED]([/color][/b][b][color=BLUE]setq[/color][/b] *row* [b][color=RED]([/color][/b][b][color=BLUE]1+[/color][/b] *row*[b][color=RED])[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]vlax-release-object[/color][/b] xlApp[b][color=RED])[/color][/b]
     [b][color=RED]([/color][/b][b][color=BLUE]alert[/color][/b] [b][color=#ff00ff]"Close Excel File Manually"[/color][/b][b][color=RED])[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]gc[/color][/b][b][color=RED])[/color][/b] [b][color=RED]([/color][/b][b][color=BLUE]gc[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
   [b][color=RED]([/color][/b][b][color=BLUE]princ[/color][/b] [b][color=#ff00ff]"\n<< No Text Found in Drawing or No File Selected >>"[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]
 [b][color=RED]([/color][/b][b][color=BLUE]princ[/color][/b][b][color=RED])[/color][/b][b][color=RED])[/color][/b]

Link to comment
Share on other sites
Commandobill Proficient

Commandobill

Posted
Posted

Hey Lee your code is only going to write over his existing file that way. Perhaps you can find a better way but, I propose something like this.

 

(defun c:ttx  (/ ss file xlApp xlCells xlshe Ucells *column* *row* Str)
 (vl-load-com)
 (if (setq ss (ssget "_X" (list (cons 0 "*TEXT,INSERT"))))           
   (progn
     (setq xlApp   (vlax-get-or-create-object "Excel.Application"))
     (if (setq file (getfiled "Select Excel File" "" "xls" )
   (progn  (setq xlshe   (vlax-get-property
               (vlax-get
                 (vla-open
                   (vlax-get xlApp "Workbooks") file) "Sheets") "Item" 1)
             Ucells  (vlax-get xlshe "Usedrange")
                     xlCells (vlax-get xlshe "Cells"))
           (setq *column*  (1- (vlax-get (vlax-get ucells "Columns") "Count"))
                     *row*     (1+ (vlax-get (vlax-get ucells "Rows")    "Count"))))
   (progn  (setq xlshe   (vlax-get-property
               (vlax-get
                 (vla-add (vlax-get xlApp "Workbooks") file) "Sheets") "Item" 1)
             Ucells  (vlax-get xlshe "Usedrange")
                     xlCells (vlax-get xlshe "Cells"))
           (setq *column*  (setq *column* 1)
                     *row*     (setq *row*    1))))
     
     (vla-put-visible xlApp :vlax-true)
     (foreach Str (mapcar 'vla-get-TextString
                    (apply 'append
                      (mapcar
                        (function
                          (lambda (x)
                            (if (eq "AcDbBlockReference" (vla-get-ObjectName x))
                              (vlax-safearray->list
                                (vlax-variant-value
                                  (vla-getAttributes x))) (list x))))
                        (vl-remove-if
                          (function
                            (lambda (x)
                              (and (eq "AcDbBlockReference" (vla-get-ObjectName x))
                                   (eq :vlax-false (vla-get-HasAttributes x)))))
                          (mapcar 'vlax-ename->vla-object
                            (mapcar 'cadr (ssnamex ss)))))))
       (if (< 65536 *row*)
         (setq *column* 3))
       (vlax-put-property xlCells "Item" *row* *column* Str)
       (vlax-put-property xlCells "Item" *row* (1+ *column*)
         (strcat (getvar "DWGPREFIX") (getvar "DWGNAME")))
       (setq *row* (1+ *row*)))
     (vlax-release-object xlApp)
     (alert "Close Excel File Manually") (gc) (gc))
   (princ "\n<< No Text Found in Drawing or No File Selected >>"))
 (princ))

 

I also added the choice to either create a new file or open an existing. Honestly i could have worked harder on it but i just lack time.

Link to comment
Share on other sites
Lee Mac Grand Master

Lee Mac

Posted
Posted

Bill,

 

I let my *column* and *row* variables be global, so that it would not overwrite within a session, but yes, it would overwrite when the session is over.

 

I am pretty new to writing to Excel, as I normally only write to CSV - so thanks for altering my code.

 

PS> where did you learn the code to write to Excel mate?

Link to comment
Share on other sites
Commandobill Proficient

Commandobill

Posted
Posted
Bill,

 

I let my *column* and *row* variables be global, so that it would not overwrite within a session, but yes, it would overwrite when the session is over.

 

I am pretty new to writing to Excel, as I normally only write to CSV - so thanks for altering my code.

 

PS> where did you learn the code to write to Excel mate?

 

Lol thats a good question. I wanted to write a lisp that involved excel and there was a lisp floating around my office to write the length of all polylines on all layers to excel. So i started by reading that code then i went into excel and went into vba and read the help files. Of course I've only looked at the parts that were usefull to me so I've only scratched the surface...

Link to comment
Share on other sites
Lee Mac Grand Master

Lee Mac

Posted
Posted
Lol thats a good question. I wanted to write a lisp that involved excel and there was a lisp floating around my office to write the length of all polylines on all layers to excel. So i started by reading that code then i went into excel and went into vba and read the help files. Of course I've only looked at the parts that were usefull to me so I've only scratched the surface...

 

I'm just not sure of the hierarchy, and which property & method names to invoke etc...

 

like:

 

"Usedrange"

"Item"

"Sheets"

"Cells"

etc etc

 

Is this all in the VBA help?

Link to comment
Share on other sites
Commandobill Proficient

Commandobill

Posted
Posted
I'm just not sure of the hierarchy, and which property & method names to invoke etc...

 

like:

 

"Usedrange"

"Item"

"Sheets"

"Cells"

etc etc

 

Is this all in the VBA help?

 

Yes, yes it is. Just like when looking through the vlisp help looking for vla commands it will point out the hierarchy. On top of that i make great use of the (vlax-dump-object) command :wink:

Link to comment
Share on other sites
Lee Mac Grand Master

Lee Mac

Posted
Posted
Yes, yes it is. Just like when looking through the vlisp help looking for vla commands it will point out the hierarchy. On top of that i make great use of the (vlax-dump-object) command :wink:

 

Good point..

 

Thanks Bill - this'll take some learning :P

Link to comment
Share on other sites
Commandobill Proficient

Commandobill

Posted
Posted
Good point..

 

Thanks Bill - this'll take some learning :P

 

Leave it to you to always be looking for the next step 8)

Link to comment
Share on other sites
Commandobill Proficient

Commandobill

Posted
Posted
I'm always trying to branch out - I think I'm pretty confident at VL now that I have been using that since January this year:

 

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

 

So I thought I'd move it on a bit, and get to learn some ObjectDBX or something... :P

 

My experience with vl started with that excel lisp i wrote. I always seem to dive in too deep and fight to make my way to the surface. I guess thats why freerefill thinks all my lisps are created using 'magic' lol. Well seeing as though we've derailed this thread i guess i should go back to work. Good luck learning code for excel mate.

Link to comment
Share on other sites
Lee Mac Grand Master

Lee Mac

Posted
Posted

Bill,

 

I have a few questions regarding your last code post:

 

1) You use this code to get the Cell Range that is already being used in the Excel File:

 

Ucells  (vlax-get xlshe "Usedrange")
                    xlCells (vlax-get xlshe "Cells"))
              (setq *column* (1- (vlax-get (vlax-get ucells "Columns") "Count"))
                    *row*    (1+ (vlax-get (vlax-get ucells "Rows") "Count"))))

Why do you need to add one to the row count, and take one from the column count? (is it just that the program returns one more column than needed and the number last row used? - if that makes sense...)

 

2) You use this code if the user does not select an existing file:

 

(progn
         (setq xlshe   (vlax-get-property
                              (vlax-get
                                (vla-add (vlax-get xlApp "Workbooks") [color=Red][b]file[/b][/color])
                                "Sheets")
                              "Item"
                              1)
                    [b][color=Red]Ucells  (vlax-get xlshe "Usedrange")
                    xlCells (vlax-get xlshe "Cells"))[/color][/b]
              (setq *column* (setq *column* 1)
                    *row*    (setq *row* 1))))

But surely the value of file is nil at this point? So how can you use it with vla-add?

 

Also, you get the values of the UsedRange and Cells, yet, surely if this is a new file, then the column and row count are just both 1?

 

 

Sorry if I am ripping your code to bits a little here, but I am really just trying to understand your methodology.

 

Thanks

 

Lee

 

EDIT: Re: 2) I can see why you collect xlCells, as it needs to be assigned for use later in the program, but my question remains about usedrange.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...