Jump to content

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

[solved] COM, Excel, Range().Value


  • Please log in to reply
13 replies to this topic
Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009
Task: get values from Range("A1:A500")

Current solutions - not fast enough:
q:: 	; slow
Values =
oExcel := ComObjActive("Excel.Application")
For k in oExcel.Range("A1:A500")
Values .= k.Value ", "
MsgBox, % Values
return
	
w::		; very slow
Values = 
oExcel := ComObjActive("Excel.Application")
Loop, 500
Values .= oExcel.Range("A" A_Index).Value ", "
MsgBox, % Values
Values =
return
Is there any faster way? After some research, I think the answer is no, but I need confirmation.

HotKeyIt
  • Moderators
  • 7439 posts
  • Last active: Jun 22 2016 09:14 PM
  • Joined: 18 Jun 2008
Sure there is :)
SetBatchLines,-1
Also when excel sheet is not active it takes ~2/3 of the time.
~300 ms on my machine.

Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009

SetBatchLines,-1

He he, I know that :)
~300ms for 500 cells is fine, but I was thinking if there is some solution that can do that task even faster...

For example, in MS Word there is a very fast way to get all text from document;
oWord := ComObjActive("Word.Application")
text := oWord.ActiveDocument.Range.text
In MS Excel, I don't see analog/similar solution. (Ok, I don't need values from whole workbook - analog to MS word doc, but for some big range.)

It looks that looping
For k in oExcel.Range("A1:A500")
is the fastest solution. Of course with boosters like SetBatchLines,-1 :wink:

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008

After some research, I think the answer is no...


I would beg to differ:

