Possible to reduce Excel command time?

Get help with using AutoHotkey and its commands and hotkeys
zotune
Posts: 83
Joined: 17 Nov 2014, 17:57

Possible to reduce Excel command time?

07 Oct 2015, 10:19

Logfiles with 10000 lines take almost 3 minutes

Code: Select all

xl := ComObjCreate("Excel.Application")
xl.WorkBooks.Add
xl.Visible := false

StartTime := A_TickCount
Loop, 100
{
	xl.Range("A" A_Index).Interior.Color := 6015990
}
ElapsedTime := A_TickCount - StartTime
MsgBox,  %ElapsedTime% milliseconds have elapsed.
xl.Visible := true
exitapp
Appreciate any help!
zo
Gicu
Posts: 111
Joined: 19 Aug 2014, 08:19
Location: Italy

Re: Possible to reduce Excel command time?

07 Oct 2015, 10:52

Code: Select all

Loop, 10000
your code with my cpu (i54460)
"16224 milliseconds have elapsed."

your code +

Code: Select all

SetBatchLines, -1
"8174 milliseconds have elapsed."

without loop:

Code: Select all

xl.Range("A1:A10000").Interior.Color := 6015990
"15 milliseconds have elapsed."
zotune
Posts: 83
Joined: 17 Nov 2014, 17:57

Re: Possible to reduce Excel command time?

07 Oct 2015, 12:43

Thanks, I figured someone would give that last example. I'm aware of that, but I color log items based on its contents, so I can't really do a range for most things. I need to do individual colors for a lot of items.
Is there no faster way to do this?
tmplinshi
Posts: 1600
Joined: 01 Oct 2013, 14:57

Re: Possible to reduce Excel command time?

07 Oct 2015, 15:02

A clipboard solution:

Code: Select all

SetBatchLines -1

#Include <WinClipAPI> ; http://www.autohotkey.com/board/topic/74670-class-winclip-direct-clipboard-manipulations/
#Include <WinClip>

html =
(
<html>
   <body>
      <table CellSpacing=0>
         <tr>
            <td bgcolor="#808080">11</td>
            <td bgcolor="#008000">12</td>
         </tr>
         <tr>
            <td bgcolor="#FF0000">21</td>
            <td bgcolor="#993366">22</td>
         </tr>
      </table>
   </body>
</html>
)
WinClip.Clear()
WinClip.SetHTML( html )

xl := ComObjCreate("Excel.Application")
xl.Visible := true
xl.WorkBooks.Add
xl.Range("A1").PasteSpecial()
zotune
Posts: 83
Joined: 17 Nov 2014, 17:57

Re: Possible to reduce Excel command time?

07 Oct 2015, 15:24

Thanks, but when you add too many items you get an error: Continuation section too long.
tmplinshi
Posts: 1600
Joined: 01 Oct 2013, 14:57

Re: Possible to reduce Excel command time?

07 Oct 2015, 19:48

Save html code to file, then use FileRead.

Or use several vars, then combine them:

Code: Select all

part1 =
(
...
)
part2 =
(
...
)
html := part1 . part2
wolf_II
Posts: 2688
Joined: 08 Feb 2015, 20:55

Re: Possible to reduce Excel command time?

07 Oct 2015, 20:08

If you have set your macro security to allow access to the VB project object model, you could write your code in VBA (.bas-file) and import that dynamically.

Code: Select all



;-------------------------------------------------------------------------------
Module = ; VBA code
;-------------------------------------------------------------------------------
(
Sub Test()
    StartTime = Timer
    For i = 1 To 65536
        Range("A" & i).Interior.Color = 6015990
    Next i
    MsgBox Timer - StartTime & " seconds have elapsed."
End Sub

)



f := FileOpen(FileName := A_ScriptDir "\Test.bas", "w")
f.Write(Module)
f.Close

oXL := ComObjCreate("Excel.Application")
oXL.Visible := False
oXL.WorkBooks.Add
oXL.VBE.ActiveVBProject.VBComponents.Import(FileName)
oXL.Run("Test")
oXL.Visible := True
ExitApp
Tested with Excel 2003: 65,536 rows (max) --> ca. 2 seconds.
User avatar
sinkfaze
Posts: 614
Joined: 01 Oct 2013, 08:01

Re: Possible to reduce Excel command time?

08 Oct 2015, 00:10

You can still essentially use Gicu's final solution, just test all of the cells in the range, create a union(s) of the cells that need to be colored certain colors and execute the coloring once all of the cells have been tested.
User avatar
Blackholyman
Posts: 1292
Joined: 29 Sep 2013, 22:57
Facebook: socialjsz
Google: +Jszapp
Location: Denmark
Contact:

Re: Possible to reduce Excel command time?

08 Oct 2015, 01:57

Union example
Also check out:
Courses on AutoHotkey

My Autohotkey Blog
:dance:
zotune
Posts: 83
Joined: 17 Nov 2014, 17:57

Re: Possible to reduce Excel command time?

08 Oct 2015, 07:57

Appreciate the help. Although is it possible to do multiple ranges like in VBA?
Then I could change the items with the same color simultaneously, that could save a bunch of time:
Range("A1:A2,B3:C4").Value = 10
User avatar
sinkfaze
Posts: 614
Joined: 01 Oct 2013, 08:01

Re: Possible to reduce Excel command time?

08 Oct 2015, 08:25

A union is multiple ranges, treated as if they are a single range, just like your code but simpler (IMO).

