check if file is open

[Adrcon3]

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?

 

[mrroland]

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]

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]

Hi,

soory for the late answer

This another approach with an example

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