MAYDAY - Close An Already Open File

[get connected]

Hi all.

I am having issues with people not closing an important file in excel.

I have a new vb.net based application that i want to close the excel file when i prompt it to. The application checks for specific named files in a hidden folder on a timer event, (that all works fine) how do i tell it to close a file that is already open.

All threads i have read say things like this:

Dim XLApp As New Application
Dim WkBook As Workbook = XLApp.Workbooks.Open("C:\Book1.xls")
Dim Sheet As Worksheet = CType(WkBook.Sheets(1), Worksheet)

the above types of code always seem to create the file - this will easily allow you to then reference and close it, however i need my vb.net application to check if the user has the file open and then call a sub within that file to close it or simply close the file from my application.

In short i need a piece of vb.net code to check if this pc has excel running book1.xls and if it does to close book1.xls leaving the excel application window alone.

is this possible ????

[Fungus1487]

Im not sure you will be able to check if excel has a specific file open but you could easily close all Excel instances from your application with something along the lines of.

Dim instances() As Process = Process.GetProcessesByName("EXCEL.EXE")
For Each instance As System.Diagnostics.Process In instances
	instance.Kill()
Next

Note that this will close ALL instances of excel that are open.
You could always prompt the user to make sure they want to do this before hand as well as first check if you can open the file and only close all excel instances if opening fails.

[get connected]

Thanks for that as a solution, the only issue i have there is two of the main people causing the problem usually have multiple spreadsheets open and also leave their desk on a regular basis. So shutting down everything would cause an issue, then when they are away from their desk they would not be able to allow closure.

Is it not possible to do something like

1.
      Dim instances() As Process = Process.GetProcessesByName("EXCEL.EXE","Book1.xls")
   2.
      For Each instance As System.Diagnostics.Process In instances
   3.
      instance.Kill()
   4.
      Next

??????

[Fungus1487]

Nope. A process is just a pointer. Same as your processes tab in task manager.

There may be a way using the process Id to connect to live instances of excel. Ill take a quick look.

[get connected]

Ok I've been looking at this in a new way.

Is it possible to say something on the lines of

Dim wbName as String = "Book1.xlsm"
 Dim wbBook As Microsoft.Office.Interop.Excel.Workbook

 For Each wbBook In Process.GetProcessesByName("excel.exe")
 If wbBook.Name = wbName Then
 wbBook.Activate()
 Exit Sub
 End If

also when i tried the code you posted for some reason it always came back with ::

instances {length=0}

i have five workbooks open and saved to desktop
books 1 - 3 in excel window 1
books 4 & 5 in excel window 2

many thanks for your quick and informative responses.
Anything i can learn is a bonus, so thanks for the help.

[Fungus1487]

You will need to check the exact instance name under the processes tab in task manager, it will be something like EXCEL.exe

As with your posted code there is no way to cast from a Process to an Application instance. The Workbook is a wrapper around the api calls required to talk to excel whilst the Process is just a pointer that an Application is in memory.

I'm afraid I have had little success finding any way to connect to an already open instance of Excel.

You may be able to use Interaction.GetObject to return instances of excel that are open but I'm afraid it looks as though it is quite random to which instance you will return.

[lolafuertes]

The approah to find the running processes is good to know if there is an excel instance running. Else every thing is OK.

I there is one (or more) running instance(s), The VisualBasic namespace offers the getobject method to get an instance of the running program. No matter wich one.

Once acquired, you should forcibily close any open workbook, without saving the data. (this will help the user to learn to save their work before leaving the desk)

You must then close the Excel object, and release it, and wait for a few( 5 to 10) seconds to allow the procesor to end the application, and redo the entire process until excel has no running instance(s).

Another approach maybe using old DDE techniques (MSDN search DDE and Visual Basic)

Hope this helps.

[get connected]

Thanks for all the looking Fungus1487 it has been very much appreciated.
I will continue to battle on with this annoying issue - if only stupid end users would simply learn to close a workbook. LOL......
If at some point you find anything that may help please do let me know.

Lolafuertes - i would forcebly close all instances of excel - however to people that are causing this issue are managers and they have several very important sheets open at once, if i caused them to lose any data because of it i would lose my job...
While i agree they should simply close the file we do have to remember that even as managers they still fall into the stupid end user catagory and as such don't always do what they are meant to with simple instructions.

I am looking for a way of referencing an excel workbook by name EG ("Book1.xls") from my external application that did not create that instance of excel.
to be able to close only ("Book1.xls") not the process ("Excel.exe") containing the workbook.

