pxl := COM_GetObject("Book1").Application array := pxl.Range("A1:A5").value(10) ; http://msdn.microsoft.com/en-us/library/bb241542.aspxNormally I just use the Excel copy method and get the data from the ClipBoard (and I know I could extract each cell value individually), but I was hoping to expand my horizons. I believe the returned "array" above is a safearray (forgive me if I'm wrong), which I've been told isn't easy to work with in AHK. I was hoping, however, someone could give me an example of how to extract the contents from this array? (and/or a 2D array if a range like "A1:E10" was used)
Get data from an Excel Array (safearray)
Started by
jethrow
, Dec 14 2009 05:03 AM
7 replies to this topic
What I'm trying to do is extract data from Excel using the following code (AHK_L & COM_L):
#1
-
Posted 14 December 2009 - 05:03 AM
Although you can do it all manually (:will be kind of a mess with AHK), if you have to ask for a help I suppose you're not prepared for that yet. I suggest to use dedicated APIs on SafeArray until grasping it.
<!-- m -->http://msdn.microsof...y/ms221145.aspx<!-- m -->
<!-- m -->http://msdn.microsof...y/ms221145.aspx<!-- m -->
#2
-
Posted 14 December 2009 - 07:03 AM
You're definately correct, considering I'm having trouble with the dedicated APIs - thanks for the reference by the way. I'm trying to create wrapper functions for these, but I'm continually hitting my scripting knowledge limits. SafeArrayDestroy, SafeArrayGetDim, & SafeArrayGetElemsize are easy, but could someone provide me an AHK example of using SafeArrayGetElement, or even SafeArrayGetLBound - I'm having trouble understanding (and then retrieving the value of) the plLbound param. Here's what I'm using to create the SafeArray:... I suppose you're not prepared for that yet. I suggest to use dedicated APIs on SafeArray until grasping it.
arr := COM_ScriptControl("arr = array(5,6,7,8)","arr") ... SafeArrayDestroy( arr ) ; wrapper functionAny guidance would be appreciated. Thanks .
#3
-
Posted 17 December 2009 - 05:51 PM
I think I forgot to mention one important thing: knowing SafeArray will not deepen your understanding about COM. You'll understand just one more data structure. Anyway, it may be a good thing to understand VB/COM Automation's unique data structures: Variant and SafeArray.
<!-- m -->http://www.autohotke...c34070-205.html<!-- m -->
VarSetCapacity(aidx, nDim*4, 0) ... ; Now fill the indices in aidx in [color=red]reverse[/color] order, i.e., from right to left! VarSetCapacity(var, 16, 0) ; for simplicity, will assume the data-type (:VarType) is Variant, which is usually so. DllCall("oleaut32\SafeArrayGetElement", "Uint", psa, "Uint", &aidx, "Uint", &var)Actually I've never used the SafeArray APIs, except for SafeArrayDestroy, so the above is not a tested one. BTW, here is an example to do it manually in 1-Dim case, so, you may compare them:
<!-- m -->http://www.autohotke...c34070-205.html<!-- m -->
#4
-
Posted 18 December 2009 - 12:17 AM
Thank you for the info Sean - it was very helpful . Here's what I came up with:
Example usage:
Here's an example of getting data from excel & breaking it up into a 2D Array (AHK_L & COM_L):
[color=darkred]SafeArrayDestroy[/color]( psa ) { ; same as in COM library Return, DllCall( "oleaut32\SafeArrayDestroy" , "Uint", psa ) } [color=darkred]SafeArrayGetDim[/color]( psa ) { Return, DllCall( "oleaut32\SafeArrayGetDim" , "Uint", psa ) } [color=darkred]SafeArrayGetLBound[/color]( psa, nDim=1 ) { VarSetCapacity( plLbound, 16, 0 ) DllCall( "oleaut32\SafeArrayGetLBound" , "Uint", psa , "Int", nDim , "Uint", &plLbound ) Return, NumGet( plLbound, 0 ) } [color=darkred]SafeArrayGetUBound[/color]( psa, nDim=1 ) { VarSetCapacity( plUbound, 16, 0 ) DllCall( "oleaut32\SafeArrayGetUBound" , "Uint", psa , "Int", nDim , "Uint", &plUbound ) Return, NumGet( plUbound, 0 ) } [color=darkred]SafeArrayGetDim[/color]( psa ) { Return, DllCall( "oleaut32\SafeArrayGetDim" , "Uint", psa ) } [color=darkred]SafeArrayGetElement[/color]( psa, indices ) { nDim := SafeArrayGetDim( psa ) Loop, Parse, indices, `, { If ( A_Index < nDim ) x += A_LoopField * A_Index ; calculate elements location - 1 y := A_LoopField ; calculate elements location - 2 } If ( nDim > 1 ) { z = 1 Loop, % nDim - 1 { ; calculate elements location - 3 lbound := (test:=SafeArrayGetLBound( psa, A_Index )) ? test : 0 ; test for lower bound z *= SafeArrayGetUBound( psa, A_Index ) - lbound + 1 } index := x+y*z } Else index := indices ; index formula (example for 3D safearray) - index1*1 + index2*2 +index3*( ++dim1 * ++dim2 ) ; element for 1D would simply be the index number pva := NumGet(psa+12) type := NumGet( pva+(Index) * 16, 0, "Ushort" ) If ( type = 2 ) Return, NumGet( pva+(Index+1) * 16 - 8, 0, "short" ) If ( type = 3 ) Return, NumGet( pva+(Index+1) * 16 - 8, 0, "int" ) If ( type = 4 ) Return, NumGet( pva+(Index+1) * 16 - 8, 0, "Float" ) If ( type = 5 ) Return, RegExReplace( NumGet( pva+(Index+1) * 16 - 8, 0, "Double" ),"\.?0*$" ) Else Return, COM_Ansi4Unicode( NumGet( pva+(Index+1) * 16 - 8 ) ) }For SafeArrayGetElement(), I used Sean's manual version rather than the API because I figured we'd need to identify the element location anyway to verify the data type. Or does anyone know a more effective way to do this. I am open to any suggestions/criticisms.
Example usage:
vb = ( dim arr(1,1,1) arr(0,0,0) = 45 ' 0 arr(1,0,0) = .023 ' 1 arr(0,1,0) = "23" ' 2 arr(1,1,0) = -"23.07865" ' 3 arr(0,0,1) = -23 ' 4 arr(1,0,1) = 12345678910 ' 5 arr(0,1,1) = "AutoHotKey" ' 6 arr(1,1,1) = -.0987 ' 7 ) psa := COM_ScriptControl(vb,"arr") MsgBox, % SafeArrayGetElement( psa, "1,1,0" ) SafeArrayDestroy( psa ) Return
Here's an example of getting data from excel & breaking it up into a 2D Array (AHK_L & COM_L):
SetBatchLines, -1 psa := COM_GetObject("Book1").ActiveSheet.UsedRange.value(10) Cells := SeparateSpreadSheet( psa ) MsgBox, % Cells[5, 50] ; [Column, Row] SafeArrayDestroy( psa ) Return [color=darkred]SeparateSpreadSheet[/color]( psa ) { Cells := object(), R := C := 1 Rows := SafeArrayGetUBound( psa, 1 ) Loop, % Rows * SafeArrayGetUBound( psa, 2 ) { Cells[C,R++] := GetElement( psa, A_Index-1 ) If ( R > Rows ) R := 1, C++ } Return, Cells } [color=darkred]GetElement[/color]( psa, Index ) { pva := NumGet(psa+12) type := NumGet( pva+(Index) * 16, 0, "Ushort" ) If type Not in 2,3,4,5 Return, COM_Ansi4Unicode( NumGet( pva+(Index+1) * 16 - 8 ) ) If type = 2 Return, NumGet( pva+(Index+1) * 16 - 8, 0, "short" ) If type = 3 Return, NumGet( pva+(Index+1) * 16 - 8, 0, "int" ) If type = 4 Return, NumGet( pva+(Index+1) * 16 - 8, 0, "Float" ) If type = 5 Return, RegExReplace( NumGet( pva+(Index+1) * 16 - 8, 0, "Double" ),"\.?0*$" ) }
#5
-
Posted 21 December 2009 - 08:38 AM
I suggest to look inside COM_Enumerate to handle different data types.
#6
-
Posted 21 December 2009 - 09:53 AM
I suggest to look inside COM_Enumerate to handle different data types.
Good call - thanks for the advice.
... yeah - it took me 3 years+ to figure this out ...
#7
-
Posted 03 July 2013 - 09:18 PM
Good call - thanks for the advice.
jethrow surely did not post this reply over 3 years later... this forum is going nuts!
#8
-
Posted 03 July 2013 - 10:31 PM
Rodolfo U. Batista
Pulover's Macro Creator - Automation Tool (Recorder & Script Writer) | Class_LV_Rows - Copy, Cut, Paste and Drag ListViews | Class_Toolbar - Create and modify | Class_Rebar - Adjustable GUI controls
Join the New AutoHotkey Forum!