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
thank you for your help. I tried this code but it opens wb2 although it is already open.
I think my codes should work if you change in my codes.....
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
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
Thanks Steve,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'.
For Each wb In [B]Application.[/B]Workbooks Debug.Print wb.Name Next