Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Get data from an Excel Array (safearray)


  • Please log in to reply
7 replies to this topic
jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
What I'm trying to do is extract data from Excel using the following code (AHK_L & COM_L):
pxl := COM_GetObject("Book1").Application

array := pxl.Range("A1:A5").value(10) ; http://msdn.microsoft.com/en-us/library/bb241542.aspx
Normally 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)

Sean
  • Members
  • 2462 posts
  • Last active: Feb 07 2012 04:00 AM
  • Joined: 12 Feb 2007
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 -->

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009

... I suppose you're not prepared for that yet. I suggest to use dedicated APIs on SafeArray until grasping it.

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:
arr := COM_ScriptControl("arr = array(5,6,7,8)","arr")
...
SafeArrayDestroy( arr ) ; wrapper function
Any guidance would be appreciated. Thanks :) .

Sean
  • Members
  • 2462 posts
  • Last active: Feb 07 2012 04:00 AM
  • Joined: 12 Feb 2007
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.
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 -->

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
Thank you for the info Sean - it was very helpful :D . Here's what I came up with:
[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*$" )
}


Sean
  • Members
  • 2462 posts
  • Last active: Feb 07 2012 04:00 AM
  • Joined: 12 Feb 2007
I suggest to look inside COM_Enumerate to handle different data types.

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009

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 ...



Pulover
  • Members
  • 1596 posts
  • Last active: Apr 06 2016 04:00 AM
  • Joined: 20 Apr 2012

Good call - thanks for the advice.


jethrow surely did not post this reply over 3 years later... this forum is going nuts! confused.png


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!