checking if some specific workbook is open; if not then it should be opened.

Sinem

Board Regular
Joined
Nov 8, 2015
Messages
63
Hello,


I want to write a code that checks if some specific workbook (wb2) is open already and if not then it should open wb2.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi Simem
There are probably lots of ways to do this.

This way uses the Workbooks Object which is a collection of all Open Workbooks. If your file is not in it then the code opens it!

I assume your file is wb2.xlsx. And i assume it is in the same Folder as the one in which this code is

2 versions, a simplified code and then one with it explained a bit in the ‘Comments


Rich (BB code):
Sub CheckAWonkBookOpenSHimpfGlified()
Dim arrWBNames() As String

Dim Eye As Long
    For Eye = 1 To Workbooks.Count
    ReDim Preserve arrWBNames(1 To Eye)
    arrWBNames(Eye) = Workbooks.Item(Eye).Name
    Next Eye

    If IsError(Application.Match("wb2.xlsx", arrWBNames(), 0)) Then
    Workbooks.Open Filename:=ThisWorkbook.path & "\" & "wb2.xlsx"
    Else
    End If
    
End Sub

'
Sub CheckAWonkBookOpen() 'http://www.mrexcel.com/forum/excel-questions/909919-checking-if-some-specific-workbook-open%3B-if-not-then-should-opened.html
Dim WBToCheck As String: Let WBToCheck = "wb2.xlsx" 'Full Name including Extension ( Bit after and including the .Dot, like .xlsx )
Dim arrWBNames() As String 'Variable for Array of Full Filenames of all open WonkBooks. Dynamic as size will be changed,but if known types so can be dimensioned appropriately

'Get Array of all open WonkBooks through Workbooks Object which has amongst other things listing of all open Files
Dim Eye As Long 'Variable for Wonkbook item number and Loop Bound varible count. ( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
    For Eye = 1 To Workbooks.Count
    ReDim Preserve arrWBNames(1 To Eye) 'increas size of Array for new entry whilst Preserving current contents
    Let arrWBNames(Eye) = Workbooks.Item(Eye).Name
    Next Eye

'Check for File name in Array for Names of all open files
Dim MtchRst As Variant 'Variable for result of .Match. may be a number or an error
Let MtchRst = Application.Match(WBToCheck, arrWBNames(), 0) ' App.Match gives position "along" of   WBToCheck  ,  in arrWBNames()  ,   looking for exact match.   or gives error if no match found
    If IsError(MtchRst) Then ' case no matach found so File not open.. so....
    Workbooks.Open Filename:=ThisWorkbook.path & "\" & WBToCheck ' Open it  ( assuming it is in same Folder as This workbook in  which the code is
    Else 'case Match "worked" - File is open. So do nothing. redundant code
    End If
    
End Sub

Alan

Wonk
 
Last edited:
Upvote 0
This function return false if it couldn't be open, and true if it opens:

Code:
Function openBook(ByVal fileName As String, ByRef resBook As Workbook)
    Dim res As Boolean: res = True

    On Error GoTo errHandler
        Set resBook = Workbooks.Open(fileName)

    On Error GoTo 0

    GoTo done

errHandler:
    MsgBox "Couldn't open file " & fileName
    res = False

    Debug.Print "ErrHandler: couldn't open & fileName"

    Resume Next
done:

    setBook = res
End Function
 
Upvote 0
Hello DocaElstein,

thank you for your help. I tried this code but it opens wb2 although it is already open.
 