I am running office 2007 and microsoft visual basic 2010 express.
DDE is not supported in the new vb.net and all other things i have found only work if you create the workbook from a vb.net application. This is not what is happening here.
Any help is greatly appreciated.

[get connected]

Fungus1487 you are a genious.

I just tried the following......

Private Sub Button1_Click() Handles Button1.Click
Dim WORKING = Interaction.GetObject("C:\Documents and Settings\username\Desktop\Book1.xlsm")
WORKING.close(False)
End Sub

this closes the workbook and only the workbook - it leaves the excel application window in tact and any other workbooks open within it in tact also.

It always seems to be the way that the job gets done in a simple line of code.

Thank you so much for all your help.

This thread will remain open for now just incase i get a knock on problem.

[get connected]

OK here we go again...

Interaction.GetObject is also causing my system to open the file if it is not already open. - This could cause a knock on effect - so although if it is open it closes it, i still need to be able to determine whether or not it is open on the pc - any ideas???

[RDSchaefer]

Why are you going through all this effort for 2 people? This is more of a mgt issue than a coding issue.

[get connected]

I agree this is more of a management issue than a code issue - however the two people causing most of this head ache are management.

They excuse themselves from any and all wrong doing by staiting that they deal with so much on a daily basis that shutting a spreadsheet doesn't rank highly on a to do list when leaving their desks or in most cases whilst staying at the desk.

I built in a new workbook that lets them know that the timer had closed the main workbook for them as they left it open. Too Subtle me thinks as had no real impact.
I built a self closing timer function into the workbook 3 minute countdown that is reset on relevant keystrokes or button clicks - they found ways of stopping it.
I built a timer that activates on no imput for 'x' minutes that checks the first timer - they found a way of breaking or interrrupting both.
This new application is in vb.net rather than excel vba - it is self opening and cannot be closed (except for a very robust and heavily tested internal timer structure) this will check if they have the workbook in question open and then close it. As with most code the problem is the STUPID END USER.....
It seems that you can try to cover all the bases when creating code but the end user will always be either so insanely stupid that they break it or must be some sort of super genious code hacker savant that can predict and counter any code ever devised.

I feel that the latter is less likely while still possible (in extreme and rare instances)

My main issue is that i am more than a little stubborn and will endeavour to prove i can out think these fools and make something that they can't get around.

[lolafuertes]

Normally, when an excel file is open, a file starting with ~$ and the name of he workbook exists on the same directory where the workbook exists. You can easely chek if thei file exists with a

Dim MyFile as new System.IO.FileInfo("~$Book1.xslm")
If MyFile.Exists Then
'
' Close it here
'
End If

Hope this helps

[zinnqu]

There is a simple solution to this all together. Add a timer into the workbook that auto-closes the workbook after x time of inactivity. here is the code to add to the VBA side of the workbook to fix the issue

'Set Timer
Private Sub Workbook_Open()
    EndTime = Now + TimeValue("00:10:00")
    RunTime
End Sub
'reset timer with changed event
Private Sub Worksheet_Change(ByVal Target As Range)
    If EndTime Then
        Application.OnTime _
          EarliestTime:=EndTime, _
          Procedure:="CloseWB", _
          Schedule:=False
        EndTime = Empty
    End If
    EndTime = Now + TimeValue("00:10:00")
    RunTime
End Sub
' If nothing is changed close workbook
Public EndTime
Sub RunTime()
    Application.OnTime _
      EarliestTime:=EndTime, _
      Procedure:="CloseWB", _
      Schedule:=True
End Sub

Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

This code is compliments of http://www.ozgrid.com/forum/showthread.php?t=32949&page=1

This is just what you need to fix the issue. Also this will shutdown the workbook without saving, so warn the People before you implement it.

[get connected]

Thanks for that lolafuertes i will look at that code to see if it helps out.

Zinnqu - much as i appreciate the response if you read through my postings you will notice that i have actually built a timer event into the workbook set for a 3 minute countdown, there is also a secondary timer function that kicks in when the pc has had no input for 5 minutes - the secondary checker version checks the first timer and will close the workbook if it is not running. However they seem to have found ways of stopping both these timers. This is why i have been asking about remotely closing an excel workbook from another application that i have built in vb.net that they cannot close.

[zinnqu]

