Merging Cells in an Excel File

[jgardner.79905]

I am trying to add a line to an existing lisp file that export/writes to an excel file that will merge cell "A11:G11". I have been going through a number of posting but have had no luck figuring this out. I have only gotten as far as the following (with an excel file open):

(setq XL (vlax-create-object "Excel.Application)

from here I cannot seem to crack it. Can anyone help

Jason

[BlackBox]

I am trying to add a line to an existing lisp file that export/writes to an excel file that will merge cell "A11:G11". I have been going through a number of posting but have had no luck figuring this out. I have only gotten as far as the following (with an excel file open):

(setq XL (vlax-create-object "Excel.Application"))

from here I cannot seem to crack it. Can anyone help

Jason

** Don't forget the other quote sign, and second paren **

I'm not sure how to accomplish your desired goal exactly, but this seems like a good place to look:



(setq XL (vlax-create-object "Excel.Application"))
(vlax-dump-object XL T)
(textpage) ; Change focus


At a glance the Selection property, and the Union method sound fruitful...?

Again, I've not tried this, and do not have time to test. Let us know...?

Be sure, when you're done, that you:



(vlax-release-object XL)

[BlackBox]

... I have only gotten as far as the following (with an excel file open) ...

Also, I may be misunderstanding you here, but if you're attempting to merge cells for an already open Excel file, then maybe try a different method:


(vlax-create-object prog-id) ; Creates a new instance

(vlax-get-object prog-id) ; Gets an open instance

(vlax-get-or-create-object prog-id) ; Gets open instance, or creates new if not open

[fixo]

I am trying to add a line to an existing lisp file that export/writes to an excel file that will merge cell "A11:G11". I have been going through a number of posting but have had no luck figuring this out. I have only gotten as far as the following (with an excel file open):

(setq XL (vlax-create-object "Excel.Application)

from here I cannot seem to crack it. Can anyone help

Jason

Try this code



(defun C:demo(/ ExcelApp FilePath Sht shtNum Wbk xlRange xlCells)
(vl-load-com)
;;local defun
;; write data to cell
(defun settext(xlCells iRow iCol strValue)
(vlax-put-property
xlCells
"Item"
iRow
iCol
strValue)
)

;;==========================main part=============================;;
(setq FilePath (getfiled "Select Excel file to read :"
(getvar "dwgprefix")
"xls"
16
)
)
(setq ShtNum (getint "\nEnter sheet number <1>: "))
(if (not ShtNum)(setq ShtNum 1))

(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true)
(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" ShtNum)))
(vlax-invoke-method Sht "Activate")
(setq xlCells (vlax-get-property Sht "Cells"))

(setq xlRange (vlax-get-property Sht "Range"
"A1:G1"))
(vlax-invoke-method xlRange "Select")
(setq xlRange (vlax-get-property ExcelApp "Selection"))

(vlax-put-property xlRange
"MergeCells" :vlax-true)

(vlax-put-property xlRange "Horizontalalignment" (vlax-make-variant -4108 3))
(settext xlCells 1 1 "The very loooooooooooooooong header")
(settext xlCells 2 1 "Hi, dude")
(settext xlCells 2 2 "-->Next cell")
(mapcar
(function (lambda (x)
(if (not (vlax-object-released-p x))

(vlax-release-object x)
)
)
)
(list xlRange Sht Wbk ExcelApp)
)
(setq xlRange nil
Sht nil
Wbk nil
ExcelApp nil
)
(gc)(gc)
(princ)
)


~'J'~

[fixo]

I am trying to add a line to an existing lisp file that export/writes to an excel file that will merge cell "A11:G11". I have been going through a number of posting but have had no luck figuring this out. I have only gotten as far as the following (with an excel file open):

(setq XL (vlax-create-object "Excel.Application)

from here I cannot seem to crack it. Can anyone help

Jason
Jason
My monitor is out of order, I can see just
a little piece of screen at left :)
So by this reason
I can't answer to your PM
See you later
Here is some code from oldies
change to suit


(defun C:test (/ Bord ExcelApp FilePath Fonto Rang Sel Sht ShtNum Wbk)
(vl-load-com)

(setq FilePath (getfiled "Select Excel file to read :"
(getvar "dwgprefix")
"xls"
16
)
)
(setq ShtNum (getint "\nEnter sheet number <1>: "))
(if (not ShtNum)(setq ShtNum 1))

(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true);or :vlax-false for invisible mode
(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" ShtNum)))
(vlax-invoke-method Sht "Activate")
;;Find last cell :
(vlax-invoke-method ExcelApp "Volatile")
(setq FindRang (vlax-get-property Sht "UsedRange"))
;;; (setq lastRow (vlax-invoke FindRang "Find" (list "*" 2 1)))
;;; (setq lastCol (vlax-invoke FindRang "Find" (list "*" 2 0)))
(setq RowNum (vlax-get-property
(vlax-get-property
FindRang "Rows") "Count"))
(setq lastRow (vlax-variant-value
(vlax-get-property (vlax-get-property
FindRang "Rows") "Item" RowNum)))
(setq lastCell (vlax-get-property lastRow "End" 2))
;;working with columns :
(setq Rang (vlax-variant-value
(vlax-get-property (vlax-get-property Sht "Columns")
"Item" 1))); 1 is column number
(vlax-put-property Rang "ColumnWidth" (vlax-make-variant 25.0 3)); 25.0 is new column width, 3 is variant type
(vlax-put-property Rang "Horizontalalignment" (vlax-make-variant -4108 3))
;;working with rows :
;;; (setq Rang (vlax-get-property Sht "Range" "A:A"));get first row
;; the same as :
(setq Rang (vlax-variant-value
(vlax-get-property (vlax-get-property Sht "Rows")
"Item" 1))); 1 is row number
(vlax-put-property Rang "RowHeight" (vlax-make-variant 18.0 3)); 25.0 is new row height, 3 is variant type
(vlax-put-property Rang "Horizontalalignment" (vlax-make-variant -4108 3))
(vlax-put-property (vlax-get-property Rang "Interior")
"Colorindex" (vlax-make-variant 4))
;;working with borders :
(setq Bord (vlax-get-property Rang "Borders"))
(vlax-put-property Bord "Color" (vlax-make-variant -1 3)) ; borders off
(vlax-put-property Bord "Color" (vlax-make-variant 1 3)) ;borders on
;; border lines (thin)
(vlax-put-property Bord "LineStyle" (vlax-make-variant 1 3))
;; borders color
(vlax-put-property Bord "Colorindex" (vlax-make-variant 5))
;;working with font :
(setq Fonto (vlax-get-property Rang "Font"))
(vlax-put-property Fonto "Name" (vlax-make-variant "Times New Roman" 12))
(vlax-put-property Fonto "Size" (vlax-make-variant 12 5))
(vlax-put-property Fonto "Bold" (vlax-make-variant 1 11))
(vlax-put-property Fonto "Italic" (vlax-make-variant 1 11))
(vlax-put-property Fonto "Colorindex" (vlax-make-variant 5));ETC

; continuing the code above
;; working with separate cell :
(vlax-invoke-method Rang "Activate");optional
(setq Cels (vlax-get-property Rang "Cells"))
(setq R1 1
C1 1)
(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))
;; Set interior color :
(vlax-put-property (vlax-get-property Cel "Interior")
"Colorindex" (vlax-make-variant 28))
;; select the particular cell:
(vlax-invoke-method Cel "Select")
;; get application selection:
(setq Sel (vlax-get-property ExcelApp "Selection"))
;; get selection borders
(setq Bords (vlax-get-property Sel "Borders"))
;; iterate through all edges of selection
(setq cnt 0)
(vlax-for a Bords
(setq cnt (1+ cnt))
(vl-catch-all-apply (function (lambda()
(progn
(if (< cnt 5)
(progn
(vlax-put-property a "LineStyle"
(vlax-make-variant 1 3))
(vlax-put-property a "Weight"
(vlax-make-variant 4 3))
(vlax-put-property a "ColorIndex"
(vlax-make-variant 5 5)));progn
;; turn off the diagonal lines:
(vlax-put-property a "LineStyle" (vlax-make-variant -4142 3))
))))))

;;; (vlax-make-variant 4 3)
;;; (setq a (vlax-get-property Bord "Item" 6))
;;; (vlax-put-property a "ColorIndex"
;;; (vlax-make-variant 20 5))
;;; (vlax-put-property Bord "Weight"(vlax-make-variant 4 3))
;;; "LineStyle")
;;; (vlax-make-variant -4142 3))
;;; Selection.Borders(xlDiagonalDown).LineStyle = xlNone '-4142 (<5)
;; Horizontal alignment Center :
(vlax-put-property Cel "Horizontalalignment" (vlax-make-variant -4108 3))
;; Vertical alignment Bottom :
(vlax-put-property Cel "VerticalAlignment" (vlax-make-variant -4107 3))
;; Set number format :
(vlax-put-property Cel "NumberFormat" (vlax-make-variant "0,00" 8))
(setq R1 1
C1 2)
(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))
;; get cell value :
(setq cval (vlax-variant-value (vlax-get-property Cel "Value")))
;; Horizontal alignment Left(Indent) :
(vlax-put-property Cel "Horizontalalignment" (vlax-make-variant -4131 3))
;; Vertical alignment Center :
(vlax-put-property Cel "VerticalAlignment" (vlax-make-variant -4108 3))
;; Set text format :
(vlax-put-property Cel "NumberFormat" (vlax-make-variant "@" 8))
;; ETC
;;; (vl-catch-all-apply
;;; 'vlax-invoke-method
;;; (list Wbk "Close")
;;; );close file w/o saving of changes

;; *** or if you need to save changes :

(vlax-invoke-method
Wbk
'SaveAs
(vlax-get-property wbk "Name");short name
-4143 ;exel file format (excel constant)
nil
nil
:vlax-false
:vlax-false
1
2
)
;;; (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()
(if (not (vlax-object-released-p x))

(vlax-release-object x)
)
)
))))
(list Bord Bords Cel Fonto lastCell lastRow FindRang Rang Sel Sht Wbk ExcelApp)
)
(setq Bord nil
Bords nil
Fonto nil
Cel nil
Sel nil
Rang nil
Sht nil
Wbk nil
ExcelApp nil
)
(gc)
(gc)
(princ)
)


