Add Row in EXCEL

[satishrajdev]

Hi All,

 

I just faced 1 problem while interacting with excel. I'm not able to add new row in excel. I searched for it but couldn't find any appropriate result.

 

My code :

       (setq ex (vlax-get-or-create-object "excel.application"))
      (vla-put-visible ex :vlax-true)
      (setq wb (vlax-get-property ex 'Workbooks))
      (vlax-for x wb
 (if (eq (strcase (vla-get-fullname x)) (strcase file))
   (setq wb1 x)
 )
      )
      (if wb1
 (setq sht (vlax-get-property wb1 "activeSheet"))
 (progn
   (setq wb1 (vla-open wb file))
   (setq sht (vlax-get-property wb1 "activeSheet"))
 )
      )

 

In below pic System Number 2 is missing, I want to add new row for that. I hope my question is clear

Piping Circuitization spreadsheet Edited.xlsx

Edited February 17, 2018 by satishrajdev

[devitg]

Please show your dwg and xls , but beware that combined cell may be a problem

[satishrajdev]

Please show your dwg and xls , but beware that combined cell may be a problem

 

I've attached excel above. Please note that I want to add row after 3rd row so that I can add system number 2 over there.

[rlx]

not my code ( http://forums.augi.com/archive/index.php/t-108747.html )

;;;================== delete column ====================
(defun C:DCOL (/ Col Cols ColNum ExcelApp FilePath Sht ShtName Wbk)

 (vl-load-com)

 (setq    FilePath (getfiled "Select Excel file :"
              (getvar "dwgprefix")
              "xls"
              16
        )
 )
 (setq ShtName (getstring "\nEnter sheet name : "))

 (setq ColNum (getint "\nEnter column number to delete : "))

 (setq ExcelApp (vlax-get-or-create-object "Excel.Application"))

 (vla-put-visible ExcelApp :vlax-true)

 (vlax-put-property ExcelApp "DisplayAlerts" :vlax-false)

 (setq    Wbk (vl-catch-all-apply
         'vla-open
         (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
       )
 )
 (setq    Sht (vl-catch-all-apply
         'vlax-get-property
         (list (vlax-get-property Wbk "Sheets")
           "Item"
           ShtName
         )
       )
 )
 (vlax-invoke-method Sht "Activate")

 (setq    Cols (vlax-get-property Sht "Columns")
 )
 (setq    Col (vlax-variant-value
         (vlax-get-property Cols "Item" ColNum)
       )
 )

 (vlax-invoke-method Col "Delete")
 (vlax-put-property ExcelApp "DisplayAlerts" :vlax-true)

 (vl-catch-all-apply
   'vlax-invoke-method
   (list Wbk "Save")
 )
 (vl-catch-all-apply
   'vlax-invoke-method
   (list Wbk "Close")
 )
 (vl-catch-all-apply
   'vlax-invoke-method
   (list ExcelApp "Quit")
 )
 (mapcar
   (function
     (lambda (x)
   (vl-catch-all-apply
     (function (lambda ()
             (progn
           (if (not (vlax-object-released-p x))
             (progn
               (vlax-release-object x)
               (setq x nil)
             )
           )
             )
           )
     )
   )
     )
   )
   (list Col Cols Sht Wbk ExcelApp)
 )
 (gc)
 (gc)
 (princ)
)


;;;================== add row ====================
(defun C:AROW (/ Col Cols RowNum ExcelApp FilePath Sht ShtName Wbk)

 (vl-load-com)

 (setq    FilePath (getfiled "Select Excel file :"
              (getvar "dwgprefix")
              "xls"
              16
        )
 )

 (setq ShtName (getstring "\nEnter sheet name : "))

 (setq
   RowNum (getint "\nEnter a row number to insert one row after: ")
 )

 (setq ExcelApp (vlax-get-or-create-object "Excel.Application"))

 (vla-put-visible ExcelApp :vlax-true)

 (vlax-put-property ExcelApp "DisplayAlerts" :vlax-false)

 (setq    Wbk (vl-catch-all-apply
         'vla-open
         (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
       )
 )
 (setq    Sht (vl-catch-all-apply
         'vlax-get-property
         (list (vlax-get-property Wbk "Sheets")
           "Item"
           ShtName
         )
       )
 )
 (vlax-invoke-method Sht "Activate")

 (setq    Rows (vlax-get-property Sht "Rows")
 )
 (setq    Row (vlax-variant-value
         (vlax-get-property Rows "Item" RowNum)
       )
 )

 (vl-catch-all-apply
   'vlax-invoke-method
   (list Row "Insert")
 )

 (vlax-put-property ExcelApp "DisplayAlerts" :vlax-true)

 (vl-catch-all-apply
   'vlax-invoke-method
   (list Wbk "Save")
 )
 (vl-catch-all-apply
   'vlax-invoke-method
   (list Wbk "Close")
 )

 (vl-catch-all-apply
   'vlax-invoke-method
   (list ExcelApp "Quit")
 )

 (mapcar
   (function
     (lambda (x)
   (vl-catch-all-apply
     (function (lambda ()
             (progn
           (if (not (vlax-object-released-p x))
             (progn
               (vlax-release-object x)
               (setq x nil)
             )
           )
             )
           )
     )
   )
     )
   )

   (list Row Rows Sht Wbk ExcelApp)
 )
 (gc)
 (gc)
 (princ)
)

gr. Rlx

Edited February 17, 2018 by rlx

[Grrr]

Heres my try:

 

(defun C:test ( / xls xlapp xlwbs xlwbk xlclss xlcl xlrow )
 (vl-catch-all-apply 'eval
   '(
     (and
       (setq xls (getfiled "Specify Excel File" (strcat (getenv "userprofile") "\\Desktop\\")  "xlsx;xls;*" 16))
       (setq xlapp (vlax-get-or-create-object "Excel.Application"))
       (setq xlwbs (vlax-get-property  xlapp 'WorkBooks))
       (setq xlwbk (vlax-invoke-method xlwbs 'Open xls))
       (setq xlclss (vlax-get-property xlapp 'Cells))
       (setq xlcl (vlax-variant-value (vlax-get-property xlclss 'item 4 1)))
       (setq xlrow (vlax-get-property xlcl 'EntireRow))
       (progn
         (vla-Select xlrow)
         (vlax-invoke-method (vla-get-Selection xlapp) 'Insert 
           -4121 ; xlDown
           0 ; xlFormatFromLeftOrAbove
         )
         (vla-Select (vlax-variant-value (vlax-get-property xlclss 'item 1 1)))
       ); progn
     ); and
   )
 ); vl-catch-all-apply
 (and (eq 'VLA-OBJECT (type xlwbk)) (vl-catch-all-apply 'vlax-invoke-method (list xlwbk 'close :vlax-true)) )
 (vl-catch-all-apply 'vlax-invoke-method (list xlapp 'quit))
 (foreach o (reverse (list xls xlapp xlwbs xlwbk xlclss xlcl xlrow)) (and (eq 'VLA-OBJECT (type o)) (vl-catch-all-apply 'vlax-release-object (list o))) )
 (princ)
); defun 

[rlx]

nice try Grrr , will add to lib

 

Gr. Rlx

[Grrr]

nice try Grrr , will add to lib

 

Gr. Rlx

 

Thanks, it has some LeeMac'ish style of coding and I've included some touch from MP: (vl-catch-all-apply 'eval '(...))

All credits to Lee! (I feel like stealing from him for the cost to have flawless coding ).

[rlx]

Thanks, it has some LeeMac'ish style of coding and I've included some touch from MP: (vl-catch-all-apply 'eval '(...))

All credits to Lee! (I feel like stealing from him for the cost to have flawless coding ).

 

Even Lee (probably) wasn't born with all the coding already in his head so I gues if you look @ it this way then all learning would be stealing

[Grrr]

Even Lee (probably) wasn't born with all the coding already in his head so I gues if you look @ it this way then all learning would be stealing

 

I agree, its just that I got too used from learning from his codes, that my style became very close to his (even the variable naming)...

So to not mistaken I just put my nickname, under the 'written by' so I would have in mind "be careful of unaccounted or stupid errors", aswell his - if he missed to place it.

 

However on the how-to-do-it part, I learn from all of you guys.

Rlx, you have alot of functions in your codes that I wanted to ask you about where/how/why.. unfortunately I don't have enough time to keep tracing/learning so much codes.

[satishrajdev]

Thanks a lot guys... My problem is solved.

 

Learned something new today

[rlx]

However on the how-to-do-it part, I learn from all of you guys.

Rlx, you have alot of functions in your codes that I wanted to ask you about where/how/why.. unfortunately I don't have enough time to keep tracing/learning so much codes.

 

Don't worry , sometimes even I don't understand my own code anymore , one year later

 

Glad to hear your problem is solved satishrajdev

 

gr. Rlx