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
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
Hi,.....
thank you for your help. I tried this code but it opens wb2 although it is already open.
.....
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
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
For Each wb In Application.Workbooks
Debug.Print wb.Name
Next
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