Trouble timing actions

[PeterlFF]

I have my code figured out for marking the beginning and end of task but I am having trouble getting the end time to trigger at the correct time. Right now I have code to run some macros in Excel and I want to wait until the macro completes before marking the end time. I am sending keyboard shortcuts to Excel to run the macros. The code works to run the macros. I thought the script would wait until the first macro finished running before moving on and it appears that way. But when I add my time stamp code it sets the end time before the macro has finished running.

I tried moving the end time code to after the Send("!{F8}") line for the second macro but it still doesn't wait for the first macro to end.

Any ideas?

expandcollapse popup

; Get ready for first macro
WinWaitActive("Excel_Workbook.xlsm - Excel")
Send("!{F8}")
WinWaitActive("Macro")

; start time for first macro.
Local $hTimer = TimerInit() 
Local $Hr1 = Int(Number(@HOUR))
Local $Min1 = Int(Number(@MIN))
Local $Sec1 = Int(Number(@SEC))
Local $MSc1 = Int(Number(@MSEC)/100)
$T1t1 = $Hr1 & ":" & $Min1 & ":" & $Sec1 & ":" & $MSc1
ConsoleWrite($T1t1 & @CRLF)

; Run first macro
Send("!R")

; end time for first macro.
Local $iTime = TimerDiff($hTimer) ; Find the difference in time from the previous call of TimerInit.
$pTime = $hTimer + $iTime ; Add TimerInit TimerDiff results to generate End time.
$hour = Floor($iTime / 3600000)
$remanH = Mod($iTime, 3600000)
$min = Floor($remanH / 60000)
$remanM = Mod($remanH, 60000)
$sec = Floor($remanM / 1000)
$remanS = Mod($remanM, 1000)
$msec = Floor($remanS/100)
ConsoleWrite("task time " & $hour & ":" & $min & ":" & $sec & ":" & $msec & @CRLF)
Local $Hr1 = Int(Number(@HOUR))
Local $Min1 = Int(Number(@MIN))
Local $Sec1 = Int(Number(@SEC))
Local $MSc1 = Int(Number(@MSEC)/100)
$T1t2 = $Hr1 & ":" & $Min1 & ":" & $Sec1 & ":" & $MSc1
ConsoleWrite("End time " & $T1t2 & @CRLF)

; Run second macro
WinWaitActive("Excel_Workbook.xlsm - Excel")
Send("!{F8}")
WinWaitActive("Macro")
Send("{TAB}")
Send("{DOWN}")
Send("!R")
WinWaitActive("Excel_Workbook.xlsm - Excel")

 

[ad777]

@PeterlFF

put Sleep Function or wait until Macro Window finished!.

Edited March 11, 2022 by ad777

[Nine]

The .run method of Excel COM forces the script to wait until the macro is completed.  I would recommend you use Excel UDF to create the COM object.  But remember, it is a blocking method, you cannot interact with the macro in your script.  If you require to interact with the macro then you will need to create a second process (I have created an example recently in here, just search for it if need be).

[PeterlFF]

@Nine

I will look for your code. I plan to do this with other apps as well where I want to time how long certain tasks take. It sounds like your solution is specific to Excel. Is there something similar if I work with PowerBI, Tableau, or an Adobe product?

Can I do something like have the code look and wait for the result of the macro (i.e. updating a cell in the spreadsheet)?

@ad777

I don't think sleep will work for me since I want to time how long the action (the macro in this case) takes to run.

[Nine]

I cannot answer for all the products.  It will depend on various factors : what you are accomplishing, if there is a way to detect the end of a task, if there is a COM object blocking function, if a windows is involved, etc, etc.  Each situation will require a specific solution.

Edit : the best way to time a specific task is to do it all in AutoIt 

Edited March 11, 2022 by Nine

[PeterlFF]

@Nine I don't understand your last comment about doing it all in AutoIt.

[Nine]

I mean, instead of running a macro in Excel, you could do all in pure AutoIt with the use of the Excel UDF and (if needed) with the COM object properties and methods.  This also applies to any other applications you are trying to automate.

[PeterlFF]

Got it. Thanks.

[PeterlFF]

I've got the UDF working and it runs the macros and the code waits so my timings are working for running the macros. The time code is a bit repetitive so I will look into turning that into a function to reduce the code size. My next task is to copy some cells from Excel into PowerPoint. I have the same issue where the code doesn't wait for the paste to finish before moving on. 

I looked in the help file and don't see any UDF for powerpoint. How can I get the code to wait for the copy/paste to complete?

Here is my current code:

expandcollapse popup

#include <Excel.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "MS Excel Data Workflow", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Open Excel Workbook
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Excel_Workbook.xlsm")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "MS Excel Data Workflow", "Error opening workbook '" & @ScriptDir & "\Excel_Workbook.xlsm'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
 EndIf

; start time for first macro.
Local $hTimer = TimerInit()
Local $Hr = Int(Number(@HOUR))
Local $Min = Int(Number(@MIN))
Local $Sec = Int(Number(@SEC))
Local $MSc = Int(Number(@MSEC)/100)
$T1t1 = $Hr & ":" & $Min & ":" & $Sec & ":" & $MSc
ConsoleWrite("Create Calc " & $T1t1 & @CRLF)

; Run first macro
$oExcel.Run("CreateCalculations")

