Sign in to follow this  
Followers 0
Andreu

Excel UDF... Help with optimization

1 post in this topic

#1 ·  Posted (edited)

Hello! I have some rather extensive reports, that are extremely repetitive that I complete every day at work. This is one of them that I've recently decided, due to their consistency, can be completely automated. (I love you AutoIt)

Unlike most posts in this section..I have a completely functional program, but I know for a fact it could be so much faster... My lack of experience with AutoIt is probably significantly dragging it down. 

I'm asking for help with optimization, by somebody more experienced or at least better versed in the Excel UDF than I am.

I have created a version of my script that will run with the example excel file provided exactly how my real version runs/works. (Except the real version, also pulls 6 other reports... Which begs the question, could I make 7 copies of this... and run them all from a 'mother' copy... and thus pull them all at the same time?!? Just thought of that while typing this.)

So please, if you will... Run the program (the spreadsheet and au3 must be in the same folder as eachother)... and share with me any tips you have as to how to make it faster.

Code examples are greatly appreciated, but if nothing else a "doing it xyxy way would be better" or just sharing some best practices... I would be extremely grateful.

If you have any questions at all, please feel free to ask. I apologize in advance for how much scrubbing I had to do to the example spreadsheet... it will work just fine though (tested). 

(Note: Please do not take the time out of your day to completely re-code this script... As I know how generous some of you can be... But that will not help me, because A: I won't learn... and B: I had to modify the code so much to protect business confidential data that it would be more time consuming for me to modify your code to go back to the "real" version, than for me to learn from what you show me and do it myself. Trust I am equally as grateful.)

AutoIt Code:

#include <Excel.au3>
#include <Array.au3>
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>

HotKeySet("{END}", "_End")

Global $sTitle, $oExcel, $startRow, $UMdataHandle, $TMdataHandle, $ASdataHandle, $site, $dcol, $sFilePath1, $status, $endRow, $mCol, $uCol, $aCol

_SetStatus("Awaiting command...")

#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("PI [Tech: My Real Full Name]", 346, 204, 404, 130)
$Label1 = GUICtrlCreateLabel("DZ", 16, 8, 49, 28)
GUICtrlSetFont(-1, 14, 800, 0, "MS Sans Serif")
GUICtrlSetFont(-1, 14, 800, 0, "MS Sans Serif")
$AZ = GUICtrlCreateCheckbox("AZ", 16, 40, 97, 17)
$FW = GUICtrlCreateCheckbox("FW", 16, 64, 97, 17)
$GB = GUICtrlCreateCheckbox("GB", 16, 88, 97, 17)
$JX = GUICtrlCreateCheckbox("JX", 120, 40, 97, 17)
$LC = GUICtrlCreateCheckbox("LC", 120, 64, 97, 17)
$PL = GUICtrlCreateCheckbox("PL", 120, 88, 97, 17)
$BR = GUICtrlCreateCheckbox("BR", 16, 112, 97, 17)
$Label3 = GUICtrlCreateLabel("Tech: My Real Full Name - (My location ID)", 60, 168, 224, 17)
$Extract = GUICtrlCreateButton("Extract", 232, 40, 99, 65)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

$uCol = 4
$mCol = 5
$aCol = 4

While 1
   $nMsg = GUIGetMsg()
   Switch $nMsg
      Case $GUI_EVENT_CLOSE
         Exit
      Case $Extract
         If _OpenLogs() <> 1 Then
            If GUICtrlRead($AZ) = $GUI_CHECKED Then _Start("AZ")
            If GUICtrlRead($FW) = $GUI_CHECKED Then _Start("FW")
            If GUICtrlRead($GB) = $GUI_CHECKED Then _Start("GB")
            If GUICtrlRead($JX) = $GUI_CHECKED Then _Start("JX")
            If GUICtrlRead($LC) = $GUI_CHECKED Then _Start("LC")
            If GUICtrlRead($PL) = $GUI_CHECKED Then _Start("PL")
            If GUICtrlRead($BR) = $GUI_CHECKED Then _Start("BR")
            _Start("THIS LINE IS ONLY HERE SO WHOEVER HELPS ME TEST CAN RUN THIS WITHOUT THE NEED FOR BOX CHECKS ETC.")
            _SetStatus("Complete - All selected sites pulled.")
         Else
            _SetStatus("Error Description: Close any open Notepads.")
            MsgBox(0, "Error", "785134")
         EndIf
   EndSwitch
            
WEnd

Func _Start($uSite)
   $site = $uSite
   If $Site = "AZ" Then $dcol = 12
   If $Site = "FW" Then $dcol = 11
   If $Site = "GB" Then $dcol = 11
   If $Site = "JX" Then $dcol = 9
   If $Site = "LC" Then $dcol = 12
   If $Site = "PL" Then $dcol = 11
   If $Site = "BR" Then $dcol = 10
   $uSite = ""
   $sTitle = $uSite&"FORUM_SHEET.xlsx"; Our file names are 100% consistent. Only the prefix shown changes.
   _OpenWorkBooks()
   _ReadTheSheet()
   _SetStatus("Finished - Closing Workbook")
   _ExcelBookClose($oExcel, 0, 0)
   _SetStatus("Finished - Workbook Closed")
EndFunc

Func _SetStatus($text)
   ToolTip($text, 10, 10, "Status")
EndFunc

Func _GetAssociateData(); THESE GET LOGGED TO THE AS NOTEPAD
   $startRow = _GetStartingRow()
   $endRow = _GetEndingRow()
   For $oStart = $startRow To $endRow - $startRow
      _SetStatus("Site ("&$site&") Associate Data - Row Count: "&$oStart)
      $xFeed = _ExcelReadCell($oExcel, $oStart, $dcol)
      $yFeed = _ExcelReadCell($oExcel, $oStart, $aCol)
      $aStart = StringInStr($yFeed, "(") + 1
      $aEnd = StringInStr($yFeed, ")")
      $aComplete = StringMid($yFeed, $aStart, $aEnd - $aStart)
      If $yfeed <> "" Then
         _Log($ASdataHandle, $aComplete&":"&$xFeed)
      EndIf
   Next
EndFunc

Func _GetManagerData(); GETS LOGGED TO THE UM NOTEPAD
   For $oStart = $startRow To $endRow - $startRow
      _SetStatus("Site ("&$site&") Manager Data - Row Count: "&$oStart)
      $xFeed = _ExcelReadCell($oExcel, $oStart, $dcol)
      $yFeed = _ExcelReadCell($oExcel, $oStart, $uCol)
      If $yfeed <> "" Then
         _Log($UMdataHandle, $yFeed&":"&$xFeed)
      EndIf
   Next
EndFunc

Func _GetTMData(); LOGGED TO THE TM NOTEPAD
   For $oStart = $startRow To $endRow - $startRow
      _SetStatus("Site ("&$site&") Team Lead Data - Row Count: "&$oStart)
      $xFeed = _ExcelReadCell($oExcel, $oStart, $dcol)
      $yFeed = _ExcelReadCell($oExcel, $oStart, $mCol)
      If $yfeed <> "" Then
         _Log($TMdataHandle, $yFeed&":"&$xFeed)
      EndIf
   Next
EndFunc

Func _ReadTheSheet()
   _SetStatus("Site ("&$site&") Manager Data: Extracting Data")
   _GetManagerData()
   _GetTMData()
   _ExcelSheetActivate($oExcel, "CSR-DETAIL")
   _GetAssociateData()
EndFunc

Func _OpenWorkBooks()
   $sFilePath1 = @ScriptDir & "\" & $sTitle
   _SetStatus("Site ("&$site&") - Opening workbook: " & $sFilePath1)
   MsgBox(0, "File Path", $sFilePath1)
   $oExcel = _ExcelBookOpen($sFilePath1, 1)
   If @error = 1 Then
      _SetStatus("Error Description: Unable to create the Excel Object")
      MsgBox(0, "Error!", "367001")
      Exit
   ElseIf @error = 2 Then
      _SetStatus("Error Description: The file does not exist. Ensure ADH files are in the program directory.")
      MsgBox(0, "Error!", "200876")
      Exit
   EndIf
   _ExcelSheetActivate($oExcel, "Managers Tab")
   $startRow = _GetStartingRow()
   $endRow = _GetEndingRow()
EndFunc

Func _GetStartingRow()
   _SetStatus("Site ("&$site&"): Counting rows...")
   $found = False
   For $row = 1 To 10
      $Test = _ExcelReadCell($oExcel, $row, 3)
      If @Error <> 0 Then MsgBox(0, "Error", "338049 - " & @Error)
      If $Test = "SITE" Then
         $Found = True
         Return $row + 2
      EndIf
   Next
   If $Found <> True Then MsgBox(0, "Error", "Error Code: 265834")
   EndFunc
   
Func _GetEndingRow()
   $row = $startRow
   While _ExcelReadCell($oExcel, $row, 3) <> "Grand Total"
      If @Error <> 0 Then MsgBox(0, "Error", "338049 - " & @Error)
      $row += 1
   Wend
   Return $row
EndFunc

Func _OpenLogs()
   $status = "Opening Log Files"
   _SetStatus("Site ("&$site&"): Opening Log Files")
   If ProcessExists("notepad.exe") Then
      Return 1
   Else
      Run(@WindowsDir & "\notepad.exe", "", @SW_MINIMIZE)
      WinWait("Untitled - Notepad")
      WinSetTitle("Untitled - Notepad", "", "UM Data")
      $UMdataHandle = WinGetHandle("UM Data")
      ;_Log($UMdataHandle, "Test")
      
      Run(@WindowsDir & "\notepad.exe", "", @SW_MINIMIZE)
      WinWait("Untitled - Notepad")
      WinSetTitle("Untitled - Notepad", "", "TM Data")
      $TMdataHandle = WinGetHandle("TM Data")
      ;_Log($TMdataHandle, "Test")
      
      Run(@WindowsDir & "\notepad.exe", "", @SW_MINIMIZE)
      WinWait("Untitled - Notepad")
      WinSetTitle("Untitled - Notepad", "", "AS Data")
      $ASdataHandle = WinGetHandle("AS Data")
      ;_Log($ASdataHandle, "Test")
   EndIf
EndFunc

Func _Log($handle, $data)
   ConsoleWrite("LOG REQUEST: Received Handle ("&$handle&" - Adding Data: "&$data&@LF)
   $send = ControlSend($handle, "", "[CLASS:Edit; INSTANCE:1]", String($data), 1)
   ControlSend($handle, "", "[CLASS:Edit; INSTANCE:1]", @LF, 1)
EndFunc

Func _End()
   Exit
EndFunc

EDIT:

I didn't want to make the above into a novel... But some more information:

The way it logs the data... I know most of you will undoubtedly think I'm crazy for the way I did it... But I need the data seperated, UM -> TM -> AS as it is... And while it doesn't necessarily have to be done through notepad... I need it exported to me in some fashion where it returns a copy/paste of all of the data.

I'm sure you noticed, the second tabs names are like this LASTNAME, FIRST NAME MI (number)... the script only needs the number.

This needs to be able to be ran without taking control over what is my active window, or control of my mouse... and (while I doubt this should pose a problem) has to be compatible with multiple monitors. (My work desk has several...)

It "counts" the rows, before actually extracting the data. I could probably cut this out, and simply have it find the last row while pulling the data by the same means it does currently... But a huge time saver I think, is to find some ability to skip empty rows so its not wasting its time there in the first place.

NOTE: The LAST column with numbers in it CAN CHANGE. (At the time if you notice, I assign a column number based on whichever report they selected on the GUI.) 

I'll continue to edit this if I think of anything before anybody responds.

FORUM_SHEET.xlsx

Edited by Andreu

Share this post


Link to post
Share on other sites



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0