Problem exporting to Excel if another book is open while exporting

[CoolDude69]

If I want to export data to Excel and I use _ExcelBookOpen() or _ExcelBookNew() I cannot open any Excel books or the script will write to the book that was opened.

Here's an example (part of my script) that does not give any errors unless you close the excel books that where opened by the ShellExecute("C:\_Excel1.xls")

Plus the _ExcelBookOpen/New() is no longer invisible.

The reason why I use the ShellExecute is tu simulate someone that would open an Excel book.

This is the error that I get when the _Excel1.xls is closed while the script is till running:

Any help will be appreciated.

C:\Program Files\AutoIt3\Include\Excel.au3 (456) : ==> The requested action with this object has failed.:

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue^ ERROR

OR

C:\Program Files\AutoIt3\Include\Excel.au3 (456) : ==> The requested action with this object has failed.:

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue

$oExcel.Activesheet^ ERROR

+======================================================

err.number is: -2147418111

err.windescription: Call was rejected by callee.

err.description is: err.source is:

err.helpfile is:

err.helpcontext is: 7209065

err.lastdllerror is: 0

err.scriptline is: 456

err.retcode is: 6619248

+======================================================

expandcollapse popup

#include <Excel.au3>
_Sample1()

MsgBox(0, '#2', 'Sample 2', 3)

_Sample2()

Func _Sample1()

$excel = _ExcelBookOpen('C:\OpenFile.xlsx', 0); blank file for the test -- stops witing on row 300

For $x = 0 To 1000
_ExcelWriteCell($excel, $x, $x, 1)
_ExcelWriteCell($excel, 1000 - $x, $x, 2)
_ExcelWriteCell($excel, $x * 2, $x, 3)
If $x = 100 Then ShellExecute("Excel.exe"); blank file for the test -- nothing is writen
If $x = 300 Then ShellExecute("C:\_Excel1.xls"); blank file for the test -- starts witing on row 301
Next

_ExcelBookSave($excel)

EndFunc ;==>_Sample1

Func _Sample2()

$excel = _ExcelBookNew(0); stops witing on row 600

For $x = 0 To 1000
_ExcelWriteCell($excel, $x, $x, 1)
_ExcelWriteCell($excel, 1000 - $x, $x, 2)
_ExcelWriteCell($excel, $x * 2, $x, 3)
If $x = 100 Then ShellExecute("Excel.exe"); blank file for the test -- nothing is writen
If $x = 600 Then ShellExecute("C:\_Excel1.xls"); blank file for the test -- starts witing on row 601
Next

_ExcelBookSaveAs($excel, 'C:\NewFile.xlsx', "xlsx", 0, 1)


EndFunc ;==>_Sample2

[BrewManNH]

There appears to be an issue in _ExcelWriteCell, it doesn't appear to be using the object passed to the function, but writes to the ActiveSheet.Cells instead.

[water]

I've already stated in the last thread where you posted this question that the Excel UDF always works with the ActiveSheet and this causes the problem.

You can either use the workbooks in sequence, do the Excel COM coding yourself. The rewrite of the Excel UDF I'm doing at the moment could be another alternative but it is in an early alpha state.

[jdelaney]

simple script...requires the 2 books to be present, and open

$oExcel = _ExcelBookAttach(@DesktopDir & "\Book1.xlsx")
$oExcel2 = _ExcelBookAttach(@DesktopDir & "\Book2.xlsx")
For $i = 1 To 10
 $oExcel.Sheets("Sheet1").cells($i,1).Value = $i & "something"
 $oExcel2.Sheets("Sheet1").cells($i,1).Value = $i & "somethingelse"
Next
Sleep(2000)
For $i = 1 To 10
 $oExcel2.Sheets("Sheet1").cells($i,1).Value = $oExcel.Sheets("Sheet1").cells($i,1).Value & " New"
Next
Sleep(2000)
For $i = 1 To 10
 $oExcel.Sheets("Sheet1").cells($i,1).Value = $oExcel2.Sheets("Sheet1").cells($i,1).Value & " Again"
Next

Edited March 4, 2013 by jdelaney