LISP Program to Copy Dimensions to Clipboard and Paste in Excel

[Anonymous]

I am looking for a LISP program that will allow me to select multiple dimensions, copy them to the clipboard, and paste into Excel. The dimensions should paste into adjacent horizontal cells. I have attached photos with a sample AutoCAD drawing and the desired Excel format.

The command should have the following prompts:

1. Select dimension 1

     > I select dimension 1 with a window/crossing/click then hit the space bar

2. Select dimension 2

     > I select dimension 2 with a window/crossing/click then hit the space bar

3. Select dimension 3

     > I select dimension 3 with a window/crossing/click then hit the space bar

4. Select dimension 4

     > I select dimension 4 with a window/crossing/click then hit the space bar, and the command ends.

If there are fewer than four dimensions hitting the space bar with no selection should end the command.

Then I go over to an open excel file and CTRL+V to populate the fields.

Is this possible? If not could someone suggest an alternative such that I can easily transfer these dimensions to a preexisting excel file?

[pendean]

If I may ask a dumb question: what is the purpose of the final Excel with random dimensions listed in a spreadsheet?

[CodeDing]

@Anonymous ,

I believe pendean is asking an appropriate question. What is the ultimate goal of having exactly 4 dimensions in a spreadsheet? Also, will it be 4 every time? Do they need to be in a specific order or any order?

But, most importantly, is the Why in this case.

Best,

~DD

[Anonymous]

The photos I attached are uncontextualized because I did not want to clutter my post with unnecessary information.

The dimensions are the flange lengths of a bent sheet metal part. The excel file is a cut list ultimately populated with dozens/hundreds of these parts. The excel file will have a formula that adds the flange lengths and a bend allowance to calculate the flat length.

The maximum number of bends is 3, therefore the maximum number of flange dimensions is 4. The dimensions must be in order.

My current practice is to manually transfer the dimensions to the excel file, but I am, even on good days, error prone. Would the program I described in my original post be possible?

[CodeDing · Accepted solution]

@Anonymous ,

Try this. This approach adds a TAB character "\t" to your text so that when you paste, it will TAB to the next cell.

You specifically mentioned the ability to select dimension OR use window... so using (ssget) with much checking is the only approach I could think of.

Using Visual Lisp seems to be the most efficient way to add this text to the clipboard, so I searched for a function to do that.

Also, it should be noted that the units here are Architectural (rtos ... 4), this is based on the photos you provided. Hope this helps..

