Sign in to follow this  
Followers 0
cafshari

How to get my script to change and save a variable value after each run?

18 posts in this topic

My script reads an excel cell and uses the value of it. After I run the script the first time, in the next run, I want to read the row+1 (the value of the cell beneath the previous one). I've thought of just doing $row = 36, and then at the end of my code, increment it... but that wouldn't work. Any ideas? I would also need to reset that variable's value back to 36 after it hits 40. 

This script is being executed from a Java function in Eclipse. I also thought maybe I could pass a variable/value to the script from where I call the script in my Java code, but I'm not sure if that's possible? I've thought of making 5 different scripts, 1 for each cell location, but my Java function locates only 1 script and executes it.

 
A For loop wouldn't work in my script, because my task has to end the script and perform some other work before I need to run the script again. 
 
I hope this is understandable, as this is hard for me to explain.
...
    
Local $ImageLoc = _ExcelReadCell($oExcel, 36, 4)    ; need row# to increment after each time the script is run.     
Send($ImageLoc)
Send("{ENTER}")

 

Share this post


Link to post
Share on other sites



Pass the row to process from Java to AutoIt. In AutoIt you can access the parameters using $CmdLine[1]


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

Pass the row to process from Java to AutoIt. In AutoIt you can access the parameters using $CmdLine[1]

 

I am a complete commandline newb. I'd prefer not to use it if I don't have to, as i have no idea what i'm doing. regarding the first part of what you said, I don't have access to the row in Java. Are you suggesting I read the excel row in my java code?

Share this post


Link to post
Share on other sites

So when you first call the AutoIt script it starts with row 1 and increments by 1 for every time you call it until it reaches 40 and then starts over again?


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

So when you first call the AutoIt script it starts with row 1 and increments by 1 for every time you call it until it reaches 40 and then starts over again?

 

No, I want it to start at row 36, and perform 5 times. then start at 36 again. Btw why can I only make 8 more posts?

Share this post


Link to post
Share on other sites

Because you created your account today. To prevent spamming a new user has only 10 posts on his first day on the forum. But if needed this limit can be lifted by a Mod.

Read the row number from an ini file, increment the number and store it again in a the ini file:

Global $iRow = IniRead("Test.Ini", "Main", "Row", 36)
; Process Excel here
$iRow = $iRow + 1
If $iRow > 40 Then $iRow = 36
IniWrite("Test.Ini", "Main", "Row", $iRow)

 

1 person likes this

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

#7 ·  Posted (edited)

 

Because you created your account today. To prevent spamming a new user has only 10 posts on his first day on the forum. But if needed this limit can be lifted by a Mod.

Read the row number from an ini file, increment the number and store it again in a the ini file:

Global $iRow = IniRead("Test.Ini", "Main", "Row", 36)
; Process Excel here
$iRow = $iRow + 1
If $iRow > 40 Then $iRow = 36
IniWrite("Test.Ini", "Main", "Row", $iRow)

 

Sorry, but what exactly would my INI file look like? I'd rather not make assumptions, since I have no experience with this. Some research leads me to believe its just: 

[Main]
Row=36
 
Oh, and will that .ini file save on its own?
Edited by cafshari

Share this post


Link to post
Share on other sites

The ini file is created by the script. InIRead returns a default value of 36 if the file does not exist. If IniWrite the file is then created with a value of 37

1 person likes this

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

water, thanks for your help so far. I have that working. Now my script is called to run 5 times, and in each of those times it opens an excel workbook. I want the book to close after each run. It is closing, however I am seeing the Save/Don't Save/Cancel prompt pop up each time. I do not want it to save, I just want it to close. How do I get this to not show up? I have my workbooks opening as hidden, btw.

$iRow = $iRow + 1

If _ExcelReadCell($oExcel, $iRow, 4) = "" Then      ; if the next cell is empty, reset the variable to 36 and end script  
   IniWrite($inifileloc, "Main", "Row", 36)  
   _ExcelBookClose($oExcel, 0, 0)
   Exit
EndIf

$oExcel.Quit
IniWrite($inifileloc, "Main", "Row", $iRow)

Am I using _ExcelBookClose incorrectly? I thought the 2nd '0' parameter is to disable the message alert?

Thanks in advance.

Share this post


Link to post
Share on other sites

Do you modify the Excel workbook?


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

Do you modify the Excel workbook?

 

No, I just copy 5 cells from it. Not sure if that counts as modifying

Share this post


Link to post
Share on other sites

It doesn't count as modification. So when closing the workbook the message shouldn't pop up. Or is data of the workbook automatically being updated when opened or a script automatically changes the workbook?


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

It doesn't count as modification. So when closing the workbook the message shouldn't pop up. Or is data of the workbook automatically being updated when opened or a script automatically changes the workbook?

 

Nothing is being changed in the workbook, as far as I know. I have it being opened as hidden and read-only. 

Share this post


Link to post
Share on other sites

Try

$oExcel.ActiveWorkbook.Saved = True

before closing the workbook.

1 person likes this

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

Try

$oExcel.ActiveWorkbook.Saved = True

before closing the workbook.

 

But I don't want to touch anything in the excel file. In the case that something is being modified, I don't want to save it. That Excel file can't be touched.

Share this post


Link to post
Share on other sites

The statement I provided simulates a Save command so when you close Excel no popup should appear.


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

The statement I provided simulates a Save command so when you close Excel no popup should appear.

 

But does it actually save? Because god forbid the sheet actually is modified, I don't want to save those changes. Sorry, I'm not following!!

Share this post


Link to post
Share on other sites

No, it doesn't.

Excel has a "Saved" property that is set to False whenever you change your workbook. When you quit and "Saved" is False then Excel tries to save the workbook and asks by showing the popup.

If you "manually" set "Saved" to True you simulate a save operation. When you exit Excel all changes will be dropped.


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