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.
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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:

k0nda

New Member
Joined
Aug 13, 2015
Messages
30
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
 

Sinem

Board Regular
Joined
Nov 8, 2015
Messages
63
Hello DocaElstein,

thank you for your help. I tried this code but it opens wb2 although it is already open.
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
.....
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?
 

Sinem

Board Regular
Joined
Nov 8, 2015
Messages
63
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.
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,722
Office Version
365
Platform
Windows
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
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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
 

Sinem

Board Regular
Joined
Nov 8, 2015
Messages
63
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
 

Forum statistics

Threads
1,078,253
Messages
5,339,110
Members
399,279
Latest member
danidanidaniel

Some videos you may like

This Week's Hot Topics

Top