Author Topic: Copy/Insert copied cells in Excel  (Read 309 times)

0 Members and 1 Guest are viewing this topic.

CincyJeff

  • Newt
  • Posts: 60
Copy/Insert copied cells in Excel
« 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?

mailmaverick

  • Bull Frog
  • Posts: 344
Re: Copy/Insert copied cells in Excel
« Reply #1 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




CincyJeff

  • Newt
  • Posts: 60
Re: Copy/Insert copied cells in Excel
« Reply #2 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.

CincyJeff

  • Newt
  • Posts: 60
Re: Copy/Insert copied cells in Excel
« Reply #3 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. )