; end time for first macro.
Local $iTime = TimerDiff($hTimer) ; Find the difference in time from the previous call of TimerInit.
$pTime = $hTimer + $iTime ; Add TimerInit TimerDiff results to generate End time.
$hour = Floor($iTime / 3600000)
$remanH = Mod($iTime, 3600000)
$min = Floor($remanH / 60000)
$remanM = Mod($remanH, 60000)
$sec = Floor($remanM / 1000)
$remanS = Mod($remanM, 1000)
$msec = Floor($remanS/100)
ConsoleWrite("task time " & $hour & ":" & $min & ":" & $sec & ":" & $msec & @CRLF)
Local $Hr = Int(Number(@HOUR))
Local $Min = Int(Number(@MIN))
Local $Sec = Int(Number(@SEC))
Local $MSc = Int(Number(@MSEC)/100)
$T1t2 = $Hr & ":" & $Min & ":" & $Sec & ":" & $MSc
ConsoleWrite("End time " & $T1t2 & @CRLF)

; start time for second macro.
Local $hTimer = TimerInit()
Local $Hr = Int(Number(@HOUR))
Local $Min = Int(Number(@MIN))
Local $Sec = Int(Number(@SEC))
Local $MSc = Int(Number(@MSEC)/100)
$T2t1 = $Hr & ":" & $Min & ":" & $Sec & ":" & $MSc
ConsoleWrite("Create Pivot " & $T1t2 & @CRLF)

; Run second macro
$oExcel.Run("CreatePivotTable")

; end time for second macro.
Local $iTime = TimerDiff($hTimer) ; Find the difference in time from the previous call of TimerInit.
$pTime = $hTimer + $iTime ; Add TimerInit TimerDiff results to generate End time.
$hour = Floor($iTime / 3600000)
$remanH = Mod($iTime, 3600000)
$min = Floor($remanH / 60000)
$remanM = Mod($remanH, 60000)
$sec = Floor($remanM / 1000)
$remanS = Mod($remanM, 1000)
$msec = Floor($remanS/100)
ConsoleWrite("task time " & $hour & ":" & $min & ":" & $sec & ":" & $msec & @CRLF)
Local $Hr = Int(Number(@HOUR))
Local $Min = Int(Number(@MIN))
Local $Sec = Int(Number(@SEC))
Local $MSc = Int(Number(@MSEC)/100)
$T2t2 = $Hr & ":" & $Min & ":" & $Sec & ":" & $MSc
ConsoleWrite("End time " & $T1t2 & @CRLF)

; Copy PivotTable cells.
Local $oRange = $oWorkbook1.ActiveSheet.Range("A3:K420")
_Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oRange)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 1", "Error copying cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; MsgBox(0, "MS Excel Data Workflow", "Range 'A3:K42' successfully copied to clipboard.")

; Open PowerPoint Presentation
RunWait( @Comspec & " /c start PowerPoint_Presentation.pptx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "MS Excel Data Workflow", "Error opening PowerPoint '" & @ScriptDir & "\PowerPoint_Presentation.pptx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
 EndIf

; start time for paste.
WinWaitActive("PowerPoint_Presentation.pptx - PowerPoint")
Local $hTimer = TimerInit()
Local $Hr = Int(Number(@HOUR))
Local $Min = Int(Number(@MIN))
Local $Sec = Int(Number(@SEC))
Local $MSc = Int(Number(@MSEC)/100)
$T3t1 = $Hr & ":" & $Min & ":" & $Sec & ":" & $MSc
ConsoleWrite("Paste " & $T1t2 & @CRLF)

; Paste content to Presentation
Send("^v")

; end time for paste.
Local $iTime = TimerDiff($hTimer) ; Find the difference in time from the previous call of TimerInit.
$pTime = $hTimer + $iTime ; Add TimerInit TimerDiff results to generate End time.
$hour = Floor($iTime / 3600000)
$remanH = Mod($iTime, 3600000)
$min = Floor($remanH / 60000)
$remanM = Mod($remanH, 60000)
$sec = Floor($remanM / 1000)
$remanS = Mod($remanM, 1000)
$msec = Floor($remanS/100)
ConsoleWrite("task time " & $hour & ":" & $min & ":" & $sec & ":" & $msec & @CRLF)
Local $Hr = Int(Number(@HOUR))
Local $Min = Int(Number(@MIN))
Local $Sec = Int(Number(@SEC))
Local $MSc = Int(Number(@MSEC)/100)
$T3t2 = $Hr & ":" & $Min & ":" & $Sec & ":" & $MSc
ConsoleWrite("End time " & $T1t2 & @CRLF)

; Close PowerPoint
WinClose("[TITLE:PowerPoint_Presentation.pptx - PowerPoint]", "")
WinWaitActive("Microsoft PowerPoint")
Send("!n")

; Close Excel
_Excel_BookClose($oWorkbook1, False)
_Excel_Close($oExcel)

 

[Nine]

There is PowerPoint UDF here.

[PeterlFF]

The only thing I saw in the PowerPoint UDF is the _PPT_ExcelImport. I couldn't figure out how to make it work and I am not sure if I need since I already copied the range of cells to the clipboard using _Excel_RangeCopyPaste.

I have tried ClipGet and _ClipBoard_GetData but have only gotten them to work if I use send, i.e. Send(ClipGet()). When I do this it paste all the cells as text and it looks like it is being typed into PPT. 

I want a single paste like I get with Send ("^v") but I need the code to wait for the paste to finish before it processes more code. I suspect there is a different way to use _ClipGet or _ClipBoard_GetData but I haven't found examples that work for me.

Can anyone help point me in the right direction?