TheSwamp

Code Red => AutoLISP (Vanilla / Visual) => Topic started by: CincyJeff on December 14, 2016, 08:52:05 AM

Title: Copy/Insert copied cells in Excel
Post by: CincyJeff on December 14, 2016, 08:52:05 AM
I'm trying to copy 4 rows in an Excel Worksheet and then Insert copied cells so I can duplicate the 4 rows for each object in a selection set. I can open Excel and set the Workbook and Worksheet current but I don't know how to put the rows into the clipboard and insert them back in. I may have been able to isolate the rows with:
Code - Auto/Visual Lisp: [Select]
  1. (setq *excelapp* (vlax-get-or-create-object "Excel.Application")
  2. (setq actsht (vlax-get-property *excelapp* 'activesheet))
  3. (setq rows (vlax-get-property actsht 'rows))
  4. (setq curreg (vlax-get-property rows 'currentregion))
  5. (setq rows1 (vlax-get-property curreg 'rows))
The rows1 object has a count of 4. This was only accomplished by manually selecting the 4 rows which I don't want to do. I would like to make the selection programmatically.
My goal would be to open the Worksheet and place the 4 rows, 17-20, into the Clipboard and Insert them back in beneath the original 4 rows, 21-24 etc..., repeatedly based on how many objects the user has placed into a selection set.
Can anybody offer some help as to where I can find how to do this?
Title: Re: Copy/Insert copied cells in Excel
Post by: mailmaverick on December 15, 2016, 11:54:22 AM
Kindly see Excel Library by our legendry FIXO

http://www.theswamp.org/index.php?topic=38450.msg466989#msg466989 (http://www.theswamp.org/index.php?topic=38450.msg466989#msg466989)



Title: Re: Copy/Insert copied cells in Excel
Post by: CincyJeff on December 15, 2016, 01:15:49 PM
Thanks mailmaverick, what a library. I'll dig into it and see if it gets me what I need. Thanks for sharing FIXO.
Title: Re: Copy/Insert copied cells in Excel
Post by: CincyJeff on January 11, 2017, 10:13:46 AM
I got it to work thanks to the Excel functions from FIXO. I modified them slightly as listed. I just needed to get the copy range, invoke 'copy, set a new range for the copy location, and invoke 'insert. It works beautifully.

Code - Auto/Visual Lisp: [Select]
  1. (setq *excelapp* (vlax-get-or-create-object "Excel.Application"))
  2. (setq copyrange (EXCL:SETRANGE *excelapp* 15 1 18 39))
  3. (vlax-invoke-method copyrange 'copy)
  4. (setq newrange (EXCL:SETRANGE *excelapp* 19 1 19 39))
  5. (vlax-invoke-method newrange 'insert (vlax-make-variant -4121)) ; -4121 means shiftdown
  6.  
  7. (defun EXCL:SETRANGE (*excelapp* r1 c1 r2 c2 / )
  8.   (EXCL:SETSELECTION
  9.     *excelapp*
  10.     (cond ((vl-every 'numberp (list r1 c1 r2 c2))
  11.            (vlax-get-property
  12.              *excelapp*
  13.              'range
  14.              (vlax-get-property
  15.                (vlax-get-property
  16.                  (vlax-get-property *excelapp* 'activesheet)
  17.                  'cells
  18.                ) ;_ end of vlax-get-property
  19.                'item
  20.                (vlax-make-variant r1)
  21.                (vlax-make-variant c1)
  22.              ) ;_ end of vlax-get-property
  23.              (vlax-get-property
  24.                (vlax-get-property
  25.                  (vlax-get-property *excelapp* 'activesheet)
  26.                  'cells
  27.                ) ;_ end of vlax-get-property
  28.                'item
  29.                (vlax-make-variant r2)
  30.                (vlax-make-variant c2)
  31.              ) ;_ end of vlax-get-property
  32.            ) ;_ end of vlax-get-property
  33.           )
  34.           ((and r1 (not c1) (not r2) (not c2))
  35.            (vlax-get-property *excelapp* 'range (vlax-make-variant r1))
  36.           )
  37.           ((and r1 c1 (not r2) (not c2))
  38.            (vlax-get-property
  39.              *excelapp*
  40.              'range
  41.              (vlax-make-variant r1)
  42.              (vlax-make-variant c1)
  43.            ) ;_ end of vlax-get-property
  44.           )
  45.           (t nil)
  46.     ) ;_ end of cond
  47.   ) ;_ end of xlsetselection
  48. )
  49.  
  50. (defun EXCL:SETSELECTION (xlapp xlrange / selrange)
  51.   (and (vl-catch-all-apply
  52.          'vlax-invoke-method
  53.          (list xlrange 'select)
  54.        ) ;_ end of vl-catch-all-apply
  55.        (setq selrange (vl-catch-all-apply
  56.                         'vlax-get-property
  57.                         (list xlapp 'selection)
  58.                       ) ;_ end of vl-catch-all-apply
  59.        ) ;_ end of setq
  60.   ) ;_ end of and
  61.   selrange
  62. )