_ExcelAttach need help (Yet Another.. ExcelCOM UDF)

[laffo16]

ok, firstly, i use a seperate piece of software, which inputs data into the excel workbook. I then use excelcom to connect to excel to read this info. Now im trying to make two seperate connections to two sep workbooks. i find that both excelcom have restrictions which prohibit this from working. For example, if both workbooks are opened in the same excel.exe process, (so you can see both workbooks from the window menu in excel), the external software can input into both of these workbooks just fine, but excelcom _attach cannot seperate the two workbooks and will access which ever workbook is active out of the two.

hmm... i have also tried connecting the external software & excelcom to two seperate works in two sep excel.exe process's but neither program can detect the 2nd excel.exe instance. Is it possible to fix this problem excel guru's and have excel attach tell the different between the two workbooks in one process?

[laffo16]

ive read the post here:

http://www.autoitscript.com/forum/index.ph...st&p=496673

and it instructs how i can get two instances working using _excelbookopen. it would be nice if i could do this from _excelattach. but this method means that two excel.exe processes are created, what i'm really looking for is attaching to two workbooks from one excel.exe process.

Edited April 30, 2008 by laffo16

[PsaltyDS]

I think you are mistaken about the $oExcel object reference returned by _ExcelAttach(). When you attach by "FilePath", the object returned is for the WORKBOOK, not the Excel instance.

To test, I opened Excel, create two new workbooks (with default 3 sheets each) in the one instance, and saved them as Test1.xls and Test2.xls. Note that each have their own button on the task bar, but they are in the one Excel window, selectable via "Window" on the toolbar, and there is only one instance of Excel.exe in the task manager. I renamed the sheets in Test1.xls as "Test1-1", "Test1-2", and "Test1-3". The sheets in Test2.xls were renamed "Test2-1", "Test2-2", and the third was deleted.

Finally, I ran this:

#include <ExcelCOM_UDF.au3>
#include <Array.au3> ; Only for _ArrayDisplay

$sExcel1 = "C:\Temp\Test1.xls"
$sExcel2 = "C:\Temp\Test2.xls"

$oExcel1 = _ExcelAttach($sExcel1, "FilePath")
$avList1 = _ExcelSheetList($oExcel1)
_ArrayDisplay($avList1, "$avList1")

$oExcel2 = _ExcelAttach($sExcel2, "FilePath")
$avList2 = _ExcelSheetList($oExcel2)
_ArrayDisplay($avList2, "$avList2")

It works fine and shows me the collection of sheet titles from the different workbooks.

[laffo16]

thank you for your help PsaltyDS, let me explain some more, i will be running two autoit programs, i want one instance of autoit to access "$oExcel1" and the other "oExcel2", i dont want either program to access information from other workbook?? (i dont know what to called it, sub workbook? when multiple workbooks riside within one instance of excel). example,

$sExcel1 = "C:\Temp\Test1.xls"
;$sExcel2 = "C:\Temp\Test2.xls"
$oExcel1 = _ExcelAttach($sExcel1, "FilePath")
While 1
    Sleep(200)
    _ExcelCopy($oExcel1, "A1")
    Sleep(200)
    _ExcelCopy($oExcel1, "B1")
WEndoÝ÷ Ù«­¢+ØìÀÌØíÍá°ÄôÅÕ½ÐíèÀäÈíQµÀÀäÈíQÍÐĹá±ÌÅÕ½Ðì(ÀÌØíÍá°ÈôÅÕ½ÐíèÀäÈíQµÀÀäÈíQÍÐȹá±ÌÅÕ½Ðì(ÀÌØí½á°Äô}á±ÑÑ  ÀÌØíÍá°È°ÅÕ½Ðí¥±AÑ ÅÕ½Ðì¤)]¡¥±Ä(%M±À ÈÀÀ¤(%}á±
½Áä ÀÌØíÍá°È°ÅÕ½ÐíÈÅÕ½Ðì¤(%M±À ÈÀÀ¤(%}á±
½Áä ÀÌØíÍá°È°ÅÕ½ÐíÈÅÕ½Ðì¤)]¹

i find the script will operate on both sub-workbooks, i just want them to operate independantly.

-- edit

i got this lovely chunk of gobboly-goop when i tried to edit, seem to have sprouted from knowhere

Sleep(200)

_ExcelCopy($oExcel1, "B1")

