Jump to content

Add Row in EXCEL


satishrajdev

Recommended Posts

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

Sample.png

Piping Circuitization spreadsheet Edited.xlsx

Edited by satishrajdev
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by rlx
Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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 :oops: ).

Link to comment
Share on other sites

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 :oops: ).

 

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 ;)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :P

 

Glad to hear your problem is solved satishrajdev

 

gr. Rlx

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.

×
×
  • Create New...