Jump to content
Sign in to follow this  
Jlchumley

Creating a loop that ends once a value in excel is reached

Recommended Posts

Jlchumley

Hey, 

I am trying to create at loop that will click hyperlinks in an excel column until there are no hyperlinks left in the column (cell value is "" I believe). Below is what I have to stop the loop, but it isn't working:

Local $ExcelRow = 1

Do

;;;;;Script to click link which moves down a row at a time to click each link in the same excel column;;;;;;;

$ExcelRow = ($ExcelRow + 1)

Until _ExcelReadCell($RefundSchedule,$ExcelRow,13) = ""   ;;;;;13 is the column the links are in;;;;;;;

 

Please let me know if anyone can help. Also, please let me know if anyone knows how to click a hyperlink in excel using a method other than mouseclicking the down scroll arrow and then mouseclick the top visible row in the hyperlink column.

Thank you!

Share this post


Link to post
Share on other sites
JLogan3o13

Hi, Jlchumley, welcome to the forum. We need a little bit more information, "isn't working" doesn't help us :) Can you please let us know if you're receiving an error, and if so, what that error is?


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
water

Welcome to AutoIt and the forum!

Can you please tell us why you need to click on all hyperlinks in the Excel file?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
water

Can you please tell us what $RefundSchedule is all about :huh:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Jlchumley

Thanks for the responses everyone.
 

The loop I created would not end. The mouse would continue to click the scroll down arrow and then the up-most visible cell in the spreadsheet.

I am clicking hyperlinks to access a database where i need to print invoices to PDFs. I have a spreadsheet with links to all the invoices I need, and once the script successfully accesses, prints, and saves the invoice to my computer, the script continues with the next cell down in the hyperlink column. I need the script to stop once there are no more links in the spreadsheet.

$refundschedule is a variable for the excel workbook I open earlier in the script before the loop.

;;; $RefundSchedule=_ExcelBookOpen($ExcelFilePath);;;;


The script is working fine now (I had rows hidden in excel for testing purposes, but those hidden rows contained values, so the script continued to loop. Beginners mistake lol. I appreciate everyone's time!

-dabbler: I am not familiar with ShellExecute and not sure I understand the the function's capabilities even after reading the help file. Please let me know if you know a way I could make my script more efficient now that I have explained what I am trying to accomplish. Also, if you have the time, I would be interested in hearing how you use the function for projects you encounter.

Thanks!

Share this post


Link to post
Share on other sites
water

To simulate clicking on a hyperlink use:

$oExcel.Activesheet.Range("M" & $ExcelRow).Hyperlinks(1).Follow

So there is no need to read the content of the cell.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
l3ill

ShellExecute starts just about anything with its default Program.

So: 

$var = "http://www.autoitscript.com/forum"
ShellExecute($var)

Will open a Hyperlink in your default browser.

With that said, any tips  from water should supersede anything I say  :)

 

Please let me know if you know a way I could make my script more efficient now that I have explained what I am trying to accomplish. Also, if you have the time, I would be interested in hearing how you use the function for projects you encounter.

 

We are all full of helpful tips and tricks! Just keep plugging away and if you get stuck or need some pointers we will be here.

Posting your code at this point would help us immensely in helping you.

Good luck with your script!

Bill

Share this post


Link to post
Share on other sites
water

With that said, any tips  from water should supersede anything I say  :)

Not necessarily! I have already posted so much bulls..t on this forum that everything I say needs to be checked and verified ;)

So you understand and get better all the time!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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  

×