Sign in to follow this  
Followers 0
DaisyDuke

problem with excel opened by autoit script

10 posts in this topic

Dear all, my first post! I am being using autoit for a while though being silent because I always found the answers to my question in the forum. But here I am stuck with a silly problem related with excel.

There are 2 excel workbooks, lets call them A and B. Some cells in A are linked via formulas to cells in B. Thereafter as soon as I write manually in a cell in B it updates the linked cell in A. And here it's normal as it should be.

The problem comes when my autoit script opens the workbook B and write to it, cells in A are not updated automatically even if the workbook B is saved after every data entry. It looks like the autoit script has priority on the workbook B.

I would use a single workbook for my job, like using one sheet for automatic data entry by the autoit script, and a second sheet for calculation and analysis, but if I write to the calculation sheet the script will not write the data on the other one. You will probably think I have to specify who's handling the workbook or something like that. Well how do you do that?

cheers to all

Share this post


Link to post
Share on other sites



Hello DaisyDuke,

try something like this:

$oExcel = _ExcelBookOpen("...")
$oExcel.Calculate

Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

thanks Hannes, i'll give it a try but I have to change the workbook, i mean i have to go back and use a single workbook, because the calculate function upply to the workbook opened by the script (:), and it doens't update the other workbook A.

But it's a good hint, I'm glad.

back soon with updates

Share this post


Link to post
Share on other sites

I'm still not focusing why the workbook A doesn't update it's linked cells to workbook B when the latter has been opened by the autoit script. And it works when I open it and manually type some data.

I will write my data to a txt file instead and then import it in excel as a workaround to this matter, with autoupdating should work, finger crossed.

Share this post


Link to post
Share on other sites

Hi, DaisyDuke. Are you opening both workbooks through AutoIT, or just one?


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

Share this post


Link to post
Share on other sites

Hi, DaisyDuke. Are you opening both workbooks through AutoIT, or just one?

only one, woorbook B, the one where the script writes data to, the other one (A) I open it myself.

Share this post


Link to post
Share on other sites

Maybe try opening both through your script, and see if you get any different result?


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

Share this post


Link to post
Share on other sites

I am moving on by trial and error, and I finished my bullets..

if anyone wants to experience this, create 2 excel file in your C drive called workbookA and workbookB, link cell A1 of workbook A to cell A1 of workbookB, then run the test script (see links) and ... why is workbookA not updating? :)

https://docs.google.com/open?id=0B_xHHdnkwPmPYmJlNTc1NzEtMmVkNy00ZDk3LWE1ZDctMDA5ZGM5MmJlZWRm

https://docs.google.com/open?id=0B_xHHdnkwPmPNzM0YmMyNTQtYTY0NC00MTdiLWJhNmEtYzMzZWM5OTIwOWRm

Share this post


Link to post
Share on other sites

Hello Daisy,

I can reproduce your problem. My Excel 2007 is giving a warning when I open WorkbookA which translates to something like "automatic updating of linked content is disabled". I don't know the exact English text, because I have a different language. When I set the security in Excel to automatically update linked content, the problem is gone.

The setting can be found at Excel button -> Options -> Trust Centre (?) -> Settings for Trust Centre -> External Content (?) -> Security Settings for Connected Workbooks. Again I'm not sure about the translation to the names of English menu items, but maybe this is enought to figure it out.

Share this post


Link to post
Share on other sites

I agree with JLogan3o13. Open both workbooks with a script. To avoid the pop-up about updating, open both workbooks in the same instance.

Or, open workbook A (manually) after script opens workbook B.

Excel doesn't update links to external workbooks correctly when the workbooks are in separate instances in my test environment.

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