vba help - function is workbook open not working

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
958
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I am using below function , to check whether there is already Open workbook , but its not working.
Also what is the meaning of below lines not getting can you add comment it. or Any alternate code check open file.

VBA Code:
 On Error Resume Next
    fileID = FreeFile()az9w
  Open FileName For Input Lock Read As #fileID
    Close fileID
        errNum = Err
    On Error GoTo 0

Sub test()
Dim wbk2 As Workbook

Dim str As String
str = "E:\VBA\009 - Enter Time in Cell.xlsm"

Dim wbk As Workbook
Set wbk = OpenWorkbook(str, False, True)

If wbk Is Nothing Then

    GoTo myend

End If


myend:

On Error Resume Next
wbk.Close False
wbk2.Close False
Set wbk = Nothing
On Error GoTo 0

End Sub


Function OpenWorkbook(ByVal sFilename As String, ByVal updatelinks As Boolean, ByVal ReadOnly As Boolean) As Workbook

On Error GoTo eh

'Check file exists

    If (Dir(sFilename) <> "") Then
        'if workbook is already open inform the user
        If IsWorkBookOpen(sFilename) Then
            MsgBox ("The workbook[" + sFilename + " ] is already open by user [" + Application.UserName + "].Please close the file and run Again.!")
          GoTo Done
        End If
        Set OpenWorkbook = Workbooks.Open(sFilename, updatelinks, ReadOnly)
    Else
   
    MsgBox ("The workbook " & sFilename & " Could not be found.")
   
    End If
   
Done:

Exit Function


eh:

MsgBox Err.Description


End Function


Function IsWorkBookOpen(FileName As String)

    Dim fileID As Long
    Dim errNum As Long

    'Open file and Check for Error
   
    On Error Resume Next
    fileID = FreeFile()   ' from below how this function works can you add comment.
    Open FileName For Input Lock Read As #fileID
    Close fileID
        errNum = Err
    On Error GoTo 0
   
End Function

Thanks
mg
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,834
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and success on your "Queeste" ...
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,141,410
Messages
5,706,293
Members
421,439
Latest member
JordsdoExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top