Hey! Who left that file open?!?!

[Sir Newbie]

Hideeho campers!

Maybe I've been sitting in front of this screen for too long, but I simply can't work out how to check whether a file is open or not.

(Grrrrrr.)

I've looked at way too many esoteric posts (and even a couple of KB's) and the best I can come up with is "workbookopen"... but basically nothing's getting through.

My objective is to save some data into a spreadsheet (called "Backup.xls") that exists elsewhere on the hard drive.

I have worked out whether that file exists via code (after virtually going insane) but that's when I start banging my head against the wall.

Why does life have to be so cruel? :)

Anyone care to help out?

Yeah, alright, I know it's probably simple to do, but I'm a newbie ...

And newbies often can't see the forest for the trees :) )

Thanks in advance!

[Jacob Hilderbrand]

This may work for you. Just change Path variable in the macro to the actual path of the file you want to check. This will also check if the file exists.

Set a reference to the Microsoft Scripting Runtime (Tools | References).


Option Explicit

Enum FileStatus
vbInUse
vbNotInUse
vbDoesNotExist
End Enum

Sub Macro1()

Dim Path As String
Dim Status As FileStatus

Path = ThisWorkbook.Path & "\Test.xls"
Status = IsFileOpen(Path)
Select Case Status
Case Is = vbInUse
MsgBox "The file is in use."
Case Is = vbNotInUse
MsgBox "The file is not in use."
Case Is = vbDoesNotExist
MsgBox "The file does not exist."
End Select

End Sub

Function IsFileOpen(FPath As String) As FileStatus

Dim FName As String
Dim F As File
Dim FSO As New FileSystemObject

'Check if the file exists.
On Error Resume Next
Set F = FSO.GetFile(FPath)
On Error GoTo 0
If F Is Nothing Then
IsFileOpen = vbDoesNotExist
GoTo ExitFunction:
End If

'Try to rename the file. If this fails then the file is in use.
FName = F.Name
On Error Resume Next
F.Name = "A" & FName
On Error GoTo 0

If F.Name = FName Then
IsFileOpen = vbInUse
Else
F.Name = FName
IsFileOpen = vbNotInUse
End If

ExitFunction:

Set F = Nothing
Set FSO = Nothing

End Function

[johnske]

As you've seen, there's more than on way to skin a cat. Here's another...


'//This function is used to determine if a workbook is already open
Function WorkbookIsOpen(WorkBookName As String) As Boolean
'//Returns TRUE if the workbook is open
WorkbookIsOpen = False
On Error GoTo WorkbookIsNotOpen
If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
WorkbookIsOpen = True
Exit Function
End If
WorkbookIsNotOpen:
End Function


The function is used in this fashion...



Option Explicit

Sub OpenWorkbook()
If WorkbookIsOpen("C:\Windows\Desktop\Book1.xls") Then
Workbooks("C:\Windows\Desktop\Book1.xls").Activate
Else
Workbooks.Open ("C:\Windows\Desktop\Book1.xls")
End If
End Sub

[Jacob Hilderbrand]

I was thinking that the file could be in use by someone else on a network (for some reason).

[Sir Newbie]

Wow, a potential light at the end of the tunnel.

Thanks for the replies guys, much appreciated.

Hmmm....

Option Explicit...

Function....

Ummmm.

I'm just gonna take a few minutes (maybe days, weeks .... years) to analyse your code :)

I'll keep in touch though!

Thanks once again :)

[Sir Newbie]

Well...

Now I know why I'm not employed as an Excel consultant :)

Here's a newbie question for you...

While the above information/code is probably very good (after all you guys are masters/gurus) is there an easier way to achieve my goal?

I was thinking something like this...


'Check to see if the "Backup" file is open, if it is then close it,
'so that it can be saved with a spreadsheet with the same name.
If Workbooks.Open("C:\WINDOWS\Desktop\PDA Order Information\Backup.xls") = True Then
Workbooks.Close ("C:\WINDOWS\Desktop\PDA Order Information\Backup.xls")
Else
End If


