Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    opening a workbook (2000)

    I need a piece of code that checks if the workbook named "logbook" is already opened or not, if it is not open then i need it to open it, if it is open then do nothing.

    Thanks for the help

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: opening a workbook (2000)

    The following code should work for you :<pre>Sub OpenWorkBook()
    Dim wb As Workbook
    For Each wb In Workbooks
    If wb.Name = "Logbook.xls" Then
    Exit Sub
    End If
    Next
    Workbooks.Open FileName:="PathNameLogbook.xls"
    End Sub</pre>

    .

    Note that you will probably have to use the full pathname when opening the file.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: opening a workbook (2000)

    Try this:

    <pre>Dim oWB As Workbook
    On Error Resume Next
    Set oWB = Workbooks("logbook.xls")
    On Error GoTo 0
    If oWB Is Nothing Then
    Workbooks.Open FileName:="logbook.xls"
    End If
    Set oWB = Nothing
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: opening a workbook (2000)

    Sorry if anybody got the previous post. I should know by now not to fiddle with code without checking it.

    Here is a Function I use to check if any file is already open. Note that the file name parameter should consist of the complete path and file name.
    The Function returns 0 (zero) if the file is not open and usually 32 (Hex 20) if the file is already open or 2 if the file does not exist.
    I suppose this could be done using the VBA/VB Open statement but I find this method more flexible as I modify it whenever I am checking for specific errors.
    It assumes that you are using the WIN 32 API.

    Public Declare Function OpenFile Lib "kernel32" (ByVal lpFileName As String, lpReOpenBuff As OFSTRUCT, ByVal wStyle As Long) As Long
    Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    Public Declare Function GetLastError Lib "kernel32" () As Long

    Public Const OFS_MAXPATHNAME = 128

    Public Const OF_READ = &H0

    Public Const OF_SHARE_EXCLUSIVE = &H10

    Public Type OFSTRUCT
    cBytes As Byte
    fFixedDisk As Byte
    nErrCode As Integer
    Reserved1 As Integer
    Reserved2 As Integer
    szPathName(OFS_MAXPATHNAME) As Byte
    End Type

    Public Function isFileOpen(pFileName As String) As Long
    Dim hFile As Long
    Dim OfStructure As OFSTRUCT
    Dim x As Long

    OfStructure.cBytes = 136

    hFile = OpenFile(pFileName, OfStructure, OF_READ + OF_SHARE_EXCLUSIVE)

    If OfStructure.nErrCode <> 0 Then
    isFileOpen = OfStructure.nErrCode
    Exit Function
    End If

    x = CloseHandle(hFile)

    If x = 0 Then
    isFileOpen = GetLastError()
    Exit Function
    End If

    isFileOpen = 0

    End Function

    Regards,

    Kevin Bell

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: opening a workbook (2000)

    Thanks a lot guys it worked perfectly. That is exactly what i needed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •