Check for Open File in Excel VBA Application 1

[miken127]

I have an application running VBA forms in an Excel workbook. I need to be sure that another workbook is also open in Excel. Is there a function which returns true or false for an open file? Anyone aware of a way of doing this? Thanks.

 

[SkipVought]

Hi,

You can use...

On Error Resume Next

and test the Err.Numer after referring to the workbook in question

Workbook("OtherOne.xls").Worksheets.Count
If Eff.Number <> 0 Then
' open the sucker!
End If

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[miken127]

That doesn't quite work in the VBA application; I get an error message trying to run the code. Instead, I put in a statement: workbooks.open filename:="path\filename.xls" and if the file is already open Excel issues a message asking whether I want to reopen it and lose all changes, or leave it alone. That's okay for this application. I can't really find a way to check for another open workbook when I'm in a VBA application.

Thanks for the response, though.

 

[SteveBausch]

You can do a For Each loop, test for a specific name.

You can test Workbooks.Count for the desired value.

Sub IsThisWorkbookOpen(LookingForThisWorkbook As String)
Dim OfTheWorkbooks As Workbook
Dim Hits As Integer
For Each OfTheWorkbooks In Application.Workbooks
    If InStr(1, "LookingForThisWorkbook", OfTheWorkbooks.Name, vbTextCompare) = 1 Then
        MsgBox "The " & LookingForThisWorkbook & " workbook was found", , "Found It!"
        Hits = Hits + 1
    End If
Next

If Hits = 0 Then
    MsgBox LookingForThisWorkbook & " was not found", , "Where is It?"
End If
End Sub

You should be able to "run with" this code; customize it to suit your needs. You may want to query the user if they want to open the book in question. In that case, you would want vbCancel as a button of the MsgBox.

You might want to test for an exact name, or any workbook that starts with/ends with a phrase.

Have fun,
Steve

 

[miken127]

This is a nice procedure. I would note, though, that the expression LookingForThisWorkbook in the InStr function should not be in quotes.

Actually, in this particular application, the workbook I am looking for is blank. I copy data over to it for later printing. Since the application may want to do this for several seaparate data operations, where I don't want the previous data anymore, I decided to just put in an open statemeent whenever the forms are reset. If the workbook doesn't exist an error condition rises and I create the workbook. If it exists but isn't open, then it is opened. And if it's already open, Excel issues a warning that the workbook is open and if I reopen it all changes will be lost, which is what I want anyway.

But I like your subroutine to look for a specific workbook by name and will keep it handy.