13 replies to this topic

[Imbuter2000]

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?

[danwilli]

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 by danwilli, 23 February 2012 - 06:15 PM.

[danwilli]

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')

[Blinky]

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.

[danwilli]

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)

[Blinky]

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

[danwilli]

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

Haha, my bad /facepalm, gotcha

[Imbuter2000]

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!!

[Imbuter2000]

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!

[Imbuter2000]

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)

[Imbuter2000]

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!

[Juvigy]

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.

[Imbuter2000]

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 workbook

Do you agree?

[Juvigy]

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.