Jump to content
Sign in to follow this  
sigil

_ExcelBookAttach() attaches when Excel isn't running

Recommended Posts

sigil

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.

Share this post


Link to post
Share on other sites
PsaltyDS

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

Share this post


Link to post
Share on other sites
sigil

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.

Share this post


Link to post
Share on other sites
PsaltyDS

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

: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

Share this post


Link to post
Share on other sites
sigil

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.

Share this post


Link to post
Share on other sites
Juvigy

What version of excel do you have?

Share this post


Link to post
Share on other sites
sigil

What version of excel do you have?

Excel 2003, running on Win XP.

Share this post


Link to post
Share on other sites
PsaltyDS

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

Share this post


Link to post
Share on other sites
sigil

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.

Share this post


Link to post
Share on other sites
Spiff59

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

Share this post


Link to post
Share on other sites
sigil

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.

Share this post


Link to post
Share on other sites
Juvigy

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

Share this post


Link to post
Share on other sites
Spiff59

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.

Share this post


Link to post
Share on other sites
Juvigy

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

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

Share this post


Link to post
Share on other sites
Spiff59

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

Share this post


Link to post
Share on other sites
sigil

@Juvigy: Thanks, that code solves the problem.

BTW, how do I open the workbook as invisible?

Share this post


Link to post
Share on other sites
Spiff59

I chopped this out of a working script...

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

Share this post


Link to post
Share on other sites
sigil

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

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  

×