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

[Stepheny2k2]

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?

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

 

[Airfix9]

I use a function to solve this:~

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:~

If CheckFileIsOpen(NameOfWorkbookToBeOpened) = False Then
Workbooks.Open PathOfWorkbookToBeOpened & NameOfWorkbookToBeOpened
End If

Hope that helps.

 

[Airfix9]

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

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

 

[Stepheny2k2]

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

 

[photoman]

I use a function to solve this:~

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:~

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:

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.

    CheckFileIsOpen = (Workbooks(chkSumfile).Name = chkSumfile)

When I step through this in Debug mode, it shows the following against each bit of code:

CheckFileIsOpen - CheckFileIsOpen = False
(Workbooks - Workbooks(chkSumfile).Name = <Subscript out of range>
(chkSumfile) - chkSumfile = "Archived.xls"
.Name - Workbooks(chkSumfile).Name = <Subscript out of range>
=chkSumfile) - chkSumfile = "Archived.xls"

 

[Airfix9]

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:~

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.

 

[photoman]

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:

[URL="http://www.xcelfiles.com/IsFileOpen.html"]h**p://www.xcelfiles.com/IsFileOpen.html[/URL]

 

[monfro]

Hi guys,

I've tried this in my code but I must be doing something wrong because when I run the macro a second time, the code doesn't recognize that the workbook in question is already open.
Can you tell me what to change?

My code:

DIM WB as String
DIM path as String

WB = "NameOfWorkbook.xls"
path = "c:\path\"

If CheckFileIsOpen(WB) = False Then Workbooks.Open path & WB, ReadOnly = True
... rest of code

the function:

Function CheckFileIsOpen(chkSumfile As String) As Boolean

On Error Resume Next

CheckFileIsOpen = (Workbooks(chkSumfile).Name = chkSumfile)

On Error GoTo 0

End Function

I want to open a dozen XLS files (but only if not already open)

 

[JGreen01]

Hello monfro,

More information requested, regarding your question. First, are you opening files on a network, or local computer? Second, you state "I want to open a dozen XLS files (but only if not already open)". So you don't want to re-open the file if opened. However, do you want to return to the file that's been opened, and if not opened open it? I have a piece of vba code using a coded button in one workbook which would take you to a specific worksheet in another workbook (whether open or closed), without receiving Error Messages such as "already open", "open as read only", etc., for it resolves those issues? However, currently written only for files being used by single user, on local machine, not network.

The answers to these two questions, will let me know if my code (.xlsm) would help you. If you believe it would, I'll post it here.

 

[monfro]

Hi JGreen01,

Yes I'm reading the XLS files from a network and yes I only want to open them if not already opened. I'm using the macro several times and if the workbooks a re already opend the macro must be able to get data from those workbooks.

I temporarily solved this by using a query at the beginning of the macro that asks me if the workbooks are open or not but this is not what I want.

thanks again for any help.