Upvote 0
.....
thank you for your help. I tried this code but it opens wb2 although it is already open.
Hi,
What exactly is your file name. ( Including the ___ .xlsx___or___..xls___or___.xlsm __ bit on the end ( The File "extension" I think that is called! )
Alan
Wonk

Do you Know how to use the Watch Window to see what is in the Array of open File names?
 
Upvote 0
Hi DocAElstein,

my workbook is called wb2.csv

This is my code:

Sub Import()

Dim fname As String
Dim fpath As String
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ThisWorkbook
fpath = wb1.Sheets(Sheet1).Range("Path").Value
fname = wb1.Sheets(Sheet1).Range("Name").Value

'Open Workbook
Application.DisplayAlerts = False
Set wb2 = Workbooks.Open(fpath & "\" & fname)
Application.DisplayAlerts = True

'code
'code



This opens wb2. But I want first check if wb2 is already open and if not it should be opened.
 
Upvote 0
Hi Sinem
.....
my workbook is called wb2.csv
This is my code:


Code:
Sub Import()

Dim fname As String    
Dim fpath As String     
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ThisWorkbook
fpath = wb1.Sheets(Sheet1).Range("Path").Value
fname = wb1.Sheets(Sheet1).Range("Name").Value

'Open Workbook
    Application.DisplayAlerts = False
    Set wb2 = Workbooks.Open(fpath & "\" & fname)
    Application.DisplayAlerts = True

'code 
'code
....

I think my codes should work if you change in my codes
"wb2.xlsx"
with
"wb2.csv"
_ ................................................

I assume
Path
and
Name
are named range things and each applies to a specific cell in sheet1 ?

_ 1 ) What exactly is in those cells?

( _ 2) I would advise You to use different names. This is because Name and Path are things often used in VBA Code, so that may lead to some confusion )

_3) You could do something along the lines of k0nda’s code to try and reference the Workbook through the Worksheets Object collection. Then use his error handling idea to open the file should that reference fail. But that is bad practice.
On Error WTF? | Excel Matters

Here is such a code.......,
Rich (BB code):
Sub CheckAWonkbookOpenErrorWonk()
Dim TestName As String: Let TestName = "Anycrap" 'Variable to test for FileName in Workbooks Collection
'
On Error Resume Next 'Stops or "surpresses" VBA "raising an Error Emergency Exceptional State of Being Thing". So it carries on at the line after the error happened as if nothing had happened
    If TestName = Workbooks("wb2.csv").Name Then 'As long as this "works" and returns a name ( other than  "Anycrap" ) we go to Else.... but if this errors in the case of no "wb2.csv" in the worksheets collecrtion we continue at the next code line
    Workbooks.Open fileName:=ThisWorkbook.path & "\" & "wb2.csv" 'We come here if the above errored, and open the file thgat was not found
    Else 'We had "wb2.csv" for Workbooks(  )  to referrence, so no error. But it was not = "Anycrap" so we come here. And do nothing. Redundant code
    End If
On Error GoTo 0 'This "Turns Off" the error Handler. Unecerssary here as end of code does this, but always good practice to do this immeditely you are finished with the Error handler, so it does not "work" again for other unpredictable errors
End Sub

____......but i think you should try to adapt my original code, or any other that someone might offer which does the job without using error handlers. I cannot think of any other than my original code just now
( Any takers? with an alternative? Just to make it interesting! :) and a good learning excersise?)

Alan

Hello wonk
 
Last edited:
Upvote 0
This little piece of code will get the name of the open files within the current instance of the excel application. Maybe that could be used to test against the named range 'name'.

Code:
For Each wb In Application.Workbooks
    Debug.Print wb.Name
Next
 
Upvote 0
Hi
This little piece of code will get the name of the open files within the current instance of the excel application. Maybe that could be used to test against the named range 'name'.

Code:
For Each wb In [B]Application.[/B]Workbooks
    Debug.Print wb.Name
Next

Thanks Steve,
The Application. made me think... i opened two instances of Excel and ran the code from a module in both. And got a different Workbooks list for each code. makes sense ( ( Dropping out the Application. in both and repeating the experiment gave the same results, ( that is to say the same two different lists again ) - but that is just the implicit default thing of VBA effectively sticking the Application. bit in or rerading it as though it was there )

So that reminded me to be a bit more specific in my code... and i guess the OP should take note and run my code from the instance of Excel that the Workbook could be open in, if it is.

Just out of interest, do you know how your code would be modified to show all open files in all instances of Excel, ( regardless of how many might be open? )

Thanks for the contribution. All good learning stuff!

Alan
 
Upvote 0
Thank you DocaElstein,

I your code works. Now I have this :


' Check if workbook is open
On Error Resume Next
Set wb1 = Workbooks(fname)
On Error GoTo 0
If wb1 Is Nothing Then
' If workbook was NOT open, it will open it
' it will use the parameter WbOpen to remember whether the workbook was open or not
Set wb1 = Workbooks.Open(fpath & "\" & fname, ReadOnly:=True)
WbOpen = False
Else
' If the workbook was open, we'll just assign the sheet and WbOpen
WbOpen = True
End If
' If the workbook was NOT open, close it with: If WbOpen = False Then daten.Close SaveChanges:=False
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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
Back
Top