Results 1 to 7 of 7

VBA code to open an Excel file only if not already open

This is a discussion on VBA code to open an Excel file only if not already open within the Excel Questions forums, part of the Question Forums category; Good morning guys, I am trying to alter my vba code so when I update a filter on a pivot ...

  1. #1
    New Member
    Join Date
    Nov 2009
    Posts
    11

    Smile VBA code to open an Excel file only if not already open

    Good morning guys,

    I am trying to alter my vba code so when I update a filter on a pivot table in one workbook, a second workbook opens with the refreshed data. Everything is working except that once the the workbook as been opened once, I get an error when i run it again - Excel recognises that the file is already open and wants to know whether to overwrite.

    I have written the code below which I was hoping would try to activate the output workbook if open but open it if it it wasn't already open but it doesn't work. It still asks me whether I want to overwrite.

    Can anyone see my problem?

    Code:
    On Error Resume Next: Err.Clear: Dim wb As Workbook
        Set wb = Workbooks("Path to X.xls file"): wb.Activate
        If Err.Number > 0 Then Set wb = Workbooks.Open(Path to "CurrentMonth-TESTING.xls"): 
        If Not wb Is Nothing Then wb.Worksheets("Master").Activate Else MsgBox "File not found", vbInformation: Exit Sub

  2. #2
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Thumbs up Re: VBA code to open an Excel file only if not already open

    I use a function to solve this:~

    Code:
    Function CheckFileIsOpen(chkSumfile As String) As Boolean
    
        On Error Resume Next
        
        CheckFileIsOpen = (Workbooks(chkSumfile).Name = chkSumfile)
        
        On Error GoTo 0
        
    End Function
    The code which calls it goes as follows:~

    Code:
    If CheckFileIsOpen(NameOfWorkbookToBeOpened) = False Then
    Workbooks.Open PathOfWorkbookToBeOpened & NameOfWorkbookToBeOpened
    End If
    Hope that helps.
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  3. #3
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default Re: VBA code to open an Excel file only if not already open

    Further... this is helpful too. It checks to see if the workbook to be opened even exists!

    Code:
    Function CheckFileExists(chkSumfile As String) As Boolean
    
    ' Sets up the object
    
        Set fs = CreateObject("Scripting.FileSystemObject")
        
    ' Checks if folder exists
        
        CheckFileExists = fs.fileexists(chkSumfile)
        
    End Function
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  4. #4
    New Member
    Join Date
    Nov 2009
    Posts
    11

    Default Re: VBA code to open an Excel file only if not already open

    Thanks Airfix9,

    Great response and speed! I took the code you suggested and added in my filenames and it works beautifully.

    I added a condition for when >

    CheckFileIsOpen(NameOfWorkbookToBeOpened) = True

    also so it activates the file in question on a particular sheet.

    Just need to complete the other finishing touches now!

    Thanks again for your help

  5. #5
    New Member
    Join Date
    Oct 2009
    Location
    New Zealand
    Posts
    14

    Default Re: VBA code to open an Excel file only if not already open

    Quote Originally Posted by Airfix9 View Post
    I use a function to solve this:~

    Code:
    Function CheckFileIsOpen(chkSumfile As String) As Boolean
     
        On Error Resume Next
     
        CheckFileIsOpen = (Workbooks(chkSumfile).Name = chkSumfile)
     
        On Error GoTo 0
     
    End Function
    The code which calls it goes as follows:~

    Code:
    If CheckFileIsOpen(NameOfWorkbookToBeOpened) = False Then
    Workbooks.Open PathOfWorkbookToBeOpened & NameOfWorkbookToBeOpened
    End If
    Hope that helps.

    Airfix9

    I've been searching around for exactly this type of scenario, but I'm obviously doing something wrong. I've added in the function exactly as you gave it above, and modified the If statement to the following:

    Code:
    caWorkbook = ActiveWorkbook.Path + "\Master\Archived.xls"
    If CheckFileIsOpen((caWorkbook)) = False Then
        Workbooks.Open (caWorkbook)
        ' Rest of code here    
    Else
        ' Set variable for processing later
    End If
    Whenever I run it, it opens the file with the Save As dialog, even though someone else already has the file open. The Archived spreadsheet has to be located in the Master subfolder.

    A very quick overview of what I'm doing. We have an individual workbook for each team member. Each workbook contains a Summary page and numerous worksheets. An Update macro updates the Summary page with details from each of the worksheets, but when a job is marked as Complete and the date is over 14 days ago, it gets archived out to the Archived workbook. The check I'm trying to do is to ensure that no one else has the Archived workbook open at the same time. If it is already open, it displays a message at the end of the update process. I've tried other methods of checking but they don't seem to work for me.

    I'm also a bit confused with the line. Can you explain what it does as I would rather learn something than just copy the code.

    Code:
        CheckFileIsOpen = (Workbooks(chkSumfile).Name = chkSumfile)
    When I step through this in Debug mode, it shows the following against each bit of code:

    Code:
    CheckFileIsOpen - CheckFileIsOpen = False
    (Workbooks - Workbooks(chkSumfile).Name = 
    (chkSumfile) - chkSumfile = "Archived.xls"
    .Name - Workbooks(chkSumfile).Name = 
    =chkSumfile) - chkSumfile = "Archived.xls"

  6. #6
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default Re: VBA code to open an Excel file only if not already open

    Hi photoman,

    The code only checks to see if the file is already open on your computer (which is the request from the original poster), not if someone else has the file open.

    As for this part:~

    Code:
    CheckFileIsOpen = (Workbooks(chkSumfile).Name = chkSumfile)
    chkSumfile is the variable containing the path of the workbook to be checked. All the code does is ask "is this file open?" as, if not, Workbooks(chkSumfile).Name = chkSumfile would return the value False.
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  7. #7
    New Member
    Join Date
    Oct 2009
    Location
    New Zealand
    Posts
    14

    Default Re: VBA code to open an Excel file only if not already open

    Thanks Airfix9. I specifically wanted one that would detect open files on a network. I did some more Googling and came across a solution that works for me. If anyone is interested, it can be found here:


    Last edited by photoman; Jun 15th, 2010 at 01:52 AM. Reason: Wrapping URL in CODE tags

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com