Jump to content

_ExcelBookAttach() attaches when Excel isn't running


Recommended Posts

My script should run _ExcelBookAttach() for a certain filepath and then if @error is set, should open the file using _ExcelBookOpen(). But no matter if the workbook is open or not, _ExcelBookAttach does not return an error. The problem may be with ObjGet(), because that isn't returning an error either.

Here's some test code, mostly taken from _ExcelBookAttach():

_filecreate("c:\test.xls")
$obj=_excelbookattach("c:\test.xls")
msgbox(0,"attached to Excel?",@error)
$o_Result = ObjGet("", "Excel.Application")
If @error Or Not IsObj($o_Result) Then
 ConsoleWrite("--> Warning, No existing Excel.Application object" & @CRLF)
 SetError(1, 1, 0)
EndIf
msgbox(0,"did ObjGet find Excel?",@error)

@error should be 1 in both cases, because Excel is not running and the file is not open. But it actually returns 0 both times.

Link to comment
Share on other sites

I run this:

#include <Excel.au3>
#include <File.au3>

_FileCreate("c:\test.xls")
$obj = _ExcelBookAttach("c:\test.xls")
ConsoleWrite("attached to Excel?  " & @error & @LF)
$o_Result = ObjGet("", "Excel.Application")
If @error Or Not IsObj($o_Result) Then
    ConsoleWrite("--> Warning, No existing Excel.Application object" & @CRLF)
    SetError(1, 1, 0)
EndIf
ConsoleWrite("did ObjGet find Excel?  " & @error & @LF)

And get this:

>Running:(3.3.6.1):C:\Program Files\AutoIt3\autoit3.exe "C:\Temp\Test.au3"    
attached to Excel?  1
--> Warning, No existing Excel.Application object
did ObjGet find Excel?  1
+>18:09:24 AutoIT3.exe ended.rc:0
>Exit code: 0    Time: 1.145

:mellow:

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

I run this:

#include <Excel.au3>
#include <File.au3>

_FileCreate("c:\test.xls")
$obj = _ExcelBookAttach("c:\test.xls")
ConsoleWrite("attached to Excel?  " & @error & @LF)
$o_Result = ObjGet("", "Excel.Application")
If @error Or Not IsObj($o_Result) Then
    ConsoleWrite("--> Warning, No existing Excel.Application object" & @CRLF)
    SetError(1, 1, 0)
EndIf
ConsoleWrite("did ObjGet find Excel?  " & @error & @LF)

And get this:

>Running:(3.3.6.1):C:\Program Files\AutoIt3\autoit3.exe "C:\Temp\Test.au3"    
attached to Excel?  1
--> Warning, No existing Excel.Application object
did ObjGet find Excel?  1
+>18:09:24 AutoIT3.exe ended.rc:0
>Exit code: 0    Time: 1.145

:mellow:

I just ran that and got the following console output:

>   
attached to Excel?  0
did ObjGet find Excel?  0
>Exit code: 0    Time: 0.997

I am very puzzled.

Link to comment
Share on other sites

Did you check TaskManager to see if there might be an instance of Excel.exe still running?

:mellow:

I checked TaskManager. There was an Excel instance; I ended it, ran the script, got the same results, checked TaskManager again and found that running the script had created a new Excel instance.

Link to comment
Share on other sites

You might want to check out this post, and the conversation that follows about applications vs document objects in the ROT.

:mellow:

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

You might want to check out this post, and the conversation that follows about applications vs document objects in the ROT.

:mellow:

Thanks for bumping the thread. It sounds like Spiff59 was having the same problem that I'm experiencing, where ObjGet is acting like ObjCreate.

I worked with your code segment (the one where you're getting different console output than me) and got an odd result: when I tried to write to a cell on test.xls and save it, test.xls doesn't get modified. But I know the save is happening, because another Excel sheet that I have set to calculate on save went through its calculations.

Very perplexed. What I want to accomplish is this: If the sheet I'm looking for is open, attach to it and write some data. If it's not open, then open it, write to it, and close it.

Link to comment
Share on other sites

Coincidental timing!

I'd just a few hours before you started this thread, made a post in this thread: Yet Another -- ExcelCOM UDF

Regarding one of the shortcomings of _ExcelBookOpen, and a proposed "improved" version.

Unfortunately, I was unable to get around all the nifty pop-ups and notifications that Excel plies us with regarding a files status, so just went with a simple FileOpen($xxx, 1) test to determine if an Excel file was already in use.

Edit: I'd of rather figured out a solution within Excel, but, barring someone opening the file in the fraction-of-a-second between the FileOpen() test and the Excel Open or Attach command, this seems to be functional.

Edited by Spiff59
Link to comment
Share on other sites

Coincidental timing!

I'd just a few hours before you started this thread, made a post in this thread: Yet Another -- ExcelCOM UDF

Regarding one of the shortcomings of _ExcelBookOpen, and a proposed "improved" version.

Unfortunately, I was unable to get around all the nifty pop-ups and notifications that Excel plies us with regarding a files status, so just went with a simple FileOpen($xxx, 1) test to determine if an Excel file was already in use.

Edit: I'd of rather figured out a solution within Excel, but, barring someone opening the file in the fraction-of-a-second between the FileOpen() test and the Excel Open or Attach command, this seems to be functional.

I'm not getting useable results from the FileOpen() test; most open workbooks return 1.

Link to comment
Share on other sites

Check this out:

oExcel = ObjGet("", "Excel.Application")
If IsObj($oExcel) = 0 Then Exit ;if excel is not running - quit
           For $element In $oExcel.Application.Workbooks
               If $element.FullName <> "c:\test.xls" Then
                   MsgBox(0,"Not found",$element.FullName)
               Else  
                    MsgBox(0,"Found",$element.FullName)
                EndIf
           Next
Exit
Link to comment
Share on other sites

I'm not getting useable results from the FileOpen() test; most open workbooks return 1.

That's odd. FileOpen() with the "1" option, so far, has worked fine on our workbooks to give me advance notice as to the availability of an excel file.

The post from 2 years ago was an interesting one. It was my first interaction with the wise and not-very-PC Valik. It was illuminating to learn that ObjGet(), when called with the filename parameter, will automatically execute an ObjCreate() if it is unsuccessful. Yet, when called with the classname parameter populated, ObjGet() does not do an ObjCreate() and will instead return an @error condition. I was hoping for some solution that would allow the unsuccessful filename-type ObjGet() to fail. I still think it is a behavior of ObjGet() that ought to be documented.

Link to comment
Share on other sites

oExcel = ObjGet("", "Excel.Application")

This line from my code above doesnt execute ObjCreate if excel is not running.

Right, that's the "application" mode of ObjGet(), it does not execute a hidden ObjCreate(). Were you to call ObjGet() in "filename" mode, like: ObjGet("workbook.xls"), and that file is not open, it will start up Excel anyway. Edited by Spiff59
Link to comment
Share on other sites

I chopped this out of a working script...

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$oExcel.WorkBooks.Open($LedgerPath, Default, 1) ; read-only

This works fine when there are no other workbooks open. But if there are workbooks open besides the one that I want to use in my script, if I set $oExcel.Visible to 0 then all those workbooks become invisible as well because it affects the entire application.

maybe this should be a new thread...

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...