Maybe this is too simplistic.

Maybe they need to develop a programming language called "Very Basic for Applications."

Any thoughts?

Anyone?

[johnske]

Option Explicit
'using the WorkbookIsOpen function
Sub CloseWorkbook()
'
Const MyBook As String = "C:\WINDOWS\Desktop\PDA Order Information\Backup.xls"
'
If WorkbookIsOpen(MyBook) Then Workbooks(MyBook).Close
'
End Sub


Here's another way...


Sub CloseWorkbook1()
'
Const MyBook As String = "C:\WINDOWS\Desktop\PDA Order Information\Backup.xls"
'
On Error GoTo Finish
Workbooks(MyBook).Close '< if the book's already closed the On Error statement kicks in
'
Finish:
End Sub

[Sir Newbie]

Thanks johnske!

I haven't had time to try out your code yet, because I've been working on some other code...


For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
MsgBox "There is an open workbook!"
w.Close savechanges:=True
End If
Next w

I'm going code crazy down here and I need to take a break (and also catch the last little bit of sunshine that's out there - it's a crappy day down here in Sydney).

Thanks for the code :)

I'll keep you informed about any updates, but I'm not too sure when. Maybe today.

Cheers.

Thanks mate!

[Jan Karel Pieterse]

I am probably missing the point, but you cannot close a file that is open on someone else's machine.

[Insomniac]

Just to add, have a look here:
(original discussion)
http://www.xtremevbtalk.com/showthread.php?t=160978

and examples:
http://www.thecodenet.com/articles.php?id=9
http://xcelfiles.homestead.com/IsFileOpen.html

This will tell if the file is open & by who (The Office UserName).
Providing their Office UserName is obvious who it is a simple phone call or whatever to ask them to close it usually works for me.

[johnske]

? Where are these last two posts coming from?? The OP said the file was somewhere on his hard drive - nothing about a network or on someone elses machine - have I missed a post? :dunno

[Jan Karel Pieterse]

johnske: Duh, sorry you are correct of course. I was put off by the code in one of the other replies.

[Sir Newbie]

I'm back!

A big thanks to all the people who have contributed so far :).

Thanks for your patience.

Now ... I may have misled some of you due to the title of this thread. Sorry about that, it didn't even occur to me that it could be ambiguous. My apologies :)

Here's the scenario...

A person is using a workbook. They save this workbook by clicking on a button. When they click on this button, for example, one workbook is saved on the Desktop, while another ("Backup.xls") is saved in a folder called "Backup."

Anyway, thanks to the code provided by "johnske" I discovered some more help (in the VBA help file). Here it is (slightly modified)...


For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
MsgBox "There is an open workbook!"
w.Close savechanges:=True
End If
Next w


This code (correct me if I'm wrong) looks for other instances of open Excel workbooks and then closes them, before continuing to backup/save the current workbook.

This is working, but it isn't 100%.

The thing is, when my overall program is started (and being used) there are no menus, toolbars visible. There are only the buttons that I have created and some columns/cells visible.

So, if (when the program is being used) I open the "Backup" workbook or the saved version of the current workbook (the only thing you can see in terms of 'extras' is the column and row headers), the code identifies them and closes them, which to me is gold. It works!

But....

If I open Excel from the Start > Programs menu (and all the menus etc are visible) then the above code doesn't identify this workbook and therefore doesn't close it, before trying to do a save. So, when it comes to the save/backup part ... I get an error if the user opens either the "Backup.xls" workbook or the saved workbook, using this method...

Run-time error '1004'
Cannot access 'Backup.xls'.

So, in the end, I've kind of solved my initial problem but also identified another.

Whew, can this program get any harder?

Does any of this stuff make sense?

If you want to try to solve the latter problem, feel free :)

But I don't mind if this thread is classified as "Solved."

Thanks for all the feedback/code etc.