WEndoÝ÷ Ù«­¢+ØìÀÌØíÍá°ÄôÅÕ½ÐíèÀäÈíQµÀÀäÈíQÍÐĹá±ÌÅÕ½Ðì(ÀÌØíÍá°ÈôÅÕ½ÐíèÀäÈíQµÀÀäÈíQÍÐȹá±ÌÅÕ½Ðì(ÀÌØí½á°Äô}á±ÑÑ ÀÌØíÍá°È°ÅÕ½Ðí¥±AÑ ÅÕ½Ðì¤)]¡¥±Ä(%M±À ÈÀÀ¤(%}á±

½Áä ÀÌØíÍá°È°ÅÕ½ÐíÈÅÕ½Ðì¤(%M±À ÈÀÀ¤(%}á±

½Áä ÀÌØíÍá°È°ÅÕ½ÐíÈÅÕ½Ðì¤)]¹['/autoit]

Edited May 1, 2008 by laffo16

[PsaltyDS]

I use the script below to test (which can be run twice to try and operate on two workbooks), and here's what happens for me:

When you have multiple workbooks open in a single instance of Excel, it can only operate on one workbook at time.

Functions like _ExcelCopy() and _ExcelPaste() operate on the current workbook regardless of which is defined by $oExcel.

It may be possible to specify the Workbook, but the current UDF doesn't support that, and I don't know how. It is probably waiting for us on MSDN, but I don't have the time to research it right now.

#include <Misc.au3>
#include <ExcelCOM_UDF.au3>

HotKeySet("{ESC}", "_Quit")

Global $sExcel, $oExcel

If _Singleton("ExcelMultiWorkbookTest", 1) Then
    ; First instance
    $sExcel = "C:\Temp\Test1.xls"
Else
    ; Second instance
    $sExcel = "C:\Temp\Test2.xls"
EndIf

$oExcel = _ExcelAttach($sExcel, "FilePath")
If @error Then
    MsgBox(16, "Error", "Unable to attach to Excel for FilePath: " & $sExcel)
    Exit
EndIf

$i = 10
While 1
    _ExcelCopy($oExcel, "A1")
    Sleep(1000)
    _ExcelPaste($oExcel, "A" & $i)
    $i += 1
    Sleep(1000)
WEnd

Func _Quit()
    $oExcel = 0
    Exit
EndFunc   ;==>_Quit

Sorry, no solution for you at the moment, but I learned something from it.

[laffo16]

again, many thanks for your help PsaltyDS, i use _ExcelCopy() quite a bit in my scripts as it is seems to be the fastest way of getting lots of data. so copy & paste use the current workbook regardless? but the other functions such as excelcellread and write use the correct workbooks? i will look into that. i doubt their are any loose ends in the msdn that can make the the copy & paste functions operate as i need. i could winactivate the correct workbook before excelcopy but this will not do.

but what also occured to me why i didnt see it before i dont know, is that with two programs using excelcopy their is a fair chance the data from the clip might go to the wrong program. so i have decided to run the scripts on different machines ^^ haha the easy way out.

[Locodarwin]

The best way to do it is to open both documents (workbooks) via the UDF separately. Don't open more than one workbook per application instance. That way you have an explicit reference for each document.

I wrote the UDF this way on purpose, to dramatically simplify programming Excel. Excel COM is very flexible and will let you specify application objects, workbook objects, and worksheet objects in so many ways it'd make your head spin. Each of these three object types alone are at a different level of the object hierarchy, and thus each would necessitate different ways of writing up the UDF. You'd end up having to make extra commands and put even more parameters into the existing commands.

Well, that's a lot of extra commands and parameters. The library is already pretty big. I find that if you stick to 1 application instance with 1 workbook object per application instance, you get the best overall results with the least amount of coding effort. Play by those rules and the UDF will flawlessly do what you need to do.

-S

[PsaltyDS]

The best way to do it is to open both documents (workbooks) via the UDF separately. Don't open more than one workbook per application instance. That way you have an explicit reference for each document.

I wrote the UDF this way on purpose, to dramatically simplify programming Excel. Excel COM is very flexible and will let you specify application objects, workbook objects, and worksheet objects in so many ways it'd make your head spin. Each of these three object types alone are at a different level of the object hierarchy, and thus each would necessitate different ways of writing up the UDF. You'd end up having to make extra commands and put even more parameters into the existing commands.

Well, that's a lot of extra commands and parameters. The library is already pretty big. I find that if you stick to 1 application instance with 1 workbook object per application instance, you get the best overall results with the least amount of coding effort. Play by those rules and the UDF will flawlessly do what you need to do.

-S

Thanks for the education on it, Locodarwin.

Any chance it might one day be done like IE.au3, where you drill down to $oForm or $oFrame for some commands vice $oIE? This wouldn't work well if a single instance of Excel.exe can't operate on more than one Workbook/Sheet without "Activating" it first. The context shifting back and forth would be crazy if that's required.

So it might look like:

$oExcel = _ExcelAttach($sXLSFile)

$oWkbk_1 = _ExcelWorkbookGet($oExcel, 0)

$oSheet_1 = _ExcelSheetGet($oWkbk_1, "Second Sheet")

$oWkbk_2 = _ExcelWorkbookGet($oExcel, 1)

$oSheet_2 = _ExcelSheetGet($oWkbk_1, "Third Sheet")

$sData = _ExcelReadCell($oSheet_1, "B2")

_ExcelWriteCell($oSheet_2, "C3)

_ExcelWorkbookClose($oWkbk_1, False)

_ExcelWorkbookClose($oWkbk_1, True)

_ExcelQuit($oExcel)

Excel is not IE, so maybe it can't work that way, though.

Edited May 2, 2008 by PsaltyDS