Excel: multi range

[Marc'Antonio Alessi]

(Visual Lisp A2K)

Question: How can I get a multi range in Excel?

ex.: row 2 from colon 5 to 9 or
column 4 from row 3 to 11

--

Purpose: apply a property to all the cells of the range
without loop in every cell.
(and without transform an alpha Excel column identifier
in integer)

--

Annotations:

in VBA I can use this syntax for range:

Range("A1") - Range("A1:H8") - Range("A:A") - Range("5:5")
Range("A:A;5:5") etc. > (A1 syntax)

but I can not use the R1C1 syntax,
I can use this form also:

Range(cell1, cell2) > Range(Cells(1, 1),Cells(10, 10))

with: Cells(n, n) in R1C1 syntax

--

Samples:

This function apply Autofit to UsedRange in one shoot:
(defun MSX_RangeAutoFit (ShtObj)
(vlax-invoke-method
(vlax-get-property (msxp-get-cells (vlax-get-property ShtObj
'UsedRange)) 'Columns)
'AutoFit
)
)

How can I do this in one shoot without repeat MSX_PutCellColor
on every cell in the range?
(defun MSX_PutRowCellsColor (RowNum SttCol ColQty ClrNum)
(repeat ColQty
(MSX_PutCellColor RowNum SttCol ClrNum)
(setq SttCol (1+ SttCol))
)
)

(defun MSX_PutCellColor (RowNum ColNum ClrNum / RngObj ItrObj)
(setq
RngObj (MSX_GetSheetCell RowNum ColNum)
ItrObj (msxp-get-Interior RngObj)
)
(msxp-put-colorindex ItrObj ClrNum)
(vlax-release-object RngObj) (vlax-release-object ItrObj)
)

(defun MSX_GetCell (ClsObj RowNum ColNum)
(vlax-variant-value
(msxp-get-Item ClsObj (vlax-make-variant RowNum) (vlax-make-variant
ColNum))
)
)

(defun MSX_GetSheetCell (RowNum ColNum)
(or *ActiveSht* (setq *ActiveSht* (msxp-get-ActiveSheet *ExcelApp*)))
(MSX_GetCell
(cond ( *ActiveCls* ) ( (setq *ActiveCls* (msxp-get-Cells
*ActiveSht*)) ))
RowNum ColNum
)
)
;without load all the typelib
(defun msxp-get-cells (ShtObj)
(vlax-get-property ShtObj 'Cells)
)
(defun msxp-get-Interior (RngObj)
(vlax-get-property RngObj 'Interior)
)

--

[Doug Broad]

I guess it depends on how you want to specify the range. You may find
more help on microsoft.public.excel.programming

a range object contains cells. You can format a range as well as individual
cells.
A selection object contains areas (which I believe are also ranges) and which
also contain cells.
Selections do not have to obey the rules for ranges (IOW they don't have to be
contiguous) but they can be used in some cases as ranges would.

I would suggest you prototype your Vlisp application in VBA inside the VBA
editor of Excel. Then you could translate the methods to vlax techniques or
use AutoCAD VBA. The help files are linked to the object browser and all
the methods and properties are in front of you.

The folks on that newsgroup are very adept, knowledgeable, and helpful.

HTH

"Marc'Antonio Alessi" <maalessi at tin dot it> wrote in message
news:8949FE6AA9D42CCD...@in.WebX.maYIadrTaRb...

[Marc'Antonio Alessi]

Thanks Doug,

but the question is quite simple and it is for Autolispers:

Example to put the color 3 in range B5:J5, in VB I have:

Worksheets(1).Range("B5:J5").Interior.ColorIndex = 3

--

in Visual Lisp:

(vlax-put-property
(vlax-get-property
(vlax-get-property *ActiveSht* 'Range (vlax-make-variant "B5:J5"))
'Interior
)
'ColorIndex 3
)

--

Now I have the same instructions in different form:

Worksheets(1).Range(Cells(5, 2), Cells(5, 10)).Interior.ColorIndex = 3

How can I translate this form in Visual Lisp?

------------------------------------------------------------
For clarity:
(setq
*ExcelApp* (vlax-get-or-create-object "Excel.Application")
*ActiveWbk* (vlax-invoke-method
(vlax-get-property *ExcelApp* 'WorkBooks)
'Open "test.xls"
)
*ActiveSht* (vlax-get-property *ExcelApp* 'ActiveSheet)
*ActiveCls* (vlax-get-property *ActiveSht* 'Cells)
)

P.S.: maybe if John wrote the below functions there is
not a different solution to use R1C1 form in range:
;;----------------------------------------------------
From: John Uhden
Date: Jan/16/02 - 15:29 (GMT)
Try these little functions. Having restricted the
number of characters to two, the results start looking
ugly after (702="ZZ").
;;----------------------------------------------------
;; Function to return an alpha Excel column identifier
;; given an integer starting from 1
;; Examples: 1="A" 26="Z" 27="AA" 52="AZ" 53="BA" 256="IV"
(defun @column$ (n / i)
(setq i (/ (1- n) 26))
(if (= i 0)
(chr (+ 65 (rem (1- n) 26)))
(strcat (chr (+ 64 i))(chr (+ 65 (rem (1- n) 26))))
)
)
;;----------------------------------------------------
;; Function to return an integer starting at 1
;; given an alpha Excel column identifier
;; Examples: "A"=1 "Z"=26 "AA"=27 "AZ"=52 "BA"=53 "IV"=256
(defun @column# (str / i)
(setq str (strcase str)
i (+ (ascii (substr str 1 1)) -64)
)
(if (> (strlen str) 1)
(+ (* 26 i)(ascii (substr str 2 1)) -64)
i
)
)

[Doug Broad]

Hi Marco,
The question might be simple but the implementation is anything but simple.
If I were involved in doing anything substantial with linking AutoCAD and
Excel, then I would choose the common language that both share: VBA, rather
than LISP.

To demonstrate the cells methodology, I include:

(vl-load-com)

;;open and set up an excel spreadsheet.
(setq xl (vlax-get-or-create-object "Excel.Application") ;the application
wbs (vlax-get-property xl 'WorkBooks) ;the workbooks collection
wb (vlax-invoke wbs 'open "test.xls") ;use add if it doesn't exist (the workbook)
ws (vlax-get-property wb 'activesheet) ;the active sheet
wcs (vlax-get-property ws 'cells) ;the cells in the sheet
c1 (variant-value (vlax-get-property wcs 'item 5 2)) ;cell R5C2
c2 (variant-value (vlax-get-property wcs 'item 5 10));cell R5C10
rng (vlax-get-property ws 'range c1 c2) ;the range given those two corners
addr (vlax-get-property rng 'address :vlax-true :vlax-true 1) ;the address in A1
format of that range
)

;;Change the interior color of the range
(vlax-put-property
(vlax-get-property rng 'interior)
'colorindex
3)

;;Close the workbook, release the objects and take out the garbage
(defun closeXL ()
(vlax-invoke wb "close" -1) ;;-1 is true in Excel. :vlax-true won't work
(mapcar 'vlax-release-object
(list c1 c2 rng wcs ws wb wbs xl))
(gc)
(princ))

The equivalent to the above, written in VBA would be very short, somethinge like:
(no error testing or dim statements)

Sub test()
Set wb = Workbooks.Open("test.xls")
Set ws = wb.ActiveSheet
ws.Range(Cells(5, 2), Cells(5, 10)).Interior.ColorIndex = 3
wb.Close True
End Sub

Given a choice between the two (VBA vs LISP), I would prefer the simplest approach.

HTH

Regards,
Doug

"Marc'Antonio Alessi" <maalessi at tin dot it> wrote in message

news:0048830CA70C0C3F...@in.WebX.maYIadrTaRb...

[Marc'Antonio Alessi]

Grazie mille, that is what I was looking for.

In particular:

> c1 (variant-value (vlax-get-property wcs 'item 5 2)) ;cell R5C2
> c2 (variant-value (vlax-get-property wcs 'item 5 10));cell R5C10
> rng (vlax-get-property ws 'range c1 c2) ;the range given those two corners

%%

Thanks again for your time.

Marco

--

P.S.:

> (vlax-invoke wb "close" -1) ;;-1 is true in Excel. :vlax-true won't work

I use this:

; MSX_Quit
; Description: Quit and close Excel session (ExlObj)
; Args: ExlObj (session object) SaveYN > T = prompt to save - nil = exit
without saving
; Example: (MSX_Quit *ExcelApp* T)
;
(defun MSX_Quit (ExlObj SaveYN)
(or
(vlax-object-released-p ExlObj)
(progn
(or
(not *ActiveCls*)
(vlax-object-released-p *ActiveCls*)
(vlax-release-object *ActiveCls*)
)
(or
(not *ActiveSht*)
(vlax-object-released-p *ActiveSht*)
(vlax-release-object *ActiveSht*)
)
(cond
( (vlax-object-released-p *ActiveWbk*) (vlax-invoke-method ExlObj
'QUIT) )
( *ActiveWbk*
(if SaveYN
(vlax-invoke-method *ActiveWbk* 'Close)
(vlax-invoke-method *ActiveWbk* 'Close :vlax-False)
)
(vlax-release-object *ActiveWbk*)
)
)
(vlax-release-object ExlObj)
(setq *ExcelApp* nil *ActiveWbk* nil *ActiveSht* nil *ActiveCls*
nil)
(gc)(gc)
)
)
)

and it works!

[Doug Broad]

Marco,
Enjoyed it. Glad to help. Keep writing Italian. I'd
like to remember what I once knew.

Doug

"Marc'Antonio Alessi" <maalessi at tin dot it> wrote in message

news:93B5C7587730A405...@in.WebX.maYIadrTaRb...