to addition


; continuing the code above
;; working with separate cells :
(vlax-invoke-method Rang "Activate");optional
(setq Cels (vlax-get-property Rang "Cells"))
(setq R1 1
C1 1)
(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))
)
;; Horizontal alignment Center :
(vlax-put-property Cel "Horizontalalignment" (vlax-make-variant -4108 3))
;; Vertical alignment Bottom :
(vlax-put-property Cel "VerticalAlignment" (vlax-make-variant -4107 3))
;; Set number format :
(vlax-put-property Cel "NumberFormat" (vlax-make-variant "0,00" 8))
(setq R1 1
C1 2)
(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))
;; get cell value :
(setq cval (vlax-variant-value (vlax-get-property Cel "Value")))
;; Horizontal alignment Left(Indent) :
(vlax-put-property Cel "Horizontalalignment" (vlax-make-variant -4131 3))
;; Vertical alignment Center :
(vlax-put-property Cel "VerticalAlignment" (vlax-make-variant -4108 3))
;; Set text format :
(vlax-put-property Cel "NumberFormat" (vlax-make-variant "@" 8))
;; ETC


Here are some Excel constants
you can rich at them from Excel VBA Editor
===========================
Alignment Constants
===========================
Lisp | VBA
===========================
Vertical Alignment:
===========================
-4107 | XlVAlignBottom
-4108 | XlVAlignCenter
-4117 | XlVAlignDistributed
-4130 | XlVAlignJustify
-4160 | XlVAlignTop
===========================
Horizontal Alignment:
===========================
-4108 | XlHAlignCenter
7 | XlHAlignCenterAcrossSelection
-4117 | XlHAlignDistributed
5 | XlHAlignFill
1 | XlHAlignGeneral
-4130 | XlHAlignJustify
-4131 | XlHAlignLeft
-4152 | XlHAlignRight
===========================
Excel Sort Constants
===========================
1 | xlAscending (Order)
2 | xlDescending (Order)
1 | xlSortValues (Type)
2 | xlSortLabels (Type)
0 | xlGuess (Header)
1 | xlYes (Header)
2 | xlNo (Header)
1 | xlSortColumns (Orientation)
2 | xlSortRows (Orientation)
1 | xlTopToBottom (OrderCustom)
1 | xlPinYin (SortMethod)
2 | xlStroke (SortMethod)
0 | xlSortNormal (DataOption)
1 | xlSortTextAsNumbers (DataOption)
===========================
[/code]

~'J'~