Also, note the post below the one Blackholyman linked to:
I typically used comma-separated ranges - xlSheet.Range("A1:A1,A5:A5"), but that only works for up to 34 separate ranges. Using the Union method, I just successfully assigned 60K ranges with no problem
zotune
Posts: 83
Joined: 17 Nov 2014, 17:57

Re: Possible to reduce Excel command time?

08 Oct 2015, 08:31

Okay, thanks. Trying to see if I understand unions.

r1:=xl.ActiveSheet.Range("F6:F6").Select
r2:=xl.ActiveSheet.Range("F7:F7").Select
myMultipleRange := xl.Union(r1, r2)
myMultipleRange.Font.Bold := True

Doesn't work atm.

Edit:
r1:=xl.Range("F6:F6")
r2:=xl.Range("F7:F7")
xl.Union(r1, r2).Font.Bold := True

WORKS :D THANKS
Last edited by zotune on 08 Oct 2015, 08:40, edited 1 time in total.
User avatar
sinkfaze
Posts: 614
Joined: 01 Oct 2013, 08:01

Re: Possible to reduce Excel command time?

08 Oct 2015, 08:37

Make sure you're following Blackholyman's example, the first range is saved directly to var, any subsequent ranges are added to the var via union:

Code: Select all

r1 := xl.ActiveSheet.Range("F6")
r2 := xl.ActiveSheet.Range("F7")
myMultipleRange := r1
myMultipleRange := xl.Union(myMultipleRange,r2) ; <~ note that Union must be called from the application pointer (xl)
zotune
Posts: 83
Joined: 17 Nov 2014, 17:57

Re: Possible to reduce Excel command time?

08 Oct 2015, 09:37

Code: Select all

r1:=xl.Range("F6")
r2:=xl.Range("G7")
combine:=r1 , r2
xl.Union[combine].Font.Bold := True
Thanks. How can I pass both R1 and R2 in from a string? I can't type them manually into xl.Union[R1,R2], that defeats the whole purpose.
User avatar
sinkfaze
Posts: 614
Joined: 01 Oct 2013, 08:01

Re: Possible to reduce Excel command time?

08 Oct 2015, 10:33

How can I pass both R1 and R2 in from a string?
How do you mean?
zotune
Posts: 83
Joined: 17 Nov 2014, 17:57

Re: Possible to reduce Excel command time?

08 Oct 2015, 10:37

I don't know how to combine R1, R2 into one line based on my findings.
Higher in the code I have something like

Code: Select all

if (bla bla)
{
Range := xl.Range["A" Row] ","
}

Range:=Trim(Range,",")

xl.Union[Range].Font.Bold := True
this gives an error

if it's not on one line then I won't see any time savings, and I don't know how to insert multiple objects into one range.

tldr:
R1 and R2 needs to be combined higher in the code, but I don't have any idea how to do this.
User avatar
sinkfaze
Posts: 614
Joined: 01 Oct 2013, 08:01

Re: Possible to reduce Excel command time?

08 Oct 2015, 13:59

Union is a function. When you use the Union function, you are telling it that you want to concatenate pointers to two ranges, which are represented by the two parameters of the function. It is not one parameter with the literal text of the ranges. The comma separates the parameters of the function. You only need to use Union to concatenate the pointers, nothing else.

If you're looking to concatenate just two pointers that have been saved to vars r1 and r2:

Code: Select all

r1 :=	xl.Range("F6")
r2 :=	xl.Range("G7")
Range :=	xl.Union[r1,r2]
Range.Font.Bold :=	True
The variable Range now contains a pointer to both ranges. It is functionally the equivalent of doing this:

xl.Range["F6,G7"]

The difference is that Range will only accept 34 comma separated ranges (as noted above). I don't know Union's limitations, but if there is one it's beyond 60,000 pointers, which is more than most people would ever need.

You can also continually concatenate pointers to one variable:

Code: Select all

Range :=	xl.Range("F6")
Range :=	xl.Union[Range,xl.Range("G7")]
Range :=	xl.Union[Range,xl.Range("H8")]
Range.Font.Bold :=	True ; Range contains pointers to cells F6, G7 and H8
Does this make more sense?
zotune
Posts: 83
Joined: 17 Nov 2014, 17:57

Re: Possible to reduce Excel command time?

09 Oct 2015, 04:59

Yes, thank you so much. That works wonderfully.
However, it's just as slow as using it without union. If not slower. Because of all the Range function calls.

Edit:
It's even slower, 2 minutes and going, I've hardly done any coloring.
User avatar
Blackholyman
Posts: 1292
Joined: 29 Sep 2013, 22:57
Facebook: socialjsz
Google: +Jszapp
Location: Denmark
Contact:

Re: Possible to reduce Excel command time?

09 Oct 2015, 05:38

this example

Code: Select all

SetBatchLines -1
xl := ComObjCreate("Excel.Application")
xl.WorkBooks.Add
xl.Visible := false
 
StartTime := A_TickCount
Loop, 100000
{
	;~ ToolTip %A_Index%
	if uni
		uni := xl.Union[uni, xl.Range("A" A_Index)]
	else
		uni := xl.Range("A" A_Index)
}
;~ ToolTip
uni.Interior.Color := 6015990
ElapsedTime := A_TickCount - StartTime
MsgBox,  %ElapsedTime% milliseconds have elapsed.
xl.Visible := true
exitapp
said 76137 so that's under a 1 minute 20 seconds so under a millisecond per range is that not quick? Please post an example of the code that you are using to test with.
Also check out:
Courses on AutoHotkey

My Autohotkey Blog
:dance:

Return to “Ask For Help”

Who is online

Users browsing this forum: gioka, Hackerxd, Melchisedek, mikeyww and 60 guests