passing an array of values to an Excel range with vlisp

[petersciganek]

I am trying to pass a single dimension safe-array to a range in Excel. For some reason – the range only accepts the first atom of the array. This is how the code excecutes:

(setq range_object (vlax-get-property worksheet_object 'Range “A1:A6”))

(vlax-put-property range_object 'Value2 safe_array)

The safe array is correctly typed and does contain separate values.

I am currently passing each value individually to a cell – and am hoping to speed up the transfer of data by passing arrays. What am I doing wrong?

Thanks for any help,

Peter

 

[Marc'Antonio Alessi]

search: autodesk.autocad.customization

Excel Quit: new behaviour - Excel: multi range - Excel quit

 

[petersciganek]

Marc'Antonio,

Thank you for replying,

In the examples that you posted it seems that you pass a single value to the range. In the examples that I have found in this newsgroup, the values are passed individually to each cell. Have you also been able to pass arrays to a range? This should be possible – this from Microsoft support:

http://support.microsoft.com/default.aspx?scid=kb;en-us;153090

Private Sub Command1_Click
        Dim o As Object         Dim i As Integer         Dim iNumbers(1 To 10) As Integer         For i = LBound(iNumbers) To UBound(iNumbers)           iNumbers(i) = Int(Rnd * 100) + 1         Next i         Set o = CreateObject("excel.application")         o.Visible = True         o.Workbooks.Add         o.sheets("sheet1").Range("A1:J1").Value = iNumbers  End Sub

I have tried passing the list as a safearray, as a variant and with the atoms both as their native types and as variants. In each case the entire range is filled with the first atom of the list. I use the Value2 property of the Range because I get a “no description available” error on (vlax-put-property range_object ‘Value *array*) – though I can return a safearray from the range with (vlax-get-property range_object ‘Value).

My code prepares a list such as this one: (("A1:A6" . #<variant 8204 ...&gt ("B1:B6" . #<variant 8204 ...&gt ("C1:C6" . #<variant 8204 ...&gt ("D16" . #<variant 8204 ...&gt ("E1:E6" . #<variant 8204 ...&gt ("F1:F6" . #<variant 8204 ...&gt ("G1:G6" . #<variant 8204 ...&gt)

-then iterates through the list – retrieving the range object from the car atom and then attempting to pass the cdr to the range. I hope that this method will speed up the whole process. It seems to take a long time to fill the cells individually – though iterating through the cells collection of the range object does seem to go a little faster.

Any ideas?

Peter

 

[Mark (archive)]

i am passing a two dimensional safe-array, and works fine, this is a snipset from my code... using roperties-prefix "xlp-" xlas = the active sheet object

(setq mmls '((5.5 4 33 "O")(nil 6.6 44 "K")(7 nil 7.7 "MM"))) (xlp-put-value (xlp-get-range xlas "A13") (vlax-safearray-fill (vlax-make-safearray vlax-vbVariant (cons 0 (1- (length mmls))) (cons 0 (1- (length (car mmls))))) (mapcar '(lambda (itm) (mapcar 'vlax-make-variant itm)) mmls)))

HTH

I am trying to pass a single dimension safe-array to a range in Excel. For some reason - the range

only accepts the first atom of the array. This is how the code excecutes:

(setq range_object (vlax-get-property worksheet_object 'Range "A1:A6"))

(vlax-put-property range_object 'Value2 safe_array)

The safe array is correctly typed and does contain separate values.

I am currently passing each value individually to a cell - and am hoping to speed up the transfer

of data by passing arrays. What am I doing wrong?

 

[Marc'Antonio Alessi]

Reading Mark post:

;
; ALEax_Make2DimSafeArray (original by Mark)
; Argument: a list
; Examples:
; (ALEax_Make2DimSafeArray '((5.5 4 33 "O")(nil 6.6 44 "K")(7 nil 7.7 "MM"))) ; (defun ALEax_Make2DimSafeArray (ImpLst) (vlax-safearray-fill (vlax-make-safearray vlax-vbVariant (cons 0 (1- (length ImpLst))) (cons 0 (1- (length (car ImpLst)))) ) (mapcar '(lambda (x) (mapcar 'vlax-make-variant x)) ImpLst) ) ) (defun C:XlNew (vl-load-com) (setq
*ExcelApp* (vlax-get-or-create-object "Excel.Application")
*ActiveWbk*
(vlax-invoke-method
(vlax-get-property *ExcelApp* 'WorkBooks) 'Add
)
*ActiveSht* (vlax-get-property *ExcelApp* 'ActiveSheet)
*ActiveCls* (vlax-get-property *ActiveSht* 'Cells)
)
(vla-put-visible *ExcelApp* 1)
(setq
RngObj
(vlax-get-property
*ActiveSht* 'Range
"A13"
)
)
(vlax-put-property RngObj 'Value2
(ALEax_Make2DimSafeArray
'((5.5 4 33 "O")(nil 6.6 44 "K")(7 nil 7.7 "MM"))
)
)
)
(defun C:XlClose
(vlax-release-object RngObj) ;<<
(vlax-release-object *ActiveCls*)
(vlax-release-object *ActiveSht*)
(vlax-invoke-method *ActiveWbk* 'Close 0)
(vlax-release-object *ActiveWbk*)
(vlax-invoke-method *ExcelApp* 'QUIT)
(vlax-release-object *ExcelApp*)
(setq
*ExcelApp* nil *ActiveWbk* nil *ActiveSht* nil *ActiveCls* nil RngObj nil ) (gc)
)

 

[Mark (archive)]

hi Marco,

any luck on the nested rectangles...

very much interested to know what

algorithm others are using.

Mark

BTW

Peter also should know that

the list and the range string should

be in sync, othewise the result will

be incorrect, i.e.

"A13" and

'((A1 B1 C1 D1)(A2 B2 C2 D2)(A3 B3 C3 D3))

or

"A1:A5" and

'((A1)( A2 )(A3)( A4 )(A5))

 

[Marc'Antonio Alessi]

Hi Mark,

no news, I have given a glance to

www.techno-sys.com www.nirvanatec.com

but I have not tried any more.

Cheers.

Marco

 

[pupito]

Hi Peter:
I'm trying to do the opposite which is to save an entire range value to a safearray. I'm getting the first record only instead. Have you figured it out yet? I get the range definition from predefined names in the Excel file. Then I passed that name to vlax-get-property obj 'Range rangename). I get a variant which contains only the first record. How can I get the whole thing?

 

[pupito]

Peter, thank you for your prompt response! How about this idea: Since I already know the beginning and end of the range, I could create a loop to then extract each record range at once (ex.: A1:U1) and loop until the last record. Since Excel lets us at least get a full record as a variant you could then fill the safearray by passing each record variant to the safearray. Would that not be faster than exporting to text files? At least you don't have to go cell by cell, what do you think? Would that work?