problem with excel opened by autoit script

[DaisyDuke]

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

[hannes08]

Hello DaisyDuke,

try something like this:

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

[DaisyDuke]

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

[DaisyDuke]

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.

[JLogan3o13]

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

[DaisyDuke]

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.

[JLogan3o13]

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

[DaisyDuke]

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

[Tonnie]

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.

[Reg2Post]

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.