Originally Posted by
mdsalman2003
Using visual lisp how can we preform following operations
> adding row/column
> merging/unmerging cells
> deleting row/column
I have searched excel object model & internet but cannot find some helpful material
any code snipped/or useful internet link will be appreciated.
thanks.
For example delete column:
Code:
;;;================== 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)
)
Or add row
Code:
;;;================== 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)
)
See also Merge/Unmerge methods for the Range object
~'J'~