check if file is open

Adrcon3

New Member
Joined
Aug 29, 2006
Messages
31
i searched the help files on vba and these forums but couldn't find anything. how would i go and check if a file is open or not?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mrroland

Active Member
Joined
Apr 29, 2002
Messages
294
hi,

try

Function CheckFile(strFile As String) As Boolean

Dim Wbk As Workbook

CheckFile = False

On Error GoTo NotOpen

Set Wbk = workbooks(strFile)

CheckFile = True

Exit Function

NotOpen:
End Function

found on this site
http://www.xcelfiles.com/IsFileOpen.html

cheers,
roland
 

Adrcon3

New Member
Joined
Aug 29, 2006
Messages
31
ohh thanks but i don't get the code lol. is there anyway you can explain it to me?
i get this part
Function CheckFile(strFile As String) As Boolean
Dim Wbk As Workbook
but than everything else doesn't really make sense to me
 

mrroland

Active Member
Joined
Apr 29, 2002
Messages
294
Hi,

soory for the late answer

This another approach with an example
Code:
Function BookOpen(Bk As String) As Boolean
Dim t As Excel.Workbook
On Error Resume Next
'if the code runs into a error, it skips it and continues
Set t = Application.Workbooks(Bk)
BookOpen = Not t Is Nothing
'   if teh workbook is open, then t will hold the workbook object and _
    therefore will NOT be nothing
On Error GoTo 0
End Function
Sub Test()
Dim isopen As Boolean, bookname As String
book
Name = "Test.xls"
isopen = BookOpen(bookname) ' calling the function
If isopen Then
MsgBox ("is open")
Else
Workbooks.Open Filename:= _
    "C:\Temp\Test.xls"
End If
End Sub

HTH

Cheers,
Roland
 

Watch MrExcel Video

Forum statistics

Threads
1,113,810
Messages
5,544,425
Members
410,610
Latest member
renatha prado
Top