DaisyDuke Posted February 9, 2012 Share Posted February 9, 2012 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 Link to comment Share on other sites More sharing options...
hannes08 Posted February 9, 2012 Share Posted February 9, 2012 Hello DaisyDuke, try something like this: $oExcel = _ExcelBookOpen("...") $oExcel.Calculate Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler] Link to comment Share on other sites More sharing options...
DaisyDuke Posted February 9, 2012 Author Share Posted February 9, 2012 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 Link to comment Share on other sites More sharing options...
DaisyDuke Posted February 9, 2012 Author Share Posted February 9, 2012 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. Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted February 9, 2012 Moderators Share Posted February 9, 2012 Hi, DaisyDuke. Are you opening both workbooks through AutoIT, or just one? "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
DaisyDuke Posted February 9, 2012 Author Share Posted February 9, 2012 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. Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted February 9, 2012 Moderators Share Posted February 9, 2012 Maybe try opening both through your script, and see if you get any different result? "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
DaisyDuke Posted February 9, 2012 Author Share Posted February 9, 2012 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_xHHdnkwPmPYmJlNTc1NzEtMmVkNy00ZDk3LWE1ZDctMDA5ZGM5MmJlZWRmhttps://docs.google.com/open?id=0B_xHHdnkwPmPNzM0YmMyNTQtYTY0NC00MTdiLWJhNmEtYzMzZWM5OTIwOWRm Link to comment Share on other sites More sharing options...
Tonnie Posted February 10, 2012 Share Posted February 10, 2012 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. Link to comment Share on other sites More sharing options...
Reg2Post Posted February 10, 2012 Share Posted February 10, 2012 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now