Issue writing into Excel when an Excel hyperlink is not functioning

[Jlchumley]

Hey!

I am creating a script that opens an Excel workbook, clicks the scroll down arrow in the opened workbook, and then clicks the up-most, visibly hyperlink in column M.

The hyperlinks in column M each open a bluebeam file from a fake client database system containing an invoice (This script is going to be used in a presentation, so no live data). The script saves these invoices to a folder called "GT Invoice Folder", and the aforementioned activities are continued in a Do Loop until there is no hyperlink in the next subsequent cell in column M.

I have a script that successfully performs the activities mentioned above, but now I want to add an If Statement in the middle of the script that will write a note in the opened Excel workbook's column L when a clicked hyperlink is dysfunctional and does not open an invoice (Instead an error message appears, and it's window title is "Microsoft Excel" - Unfortunately this is similar to the window title for the Excel workbook, and at one point in the loop I change the AutoItSet option for WinTitleMatchMode to option 2. I think the issue I am about to mention may be due to this even though I change WinTitleMatchMode back to option 1 towards the end of the loop.) 

After creating the If Statement to make notes in Excel when a hyperlink is dysfunctional, the script fails to run correctly after clicking the first hyperlink (which I know is functional), a Bluebeam window for the invoice appears momentarily, and then the Excel workbook appears to become activated. Attached is a copy of my script. Please let me know if any of you can provide some guidance.

Thank you!

Presentation.au3

[water]

Don't automate the Excel GUI. Use COM to write reliable scripts. The Excel UDF that comes with AutoIt will help.

Something like:

$oExcel = _ExcelBookOpen(...)
For $oLink in $oExcel.ActiveSheet.Hyperlinks
    If $oLink.Range.Column = 13 Then ; Column "M"
       ; process the Link $oLink.Address
    EndIf
Next

[Jlchumley]

Thanks for the response!

COMs are a new concept for me, and I have no experience working with them. After reading about them in the help file, I see that I can create objects and have methods performed on them. Do you know where I could find a list of methods that can be used on objects? With my understanding, It looks like you are using 4 in your example above (ActiveSheet, Hyperlinks, Range, Column), but I do not know the reasons why they are providing me with the desired result. I do not know COMs well enough yet to manipulate the information you have provided me with to suite my unique situation, and the help file (as far as I was able to locate) does not mention anything about ActiveSheet, Hyperlinks, Range, Column. A list/explanation of the various methods that can be used on object seems like it will help me to begin scripting at a more efficient level, and will allow me to self-study COMs.

Please let me know if you can provide further guidance.

Thank you! 

[Jlchumley]

Hello!

 

for the sake of time, I will need to automate the Excel GUI in my script (I will self-study and try to incorporate COMs in my future scripts). In regards to why Bluebeam temporarily appears to become active and then Excel appears to become active, I thought I could solve the problem by changing the AutoITSetOptions for WinTitleMatchMode to parameter 3 just before the link is clicked and if the error message (window title "Microsoft Excel") does not exist, the AutpoITSetOption for WinTitleMatchMode is set to 2 to take into account the varying Bluebeam window titles I will need to access.

 

However, now my script does not run do to a syntax error with the AutoITSetOption just before the hyperlink is clicked. I think this may be due to having multiple AutoITSetOptions for WinTitleMatchMode in my script but not sure.  Below is an updated version of my script (I am not sure how to attach as a file now that this comment is not an initial post).

 

 

expandcollapse popup

#include <Excel.au3>
 
;;TO STOP THE SCRIPT FROM RUNNING;;
Func Terminate()
Exit 0
EndFunc
 
HotKeySet("{ESC}", "Terminate")
 
;;;;;;;; START THE SCRIPT HERE;;;;;;;;;;;;;;;
 
 
;ACTIVATE CLIENT'S DATABASE SYSTEM (LINKS PROVIDED ON REFUND SCHEDULE);
 
Local $ExcelFilePath = "D:\Users\US33852\Desktop\AutoIT Scripts\Presentation\2009 Refund Spreadsheet.xlsx"
$RefundSchedule=_ExcelBookOpen($ExcelFilePath)
 
Local $ExcelRow = 2
Local $ExcelRow2 = 1
 
 
DO
 
WinActivate("Microsoft Excel - 2009 Refund Spreadsheet.xlsx")
WinWait("Microsoft Excel - 2009 Refund Spreadsheet.xlsx")
 
