Sign in to follow this  
Followers 0
Jewtus

If Excel window exists

10 posts in this topic

I'm wondering if there is a way to attach to an open excel object. I've been trying to do this:

$test=ObjGet("", "Excel.Application")
$test.Workbook.Close

But I get an error on the close command. I've also tried it without Workbook and it still errors.

 

What I'm really trying to do is attach to a specific instance of excel (based on a window name or file name or something) and then save it and close it. Anyone have suggestions?

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

I think I might have something that can help. I have a script that runs a spreadsheet in the background and when it's done it gets saved. I had to check for the object to make it work.

;Setting up the workbook
$oExcel = ObjCreate("Excel.Application")
$oBook = $oExcel.Workbooks.Open(@ScriptDir & "\..\Spreadsheet.xlsx")
$oExcel.Visible = False ;Invisible Excel!


;Stuff

;Here is where I checked for the object before saving and closing.
If IsObj($oExcel) Then _Excel_BookSave($oBook)
If @error Then MsgBox(64, "", "Unable to save  spreadsheet.")

If IsObj($oExcel) Then _Excel_Close($oExcel, True, True)
If @error Then MsgBox(64, "", "Unable to close  spreadsheet.")

Let the fun begin!

Edited by Bearpocalypse

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

I'm trying to avoid closing other instances so I'm really looking to attach to a window or filename. This is for a calendar for vacation days that I'm working on. I want to make sure that if they open the file, that they don't already have it open, but I don't want to close any other instances of Excel. So if someone was working on an excel file and then remembered they needed to put in their vacation days, it wouldn't close the other excel file on them. This is how I have my startup on the script:

If WinExists("[CLASS:XLMAIN]","Vacation Calendar") Then
    $test=ObjGet("", "Excel.Application")
    $reopen=MsgBox(4,"Oops..", "It appears you already have the speadsheet open. Would you like to save and reopen the file?")
    If $reopen=6 Then
        MsgBox(0,"Oops..", "Trying to save... but I dont know how...")
                $test.Workbook.Close
    Else
        Exit
    EndIf
    $test.Workbook.Close
EndIf
Edited by Jewtus

Share this post


Link to post
Share on other sites

Please have a look at the Excel UDF in combination with ProcessExist.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Please have a look at the Excel UDF in combination with ProcessExist.

I was actually trying to use _Excel_BookAttach, but I keep getting an error on the line:

$oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)

I'm assuming it relates to the $sCLSID_Workbook variable, but I don't know exactly what is going on. The error description is "Unknown name".

I've tried using Filename, filepath, and title and I get the same result on all of them.

EDIT:

Scratch that. I looked at my process list and there were about a hundred instances of EXCEL.exe so I closed them all and it worked without issue.

Edited by Jewtus

Share this post


Link to post
Share on other sites

Scratch that. I looked at my process list and there were about a hundred instances of EXCEL.exe so I closed them all and it worked without issue.

 

Oh how I don't miss that when I was working on my thing.

Share this post


Link to post
Share on other sites

Oh how I don't miss that when I was working on my thing.

What are you talking about :huh:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

When you run _Excel_BookList you get a list of workbooks in all running Excel instances. Loop through the array and select the needed workbook, then use _Excel_BookAttach to connect to this workbooks and save/close them if needed.

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I ended up going with this:

If ProcessExists('EXCEL.EXE') Then
    If WinExists("[CLASS:XLMAIN]","Vacation Calendar") Then
        $reopen=MsgBox(4,"Oops..", "It appears you already have the speadsheet open. Would you like to save and reopen the file?")
        If $reopen=6 Then
            $oExcel2=_Excel_BookAttach($filePath,'filepath')
            $oExcel2.Save
            $oExcel2.close
        Else
            WinActivate("[CLASS:XLMAIN]","Vacation Calendar")
            Exit
        EndIf
    EndIf
EndIf

Only thing I need to figure out is how to close that excel instance... What it does now is close the workbook, but leaves that instance of excel open.

Share this post


Link to post
Share on other sites

Use something like this:

If ProcessExists('EXCEL.EXE') Then
    If WinExists("[CLASS:XLMAIN]","Vacation Calendar") Then
        $reopen=MsgBox(4,"Oops..", "It appears you already have the speadsheet open. Would you like to save and reopen the file?")
        If $reopen=6 Then
            $oWorkbook=_Excel_BookAttach($filePath,'filepath')
            $oWorkbook.Save()
            $oWorkbook.Close()
            _Excel_Close($oWorkbook.Parent)
        Else
            WinActivate("[CLASS:XLMAIN]","Vacation Calendar")
            Exit
        EndIf
    EndIf
EndI

What your script still needs is some type of error checking after each action with Excel.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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  
Followers 0