Possible to reduce Excel command time?

zotune · 07 Oct 2015, 10:19

Logfiles with 10000 lines take almost 3 minutes

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 · 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 · 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?

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 · 07 Oct 2015, 15:24

Thanks, but when you add too many items you get an error: Continuation section too long.

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

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.

YouTube · 08 Oct 2015, 01:57

Union example

zotune · 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

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

THANKS

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

08 Oct 2015, 10:33

How can I pass both R1 and R2 in from a string?

How do you mean?

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

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

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

Possible to reduce Excel command time?

Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Re: Possible to reduce Excel command time?

Who is online