Sleep(2000)
 
;CLICK THE LINK TO ACCESS CLIENT'S DATABASE;
 
MouseClick("left",1589,813) ;Location to scroll down to next cell
 
$PicCapture= PixelChecksum(1053,188,1188,248)
MouseClick("left",1291,193)
 
;Location to click hyperlink
;;TO SET WINDOW MATCHING TO EXACT MATCH;;;;;
 
AutoItSetOption(WinTitleMatchMode,3)
 
$Loopcounter= 0
While $PicCapture=PixelChecksum(1053,188,1188,248)
Sleep(1000)
$Loopcounter=$Loopcounter+1
If $Loopcounter=5 Then
MouseClick("left",1291,193) ;Location to click hyperlink
$Loopcounter=0
EndIf
WEnd
 
 
 
 
Sleep(2000)
 
;;;TO TAKE INTO ACCOUNT MISSING INVOICES;;;
If WinExists("Microsoft Excel") Then
WinActivate("Microsoft Excel")
Send("{ENTER}")
WinActivate("Microsoft Excel - 2009 Refund Spreadsheet.xlsx")
WinWait("Microsoft Excel - 2009 Refund Spreadsheet.xlsx")
_ExcelWriteCell($RefundSchedule,"Could not Pull",$ExcelRow2,12)
Sleep(2000)
MouseClick("left",1589,813)
$Loopcounter= 0
$PicCapture= PixelChecksum(1053,188,1188,248)
While $PicCapture=PixelChecksum(1053,188,1188,248)
Sleep(1000)
$Loopcounter=$Loopcounter+1
If $Loopcounter=5 Then
MouseClick("left",1291,193) ;Location to click hyperlink
$Loopcounter=0
EndIf
WEnd
 
EndIf
 
Sleep(2000)
 
;Setting window matching to any string in window;
AutoItSetOption("WinTitleMatchMode",2)
WinActivate("Bluebeam")
WinWait("Bluebeam")
Sleep(3000)
 
;SAVE INVOICE TO GT INVOICE FOLDER;
send("+^s")
WinActivate("Save File")
WinWait("Save File")
Sleep(2500)
Send("{TAB 2}")
Send("{ENTER}")
 
Sleep(1500)
MouseClick(729.180)
Send("D:\Users\US33852\Desktop\AutoIT Scripts\Presentation\GT Invoice Folder")
Send("{Enter}")
Sleep(3500)
Send("{Enter}")
Sleep(3500)
Send("!{ESCAPE}")
 
$ExcelRow = ($ExcelRow + 1)
$ExcelRow2 = ($ExcelRow2 + 1)
AutoItSetOption("WinTitleMatchMode",3)
 
$CellValue = _ExcelReadCell($RefundSchedule,$ExcelRow,13)
 
 
Until  $CellValue = ""
 
 
MsgBox(0,"It's Working","It's Working")

Edited November 25, 2013 by Melba23
Added code tags

[Melba23]

Jlchumley,

When you post code please use Code tags - see here how to do it. Then you get a scrolling box and syntax colouring as you can see above now I have added the tags.

And I have lifted your "New Member posting restriction - no worries about running out now.

M23

[Jlchumley]

Thank you!!

[water]

Replace line

AutoItSetOption(WinTitleMatchMode,3)

with

AutoItSetOption("WinTitleMatchMode", 3)

[water]

COMs are a new concept for me, and I have no experience working with them. After reading about them in the help file, I see that I can create objects and have methods performed on them. Do you know where I could find a list of methods that can be used on objects? With my understanding, It looks like you are using 4 in your example above (ActiveSheet, Hyperlinks, Range, Column), but I do not know the reasons why they are providing me with the desired result. I do not know COMs well enough yet to manipulate the information you have provided me with to suite my unique situation, and the help file (as far as I was able to locate) does not mention anything about ActiveSheet, Hyperlinks, Range, Column. A list/explanation of the various methods that can be used on object seems like it will help me to begin scripting at a more efficient level, and will allow me to self-study COMs.

AutoIt only provides an interface to COM. A list of objects, properties etc. can be found on the Microsoft Developer Network (MSDN).

For Excel 2007 start here, for Excel 2010 here.

When you have finished your current script, I will be glad to show you how it would be coded using COM.

