Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

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


  • Please log in to reply
13 replies to this topic

#1 Imbuter2000

Imbuter2000

    Prodigy

  • Active Members
  • PipPipPip
  • 189 posts

Posted 23 February 2012 - 05:06 PM

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?







#2 DW1

DW1

    Central Scrutinizer

  • Active Members
  • PipPipPipPipPipPip
  • 2,107 posts

Posted 23 February 2012 - 06:14 PM

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.


#3 DW1

DW1

    Central Scrutinizer

  • Active Members
  • PipPipPipPipPipPip
  • 2,107 posts

Posted 23 February 2012 - 06:19 PM

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


#4 Blinky

Blinky

    Wayfarer

  • Active Members
  • Pip
  • 50 posts

Posted 23 February 2012 - 07:53 PM

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.

#5 DW1

DW1

    Central Scrutinizer

  • Active Members
  • PipPipPipPipPipPip
  • 2,107 posts

Posted 23 February 2012 - 08:10 PM

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)

#6 Blinky

Blinky

    Wayfarer

  • Active Members
  • Pip
  • 50 posts

Posted 23 February 2012 - 09:24 PM

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

#7 DW1

DW1

    Central Scrutinizer

  • Active Members
  • PipPipPipPipPipPip
  • 2,107 posts

Posted 23 February 2012 - 09:33 PM

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

#8 Imbuter2000

Imbuter2000

    Prodigy

  • Active Members
  • PipPipPip
  • 189 posts

Posted 23 February 2012 - 10:52 PM

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

#9 Imbuter2000

Imbuter2000

    Prodigy

  • Active Members
  • PipPipPip
  • 189 posts

Posted 23 February 2012 - 10:56 PM

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!

#10 Imbuter2000

Imbuter2000

    Prodigy

  • Active Members
  • PipPipPip
  • 189 posts

Posted 24 February 2012 - 10:26 AM

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)

#11 Imbuter2000

Imbuter2000

    Prodigy

  • Active Members
  • PipPipPip
  • 189 posts

Posted 24 February 2012 - 11:00 AM

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!

#12 Juvigy

Juvigy

    Experimental Drugs Abuser

  • Active Members
  • PipPipPipPipPipPip
  • 1,282 posts

Posted 24 February 2012 - 03:40 PM

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.

#13 Imbuter2000

Imbuter2000

    Prodigy

  • Active Members
  • PipPipPip
  • 189 posts

Posted 27 February 2012 - 06:50 AM

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?

#14 Juvigy

Juvigy

    Experimental Drugs Abuser

  • Active Members
  • PipPipPipPipPipPip
  • 1,282 posts

Posted 27 February 2012 - 02:16 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users