Imbuter2000 Posted February 23, 2012 Share Posted February 23, 2012 MY NEED: to automatically open two xls files (1 and 2), fill a column in 1 with vlookup formulas with references to file 2, do other commands on those files... (all from an Autoit script) MY ATTEMPTED SOLUTION: I open the two files with two consecutive _ExcelBookOpen commands this way: $oExcel1 = _ExcelBookOpen(file1.xls) $oExcel2 = _ExcelBookOpen(file2.xls) I write the vlookup formulas with a cycle of: $oExcel1.Cells($row,"=vlookup(........file2.xls.........)").formula and I use the $oExcel1 and $oExcel2 objects for following commands... THE PROBLEM: all the Excel formulas mysteriously/wrongly returns #N/A! Troubleshooting the problem I discovered that the problem is caused by the fact that the two Excel files are open in two separated Excel application windows (and processes?). Indeed, if I (manually) open both them in the same Excel application window or if I convert them in two sheets of a single file, the analogous vlookup formulas all works! So... how could I solve this problem? i.e. how can I open both the files from Autoit in the same Excel window having two valid objects $oExcel1 and oExcel2 for following commands? Link to comment Share on other sites More sharing options...
DW1 Posted February 23, 2012 Share Posted February 23, 2012 (edited) The following will open two books in the same excel application: #include <Excel.au3> $oExcel = _ExcelBookOpen(@DesktopDir & 'Book1.xls') $oExcel.WorkBooks.Open(@DesktopDir & 'Book2.xls', Default, False) Not sure if this is what you are looking for. Take a look at the _ExcelBookOpen() function in Excel.au3 for more hints EDIT: I re-read your post, and this DOES NOT supply two different objects Edited February 23, 2012 by danwilli AutoIt3 Online Help Link to comment Share on other sites More sharing options...
DW1 Posted February 23, 2012 Share Posted February 23, 2012 This DOES create two different objects though: #include <Excel.au3> $oExcel = _ExcelBookOpen(@DesktopDir & 'Book1.xls') $oExcel.WorkBooks.Open(@DesktopDir & 'Book2.xls', Default, False) $oExcel1 = _ExcelBookAttach(@DesktopDir & 'Book1.xls') $oExcel2 = _ExcelBookAttach(@DesktopDir & 'Book2.xls') _ExcelWriteCell($oExcel1, 'I am $oExcel1', 'A2') _ExcelWriteCell($oExcel2, 'I am $oExcel2', 'A2') AutoIt3 Online Help Link to comment Share on other sites More sharing options...
Blinky Posted February 23, 2012 Share Posted February 23, 2012 i think you should save the file after cell formula entry and if you modify a cell in the second file that the formula uses u should save that one to. Link to comment Share on other sites More sharing options...
DW1 Posted February 23, 2012 Share Posted February 23, 2012 i think you should save the file after cell formula entry and if you modify a cell in the second file that the formula uses u should save that one to.The example I provided is not a full script, just showing how to open two books in one instance of excel while providing two different objects to work with (one for each book) AutoIt3 Online Help Link to comment Share on other sites More sharing options...
Blinky Posted February 23, 2012 Share Posted February 23, 2012 i was telling the owner of this post that his problem may be that any formulas that use other files data may need to be flushed to disc. it had nothing to do with your script. sorry about that Link to comment Share on other sites More sharing options...
DW1 Posted February 23, 2012 Share Posted February 23, 2012 i was telling the owner of this post that his problem may be that any formulas that use other files data may need to be flushed to disc. it had nothing to do with your script.sorry about thatHaha, my bad /facepalm, gotcha AutoIt3 Online Help Link to comment Share on other sites More sharing options...
Imbuter2000 Posted February 23, 2012 Author Share Posted February 23, 2012 This DOES create two different objects though: #include <Excel.au3> $oExcel = _ExcelBookOpen(@DesktopDir & 'Book1.xls') $oExcel.WorkBooks.Open(@DesktopDir & 'Book2.xls', Default, False) $oExcel1 = _ExcelBookAttach(@DesktopDir & 'Book1.xls') $oExcel2 = _ExcelBookAttach(@DesktopDir & 'Book2.xls') _ExcelWriteCell($oExcel1, 'I am $oExcel1', 'A2') _ExcelWriteCell($oExcel2, 'I am $oExcel2', 'A2') Great idea! Tomorrow at work I'll try it and will report the result! thanks!! Link to comment Share on other sites More sharing options...
Imbuter2000 Posted February 23, 2012 Author Share Posted February 23, 2012 by the way I wrongly wrote the command that writes the vlookup formulas. This is the correct way:$oExcel1.Cells($row,$column).formula = "=vlookup(........file2.xls.........)"Anyway the problem is not here, I wrote it correctly in my script. The only problem was to obtain an object for the second file, opened by workbooks.open. I cross my finger for the solution suggested by danwilli. Tomorrow I'll let you know if all will work! Link to comment Share on other sites More sharing options...
Imbuter2000 Posted February 24, 2012 Author Share Posted February 24, 2012 This DOES create two different objects though: #include <Excel.au3> $oExcel = _ExcelBookOpen(@DesktopDir & 'Book1.xls') $oExcel.WorkBooks.Open(@DesktopDir & 'Book2.xls', Default, False) $oExcel1 = _ExcelBookAttach(@DesktopDir & 'Book1.xls') $oExcel2 = _ExcelBookAttach(@DesktopDir & 'Book2.xls') _ExcelWriteCell($oExcel1, 'I am $oExcel1', 'A2') _ExcelWriteCell($oExcel2, 'I am $oExcel2', 'A2') Ok I just tried it and it works but not for all the functions. For example the _ExcelSheetList fails. Try to add this to see the problem: #Include <Array.au3> $temp1 = _ExcelSheetList($oExcel1) $temp2 = _ExcelSheetList($oExcel2) _ArrayDisplay($temp1) _ArrayDisplay($temp2) Link to comment Share on other sites More sharing options...
Imbuter2000 Posted February 24, 2012 Author Share Posted February 24, 2012 Ok I just tried it and it works but not for all the functions.For example the _ExcelSheetList fails. Try to add this to see the problem:#Include <Array.au3>$temp1 = _ExcelSheetList($oExcel1)$temp2 = _ExcelSheetList($oExcel2)_ArrayDisplay($temp1)_ArrayDisplay($temp2)To avoid the error I modified it this way:#Include <Array.au3>$temp1 = _ExcelSheetList($oExcel1.Application)$temp2 = _ExcelSheetList($oExcel2.Application)_ArrayDisplay($temp1)_ArrayDisplay($temp2)but... with various tests I saw that the two arraydisplay both list the sheets of the $oExcel1 workbook! Link to comment Share on other sites More sharing options...
Juvigy Posted February 24, 2012 Share Posted February 24, 2012 Could you make a simple script to reproduce the issue ? I think the fact that excel is opening 2 files in separate processes is not the problem. Link to comment Share on other sites More sharing options...
Imbuter2000 Posted February 27, 2012 Author Share Posted February 27, 2012 Could you make a simple script to reproduce the issue ? I think the fact that excel is opening 2 files in separate processes is not the problem.Opening 2 files in separate Excel application/processes (like _ExcelBookOpen does) cause the VLOOKUP functions to return #N/A. I think that it's an Excel limitation, as the same (analogous) functions work if I open the same workbooks in the same application/process and even if I keep the second workbook closed.With other tests I arrived to this conclusion:- the $oExcel object returned by AutoIT doesn't identify a workbook but it identifies the application/process, so that if you open multiple workbooks (files) in the same application/process, it doesn't identify any specific workbook- the AutoIT Excel UDF functions simply points to the ACTIVE workbook in the given object (Excel application/process). Therefore if you want to work with multiple workbooks in the same Excel application/process, using the Excel UDF function, the (only?) solution is to change the active workbook before any time you want to work with other workbookDo you agree? Link to comment Share on other sites More sharing options...
Juvigy Posted February 27, 2012 Share Posted February 27, 2012 I am always opening my excel files in separate process and neevr had issues with VLOOKUP. What exactly is your VLOOKUP code ? The _ExcelBookOpen had some issues with activeworkbook/Application implementation but maybe i have fixed it manually long time ago. 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