Sign in to follow this  
Followers 0
Jlchumley

Issue writing into Excel when an Excel hyperlink is not functioning

12 posts in this topic

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

Share this post


Link to post
Share on other sites



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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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! 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

 

 

#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 by Melba23
Added code tags

Share this post


Link to post
Share on other sites

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


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

Thank you!!

Share this post


Link to post
Share on other sites

Replace line

AutoItSetOption(WinTitleMatchMode,3)

with

AutoItSetOption("WinTitleMatchMode", 3)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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:

 

#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")

Share this post


Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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