For some reason, when I posted this yesterday, your comment about the timing was not there. So I apologize for that. The other way to fix the workbook is to PW protect the VBA side of the document. Changes to the code are not permitted, and the document is allowed to be edited. Remote closing a file is harder since the ~$ precedes the file name, meant as a backup for an independent access of the file. My question for you at this point, are you an admin for the network? I have an idea that will work outside of that..

[get connected]

Zinnqu - I have password protected and file name safety macros built in to all my work - eg
(below is for demo only)
file path / file name / file extension <> what they should be then
msgbox" this is not an official file and will be terminated"
file.close

with a daisy chain condition to log and kill(file) once closed.

I am not system admin or network admin - work doesn't trust anyone with anything. LOL.

We are on a network on computers with multiple people using several desktops over the course of a week.

Most of my coding uses environ("username") to allow me to define users rights to my apps.

would love to hear any ideas you may have.

Thanks for taking the time to read and respond to my post

[zinnqu]

Trying to see if I follow the thread here... You have an application that opens the excel doc. The doc is on a network drive space. There is many people using your front end program to open this document.

If this is all correct, then try to create dll. The Dll will have a capture log. This log will contain, username, and time opened. Then have the username checked against the log and if 3 min (from your post) is met, then the program shuts the whole sheet down. This is similar to a login timing event that is used by Microhell for user activity timeouts. The only thing that you need to consider is a login type action for the front-end of the spreadsheet. Then the issue is not how to shut them down, rather user login activity. I use something similar to this via access and excel, where access is the front end to the excel system, there is logins, and then there is the data. I also have an inactivity routine that is handled in the access program that allows me to kill the login and close their end. All of this is on a network storage space, and the only admin rights are determined by me since I am the originator of the access and excel doc.

[get connected]

Almost -
Any user can open the excel document (only one user at a time) - macro takes care of that.

My new application is used by all users but does not open the excel file - i want to use this application to close the excel file if the user has it open.

I use a hidden file structure to send update links between users on the application so can tell only the application used by the user that has the excel file open to close the excel file.

Sorry if my threads don't make total sense, i have only been coding for a year and find it hard to explain what i want to do sometimes.

So once again thanks for the patience and any help you can give.

[Fungus1487]

Well could you not perform the following...

Dim workbook As String = "C:\MyWorkBook.xls"
Dim isOpen As Boolean

' The method should try to open your workbook, if failure then return true
isOpen = CheckIsWorkbookOpen()

While isOpen
  Interaction.GetObject(workbook).close(False)

  ' Force garbage collection
  GC.Collect()

  ' Check if workbook can be opened again
  isOpen = CheckIsWorkbookOpen()
End While

' Eurika! The loop has completed, the workbook must be closed

This way you keep closing any remaining instances that are open until you hit the jackpot.

And to be honest if the guy wanted to give his management team advice he probably wouldn't have posted to the VB.Net DaniWeb forum.

[get connected]

Thanks again Fungus.
I will give that a try with what i already have and let you know how it works out.

I have no problem telling management that they are doing things wrong and showing them how it should be done as i used to be an assistant manager myself - the problem here is that the two people in question just don't listen or learn.
As far as they are concerned even simple vlookups in excel are complex or like black magic.

So following a simple instruction (which is even laid out on screen) once you have finished the job you open this file for please close the workbook.
This includes things like :
Do not use this file in read only - yep you guessed it they used it all day in read only.
Don't leave filters turned on when you use the workbook to hand out files - yep once again it was done so files got missed.
Make sure you check the file doesn't say things like Book1.xlsm [version 1] or [autorecover] - oh my my yes they couldn't even read the file name was different.

But most importantly if something seems wrong then let me know so i can look into it - and no surprises they leave any issue until i notice it or it has been going on for months.

So i hope this shows in short what i am up against on a daily basis.
What i am trying to do is almost baby sit and maintain files because of laziness and stupidity.

[get connected]

Lolafuertes & Fungus
I believe using a combination of your two pieces of code i have a working premise for the code
please check my logic is sound.

Dim MyFile As New System.IO.FileInfo("C:\-\Desktop\~$Book1.xlsm")
If MyFile.Exists Then
Dim WORKING = Interaction.GetObject("C:\-\Desktop\Book1.xlsm")
WORKING.close(False)
End If

I believe this will check the location for the hidden lockin file and if found will then get the open object and close it.
Would i be correct in thinking that.
As the interaction.getobject opens and closes the object if it isn't open and only closes if is open.

[Fungus1487]

I believe so, I just wonder if there are any circumstances where Microsoft Word will not create the ~$ prefixed system file.