xl :=	ComObjActive("Excel.Application")
xl.Range("A1:A500").Copy
StringReplace, Clipboard, Clipboard, `r`n, `,%A_Space%, All
MsgBox %	Clipboard

I'm benchmarking on my machine at ~50ms with Excel as the active window.

Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009
True, that's rocket fast, but I forgot to mention that I must not use the clipboard --> my mistake.
Anyway, HotKeyIt and sinkfaze, thanks for your input. :)

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

... I forgot to mention that I must not use the clipboard

Why?
xlApp := [color=#107095]ComObjActive[/color]([color=#666666]"Excel.Application"[/color])

clip := [color=brown]ClipboardAll[/color]
xlApp.Range([color=#666666]"A1:A100"[/color]).copy
[color=#107095]StringReplace[/color], Values, [color=brown]Clipboard[/color], `r`n, `,%[color=brown]A_Space[/color]%, All
[color=brown]Clipboard[/color] := clip
[color=#107095]MsgBox[/color], %Values%

However, all-ya-all are missin the true greatness of Lexikos implementing SafeArray support:
xlApp := [color=#107095]ComObjActive[/color]([color=#666666]"Excel.Application"[/color])

SArr := xlApp.Range([color=#666666]"A1:A100"[/color]).value
[color=#107095]Loop[/color], % SArr.MaxIndex(1)
	Values .= SArr[[color=brown]A_Index[/color], 1] [color=#666666]", "[/color] ; <-- Pulls Numbers as a Double (1 = 1.000000)
[color=#107095]MsgBox[/color], %Values%
If you want to trim the trailing zeros & decimal, here's a regex:
Values .= [color=#107095]RegExReplace[/color](SArr[[color=brown]A_Index[/color], 1], [color=#666666]"\.(0*|\d*?\K0*)$"[/color]) [color=#666666]", "[/color]


sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
I had a sneaking suspicion that the recently added SafeArray support would probably make for a faster solution but I had no idea how to implement it. How would you reference a multidimensional area like "A1:AN40" through SafeArray?

If you want to trim the trailing zeros & decimal, here's a regex...


Why all that extra work? :wink:

Values .= [color=red]Round([/color]SArr[A_Index, 1][color=red])[/color] ", "

EDIT: I happened to figure it out a way to parse a range using your method, but you might know of a better one:

xl :=	ComObjActive("Excel.Application")
sArr :=	xl.Range("A1:AN40").value
Loop, [color=red]40[/color] {
[color=red]i :=	A_Index[/color]
Loop, %	SArr.MaxIndex(1)
	Values .= (!Values ? "" : ", ") Round(SArr[A_Index, [color=red]i[/color]])
}


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

How would you reference a multidimensional area like "A1:AN40" through SafeArray?

[*:19wmcv56]SafeArray[row#,column#]
[*:19wmcv56]when you extract the SafeArray, it is 1-based (as opposed to zero-based)[*:19wmcv56](answer to future question) when building a SafeArray to assign to an Excel Range Value (via ComObjArray), it will be zero-based (SArr[0,0] through SArr[99,0])[*:19wmcv56]row# & column# are based on the Range (2D Array) - not the actual worksheet row/col[*:19wmcv56]SafeArray.MaxIndex(dimension)

Why all that extra work?

Because the Double for 1.1 is 1.100000 :wink:

Learning one
  • Members
  • 1483 posts
  • Last active: Jan 02 2016 02:30 PM
  • Joined: 04 Apr 2009

SafeArray

That's it! :D:D:D Thank you! Solved!

Parsing multidimensional area example:
q::
xlApp := ComObjActive("Excel.Application")

SArr := xlApp.Range("A1:C10").value

;MsgBox % SArr.MaxIndex(1)	; total rows
;MsgBox % SArr.MaxIndex(2)	; total columns

Loop % SArr.MaxIndex(1)
{
	CurRowNum := A_Index
	Loop % SArr.MaxIndex(2)
	MsgBox % SArr[CurRowNum, A_Index]
}
return


LordOfTheFiles
  • Members
  • 14 posts
  • Last active: Nov 09 2011 04:48 PM
  • Joined: 28 Oct 2008

After some research, I think the answer is no...


I would beg to differ:

xl :=	ComObjActive("Excel.Application")
xl.Range("A1:A500").Copy
StringReplace, Clipboard, Clipboard, `r`n, `,%A_Space%, All
MsgBox %	Clipboard

I'm benchmarking on my machine at ~50ms with Excel as the active window.


Sink, thanks for this clipboard solution script; it is what I needed. However, when I try to run my script in AHK within the COM framework, I get this error:

"The following variable name contains an illegal character: ".Copy"".


My script:

#SingleInstance force
#Include %A_ScriptDir%
#Include %A_ScriptDir%\Com.ahk

/*
Information: A function to retrieve cell contents from Ms Excel to the clipboard.

*/

COM_Init()
WinWt("Microsoft Excel")


xl :=   ComObjActive("Excel.Application")
xl.Range("A2:C10").Copy
StringReplace, Clipboard, Clipboard, `r`n, `,%A_Space%, All
MsgBox %   Clipboard




Any help is appreciated. I know I'm missing something, and admit I'm still a COM novice. I'm running AHK standard Version 1.0.48.05 on WinXP. If I need to be running AHK_L to make this work properly, I'll switch for that reason alone if I must. Thanks in advance.
Better a diamond with a flaw than a pebble without.

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008

I know I'm missing something...I'm running AHK standard Version 1.0.48.05...


That is indeed the problem, the solutions shown earlier in this thread are all using the newer AHK_L. For you using the "basic" version the solution must be slightly different. Try this:

#SingleInstance force
#Include %A_ScriptDir%
#Include %A_ScriptDir%\Com.ahk

/*
Information: A function to retrieve cell contents from Ms Excel to the clipboard.

*/

COM_CoInitialize()
WinWt("Microsoft Excel")


xl :=   [color=red]COM_GetActiveObject([/color]"Excel.Application"[color=red])[/color]
[color=red]COM_Invoke(xl,"Range("A2:C10").Copy")
COM_Release(xl), COM_CoUninitialize()[/color]
StringReplace, Clipboard, Clipboard, `r`n, `,%A_Space%, All
MsgBox %   Clipboard


jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
COM_Invoke(xl,"Range[A2].Copy")
:p

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
D'OH! Knew I'd forget more syntax conversions. :oops:

LordOfTheFiles
  • Members
  • 14 posts
  • Last active: Nov 09 2011 04:48 PM
  • Joined: 28 Oct 2008
Thanks guys. What a great community!
Better a diamond with a flaw than a pebble without.