Sign in to follow this  
Followers 0

multiple _ExcelBookOpen open multiple Excel processes :( How to avoid it?

14 posts in this topic

#1 ·  Posted

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?

Share this post


Link to post
Share on other sites



#2 ·  Posted (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 by danwilli

Share this post


Link to post
Share on other sites

#3 ·  Posted

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

Share this post


Link to post
Share on other sites

#4 ·  Posted

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.

Share this post


Link to post
Share on other sites

#5 ·  Posted

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)

Share this post


Link to post
Share on other sites

#6 ·  Posted

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

Share this post


Link to post
Share on other sites

#7 ·  Posted

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

Share this post


Link to post
Share on other sites

#8 ·  Posted

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

Share this post


Link to post
Share on other sites

#9 ·  Posted

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!

Share this post


Link to post
Share on other sites

#10 ·  Posted

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)

Share this post


Link to post
Share on other sites

#11 ·  Posted

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!

Share this post


Link to post
Share on other sites

#12 ·  Posted

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.

Share this post


Link to post
Share on other sites

#13 ·  Posted

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?

Share this post


Link to post
Share on other sites

#14 ·  Posted

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.

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