[Jlchumley]

Thank you! I will check out the lists. My code worked successfully after your help, so thank you again for your help!

Below is my code if you want to show me how you would have written it:

 

expandcollapse popup

#include <Excel.au3>

;;TO STOP THE SCRIPT FROM RUNNING;;
Func Terminate()
Exit 0
EndFunc

HotKeySet("{ESC}", "Terminate")

;;;;;;;; START THE SCRIPT HERE;;;;;;;;;;;;;;;

;ACTIVATE CLIENT'S DATABASE SYSTEM (LINKS PROVIDED ON REFUND SCHEDULE);

Local $ExcelFilePath = "D:\Users\US33852\Desktop\AutoIT Scripts\Presentation\2009 Refund Spreadsheet.xlsx"
$RefundSchedule=_ExcelBookOpen($ExcelFilePath)


Local $ExcelRow = 2

DO

WinActivate("Microsoft Excel - 2009 Refund Spreadsheet.xlsx")
WinWait("Microsoft Excel - 2009 Refund Spreadsheet.xlsx")

Sleep(2000)

;;TO SET WINDOW MATCHING TO EXACT MATCH;;;;;

AutoItSetOption("WinTitleMatchMode",3)

;CLICK THE LINK TO ACCESS CLIENT'S DATABASE;

MouseClick("left",1589,813) ;Location to scroll down to next cell
MouseClick("left",1291,193) ;Location to click hyperlink

Sleep(4000)

;;;TO TAKE INTO ACCOUNT MISSING INVOICES;;;
If WinExists("Microsoft Excel") Then
    Sleep(800)
    WinActivate("Microsoft Excel")
    Send("{ENTER}")
    Sleep(800)
    WinActivate("Microsoft Excel - 2009 Refund Spreadsheet.xlsx")
    WinWait("Microsoft Excel - 2009 Refund Spreadsheet.xlsx")
    _ExcelWriteCell($RefundSchedule,"Could Not Pull",$ExcelRow,12)
    Sleep(2000)

    Else

    AutoItSetOption("WinTitleMatchMode",2)
    WinActivate("Bluebeam")
    WinWait("Bluebeam")
    Sleep(3000)

    send("+^s")
    WinActivate("Save File")
    WinWait("Save File")
    Sleep(2500)
    Send("{TAB 2}")
    Send("{ENTER}")

    Sleep(1500)
    MouseClick(729.180)
    Send("D:\Users\US33852\Desktop\AutoIT Scripts\Presentation\GT Invoice Folder")
    Send("{Enter}")
    Sleep(3500)
    Send("{Enter}")
    Sleep(3500)
    Send("!{ESCAPE}")

EndIf

$ExcelRow = ($ExcelRow + 1)

$CellValue = _ExcelReadCell($RefundSchedule,$ExcelRow,13)


Until  $CellValue = ""


MsgBox(0,"It's Working","It's Working")

[water]

I would so something like this. It only covers the part of looping through all hyperlinks in the worksheet and opens links in column "M". Every link is processed in function "_ProcessLink".

#include <Excel.au3>

HotKeySet("{ESC}", "Terminate")

Func Terminate()
    Exit 0
EndFunc   ;==>Terminate

Global $sExcelFilePath = "C:\Temp\test.xlsx"
$oExcel = _ExcelBookOpen($sExcelFilePath)       ; Open the workbook

For $oLink In $oExcel.ActiveSheet.Hyperlinks    ; Check all hyperlinks of the active worksheet
    If $oLink.Range.Column = 13 Then            ; Only process hyperlinks in column "M"
        _ProcessLink($oLink)                    ; Process a hyperlink
        If @error Then
            MsgBox(16, "Error", "Could not process hyperlink " & $oLink.Address)
        EndIf
    EndIf
Next

Func _ProcessLink($oLink)                       ; Function to process a single hyperlink
    $oLink.Follow()                             ; Open the hyperlink
    If @error Then Return SetError(1, 0, 0)     ; Return an error
    ; Now process Bluebeam
EndFunc

[Jlchumley]

Thank you! I will play around with this format after my presentation. You have been very helpful.

Also, why would you set a variable to Global as opposed to Local like you did with the file path?

-J

[water]

Just good coding practice. A variable defined as Local in the main script is a Global variable. In this case I use Global.