(defun c:C2E ( / _SetClipBoardText ss txt cnt len)
(vl-load-com)
;Function to set clipboard
  (defun _SetClipBoardText ( text / htmlfile result )
    ;;  Attribution: Reformatted version of
    ;;  post by XShrimp at theswamp.org.
    ;;  See http://tinyurl.com/2ngf4r.
    (setq result
        (vlax-invoke
            (vlax-get
                (vlax-get
                    (setq htmlfile (vlax-create-object "htmlfile"))
                   'ParentWindow
                )
               'ClipBoardData
            )
           'SetData
            "Text"
            text
        )
    )
    (vlax-release-object htmlfile)
    text
  );defun
;Get input from user. 4 dims, in order w/ window (ssget) capability
(setq ss nil cnt 1 txt "")
(while (not ss)
  (prompt (strcat "\nSelect Dimension " (itoa cnt) ": "))
  (if (setq ss (ssget '((0 . "DIMENSION"))))
    (if (= 1 (sslength ss))
      (progn
	(setq len (cdr (assoc 42 (entget (ssname ss 0)))))
	(setq txt (strcat txt (rtos len 4)))
	(if (/= 4 cnt) (setq ss nil txt (strcat txt "\t")))
	(setq cnt (1+ cnt) len nil)
      );progn
      (progn
	(setq ss nil)
	(prompt "\n...invalid. More than 1 object selected.")
      );progn
    );if
  ;else
    (prompt "\n...nothing selected")
  );if
);while
;If string, send to clipboard and send feedback to user
(if (< 0 (strlen txt))
  (progn
    (_SetClipBoardText txt)
    (prompt "\nText Successfully copied to clipboard.")
  );progn
    (prompt "\nFailure, no text to copy to clipboard...")
);if
(princ);finish quietly
);defun

Best,

~DD

[Anonymous]

Sometimes the bent pieces only have 1/2/3 flanges. Is there any way to edit this code so that it could be used for these parts? For example after I select 3 flange dimensions, I could hit the spacebar without a selection to end the command (and copy the 3 dimensions).

But, this code is pretty much perfect, thank you so much for your help.

[CodeDing · Accepted solution]

@Anonymous ,

Sure thing. Give this a go..

(defun c:C2E ( / _SetClipBoardText ss txt cnt len tab)
(vl-load-com)
;Function to set clipboard
  (defun _SetClipBoardText ( text / htmlfile result )
    ;;  Attribution: Reformatted version of
    ;;  post by XShrimp at theswamp.org.
    ;;  See http://tinyurl.com/2ngf4r.
    (setq result
        (vlax-invoke
            (vlax-get
                (vlax-get
                    (setq htmlfile (vlax-create-object "htmlfile"))
                   'ParentWindow
                )
               'ClipBoardData
            )
           'SetData
            "Text"
            text
        )
    )
    (vlax-release-object htmlfile)
    text
  );defun
;Get input from user. 4 dims, in order w/ window (ssget) capability
(setq ss nil cnt 1 txt "" tab "")
(while (not ss)
  (prompt (strcat "\nSelect Dimension " (itoa cnt) ": "))
  (setq ss (ssget '((0 . "DIMENSION"))))
  (cond
    ((not ss)
      (prompt (strcat "\n...nothing selected for object " (itoa cnt)))
      (if (<= 4 cnt) (setq ss t))
      (setq cnt (1+ cnt))
    );cond 1
    ((= 1 (sslength ss))
      (if (or (= 1 cnt) (= 0 (strlen txt))) (setq tab "") (setq tab "\t"))
      (setq len (cdr (assoc 42 (entget (ssname ss 0)))))
      (setq txt (strcat txt tab (rtos len 4)))
      (if (<= 4 cnt) (setq ss t) (setq ss nil))
      (setq cnt (1+ cnt) len nil)
    );cond 2
    (t 
      (setq ss nil)
      (prompt "\n...invalid. More than 1 object selected.")
    );cond else
  );cond
);while
;If string, send to clipboard and send feedback to user
(if (< 0 (strlen txt))
  (progn
    (_SetClipBoardText txt)
    (prompt "\nText Successfully copied to clipboard.")
  );progn
    (prompt "\nFailure, no text to copy to clipboard...")
);if
(princ);finish quietly
);defun

Best,

~DD

[Anonymous]

This is perfect, thank you!

[Moshe-A]

@Anonymous  hi,

here is my version...it will let you collect your dimensions (1 to 4 dimension or whatever you need, by pressing enter you terminate the selection) than directly send it to excel (csv) file and even open the file in microsoft excel from there you can copy & paste as needed.

the call to open microsoft excel is done by (startapp) function, it's first param must be the full path of microsoft excel.  since i do not know what version you have, i call (get_excel_path) function which gets the full path of excel from registry. if this function fails, you will get prompt that excel is no exist but this could be wrong. to fix this you have to replace (get_excel_path) with a similar function and do not forget to duplicate each backslash to separate between folders name as in my origin code  

(defun get_excel_path (/ path)
  (setq path "YourExcelFullpathName")
)

enjoy

moshe

(vl-load-com); load ActiveX support

; Send Text dimension to Excel
(defun c:ST2E (/ get_excel_path ; local function
	         i recLine pick ename elist lst dwgFile excelFile f)

 ; return excel.exe full path for registry
 (defun get_excel_path (/ path)
  (if (or
     	(setq path (vl-registry-read "HKEY_CURRENT_USER\\Software\\Microsoft\\IntelliPoint\\AppSpecific\\EXCEL.EXE" "Path"))
    	(setq path (vl-registry-read "HKEY_CURRENT_USER\\Software\\Microsoft\\IntelliType Pro\\AppSpecific\\EXCEL.EXE" "Path"))
      )
   path
  )
 ); get_excel_path

 ; here start command 
 (setq i 0 recLine "")
 (while (setq pick (entsel (strcat "\nSelect dimension #" (itoa (setq i (1+ i))) ": ")))
  (setq ename (car pick) elist (entget ename))
   
  (if (not (eq (cdr (assoc '0 elist)) "DIMENSION"))
   (progn
    (vlr-beep-reaction)
    (prompt "\nentity selected is not dimension.")
   )
   (progn
    (setq lst (cons ename lst)) 
    (redraw ename 3)
    (setq recLine (strcat recLine (rtos (cdr (assoc '42 elist)) 4) ","))
   )
  ); if
 ); while
  
 ; de-highlight dimensions
 (foreach ename lst
  (redraw ename 4)
 )
  
 (setq recLine (substr recLine 1 (1- (strlen recLine)))) ; chop last comma delim
  
 (setq dwgFile (strcat (getvar "dwgprefix") (getvar "dwgname")))
 (setq excelFile (strcat (substr dwgFile 1 (- (strlen dwgFile) 3)) "csv"))
  
 ; write to excel file
 (if (not (findfile excelFile))
  (progn 
   (setq f (open excelFile "w"))
   (prompt (strcat "\nCreating " excelFile " file."))
  ); progn
  (progn
   (setq f (open excelFile "a"))
   (prompt (strcat "\nAppending to " excelFile " file."))
  ); progn
 ); if
   
 (write-line recLine f) 
 (setq f (close f))

 (if (not (setq excelPath (get_excel_path)))
  (progn
   (vlr-beep-reaction)
   (prompt "\nMicrosoft excel is not exist.")
  )
  ; else, excel.exe found in registry
  (startapp (vl-prin1-to-string excelPath) (vl-prin1-to-string excelFile))
 )
  
 (princ) 
); C:ST2E

[sameerulcdc]

can we get dim value in MM format
please 

[Kent1Cooper]

....
  (setq recLine (strcat recLine (rtos (cdr (assoc '42 elist)) 2) ","))
.... 

[That's assuming your drawing unit is a millimeter.  If it's something else, such as a meter, a calculation would need to be made.]  Read about the (rtos) function in the AutoLisp Reference.

[itteam]

can we get dim value in MM format
please

[ancrayzy]

Hello everyone, a have a same problem,

I want to copy values ​​from dimensions in autocad in the following way

First, I put the mouse pointer in cell A2 in Excel then switch to AutoCad and do the following:
- Click dimension 1 then hit the space bar > Lisp copy Dim 1 value and paste to cell A2 then move to cell B2
- Click dimension 2 then hit the space bar > Lisp copy Dim 2 value and paste to cell B2 then move to cell A3
- Click dimension 3 then hit the space bar > Lisp copy Dim 3 value and paste to cell A3 then move to cell B3
- Click dimension 4 then hit the space bar > Lisp copy Dim 4 value and paste to cell B3 then move to cell A4
And so on...

Is there any way to solve it.

Regards.

[Sea-Haven]

Yes the background code is out there, you can write direct to Excel, if its just pick 2 dims repeatedly pretty easy.

Try this

; https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/lisp-program-to-copy-dimensions-to-clipboard-and-paste-in-excel/td-p/8705507
; export dims to excel
; By AlanH June 2024
(defun dim2excel ( / row ent txt)
(if (not columnrow)(load "alan excel library3"))

(xlsetcelltext  1 1 "X")
(xlsetcelltext  1 2 "Y")
(setq row 2)
(while (setq ent (entsel "\nPick X dim Enter to exit "))
(setq txt (vlax-get (vlax-ename->vla-object (car ent)) 'Measurement))
(xlsetcelltext  row 1 txt)
(setq ent (entsel "\nPick X dim Enter to exit "))
(setq txt (vlax-get (vlax-ename->vla-object (car ent)) 'Measurement))
(xlsetcelltext  row 2 txt)
(setq row (1+ row))
)
(princ)
)
(dim2excel)

You need to save Alan Excel library3.lsp in a support path, or change the (load to include the full path, as its auto loaded.

This is a very simple example as checking Excel is open and correct workbook involves a lot more code. So either have a blank Excel open or just not at all.

[ancrayzy]

Thanks @Sea-Haven , but I don't see defun c:example in attachment and your code above.

Can you explain further how to use it ?

Regards.

[Sea-Haven]

Updated code above, when you load the code it will run. Make sure the Alan Excel library3.lsp is saved in a support path or change the (load "c:\\your\\directory\\oflisps\\Alan Excel library3") to include correct path. Preferably don't save on desktop.

[ancrayzy]

Thanks @Sea-Haven 

The updated code still doesn't work; the defun c: command is missing.

When I load the these Autolisps, I got the following error:

Command: (LOAD "E:/WORK/CAD TOOLS/100. Tools/Alan Excel library3.lsp") ; error: syntax error

Command: (LOAD "E:/WORK/CAD TOOLS/04. Excel Routines/ExportDimsToExcel.lsp") ; error: no function definition: XLSETCELLTEXT

Regards.

[Sea-Haven]

You need to use a correct path syntax. Most times use \\, if no spaces in paths can sometimes use /. See post 16 it had \\.

Just copy the line below to the command line to test.

(LOAD "E:\\WORK\\CAD TOOLS\\100. Tools\\Alan Excel library3.lsp")

[ancrayzy]

(LOAD "E:\\WORK\\CAD TOOLS\\100. Tools\\Alan Excel library3.lsp")

---

When I copy this command into the command line, it automatically opens Excel. => it worked well.

However, the Lisp code dim2excel reports an error.

Command: DIM2EXCEL
"Setting cell at (1, 1) to X"
"Setting cell at (1, 2) to Y"
Pick X dim Enter to exit ; error: bad argument type: stringp 7849.02
Command:
Command: DIM2EXCEL
"Setting cell at (1, 1) to X"
"Setting cell at (1, 2) to Y"
Pick X dim Enter to exit ; error: bad argument type: stringp 2750.0

It didn't push value to Excel

Is that possible if I want to use the above lisp code to transfer the dimension value from CAD to any Excel file without having to create a new file.

Regards.

[Sea-Haven]

One of the extra options I am looking at is just asking,

Use a new excel simplest way

Use current Excel need to find last cell that has been entered yes code exists. 

Open an existing ask for a file name and check for last cell used.

Ok will think about changing code to ask for a file name, 1st step is ask "Is Excel open and is it the filename" you can retrieve the file name from a Excel using lisp. Same as can get sheet names, add etc.