+ Reply to Thread
Results 1 to 9 of 9

Code Checking if workbook is open, is not working

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-08-2005
    Location
    Silicon Valley, CA USA
    MS-Off Ver
    2013 - Using 64 bit system
    Posts
    275

    Code Checking if workbook is open, is not working

    Hi,

    Here is the code:

    HTML Code: 
    Each time I run my code and if DB.xls is open it halts the code.

    Thanks for your help.
    Jaz

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Code Checking if workbook is open, is not working

    If the activeworkbook is DB.xls, there's no need to activaate it. Do you want to know if it's among the open workbooks?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    12-08-2005
    Location
    Silicon Valley, CA USA
    MS-Off Ver
    2013 - Using 64 bit system
    Posts
    275

    Re: Code Checking if workbook is open, is not working

    Quote Originally Posted by shg View Post
    If the activeworkbook is DB.xls, there's no need to activaate it. Do you want to know if it's among the open workbooks?
    I would be adding data to it, so I just don't want to know but activate if its open and add data. If its not open then open it.
    Thank you Shg
    Jaz
    Last edited by jazbath; 05-24-2010 at 08:17 PM.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Code Checking if workbook is open, is not working

    Hello jazbath,

    I am not sure if this is what you are after. This code will test if the file open is open or not. This code can also be used for testing if the file has been opened in a separate instance of Excel (Code from Microsoft article KB213383)
    Function IsFileOpen(filename As String)
        Dim filenum As Integer, errnum As Integer
    
        On Error Resume Next   ' Turn error checking off.
        filenum = FreeFile()   ' Get a free file number.
        ' Attempt to open the file and lock it.
        Open filename For Input Lock Read As #filenum
        Close filenum          ' Close the file.
        errnum = Err           ' Save the error number that occurred.
        On Error GoTo 0        ' Turn error checking back on.
    
        ' Check to see which error occurred.
        Select Case errnum
    
            ' No error occurred.
            ' File is NOT already open by another user.
            Case 0
             IsFileOpen = False
    
            ' Error number for "Permission Denied."
            ' File is already opened by another user.
            Case 70
                IsFileOpen = True
    
            ' Another error occurred.
            Case Else
                Error errnum
        End Select
    
    End Function
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  5. #5
    Forum Contributor
    Join Date
    12-08-2005
    Location
    Silicon Valley, CA USA
    MS-Off Ver
    2013 - Using 64 bit system
    Posts
    275

    Re: Code Checking if workbook is open, is not working

    Quote Originally Posted by Leith Ross View Post
    Hello jazbath,

    I am not sure if this is what you are after. This code will test if the file open is open or not. This code can also be used for testing if the file has been opened in a separate instance of Excel (Code from Microsoft article KB213383)
    Function IsFileOpen(filename As String)
        Dim filenum As Integer, errnum As Integer
    
        On Error Resume Next   ' Turn error checking off.
        filenum = FreeFile()   ' Get a free file number.
        ' Attempt to open the file and lock it.
        Open filename For Input Lock Read As #filenum
        Close filenum          ' Close the file.
        errnum = Err           ' Save the error number that occurred.
        On Error GoTo 0        ' Turn error checking back on.
    
        ' Check to see which error occurred.
        Select Case errnum
    
            ' No error occurred.
            ' File is NOT already open by another user.
            Case 0
             IsFileOpen = False
    
            ' Error number for "Permission Denied."
            ' File is already opened by another user.
            Case 70
                IsFileOpen = True
    
            ' Another error occurred.
            Case Else
                Error errnum
        End Select
    
    End Function
    Hi Leith,
    thanks for posting the code. I am not looking for something like this. I am running a code to collect data from a run sheet and put into DB.xls. So if DB.xls is already open I am trying to have the code just select DB. If not open DB.xls to input data.
    Thank you
    Jaz

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Code Checking if workbook is open, is not working

    Hello jazbath,

    Will you be in Excel when you do this check for DB.xls?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1