If file not open then open a file from same folder

[misiek5510]

What I'm trying to do is to detect if excel file is already open and if it is then to open specific tab and if its not open to open it from same folder and then open specific tab.

My current code opens the file I need and selects the sheet I wanted however if the file is already open then it asks if I want to reopen it.

Sub test()
Workbooks.Open (ThisWorkbook.Path & "\test.xlsm")
Sheets("testing").Select
End Sub

 

[Marc L]

Use IsOpened function like in this thread :​

Comparing Data from multiple spread-sheets and fish out wanted data into a separate spread-sheet organized per tab

I have multiple tabs of data (named AB, BC, CD, EF, FG, GH, HI, IJ, JK, KL, LM, MN, NO, OP, PQ, QR, RS, ST, TU, UV, VW, WX, XY, YZ, Z00, Z01, Z02, Z03) in my "critical numbers spread-sheet" with wanted numbers per application identified in each tab but columns and rows are inconsistent. I have...

www.mrexcel.com

 

[misiek5510]

Can't get it to work with my code..

 

[Marc L]

​

Do not forget to post your code attempt just using the VBA icon …​

 

[misiek5510]

I managed to fix it. For everyone with the same issue in future here is the code:

Function IsWorkBookOpen(Name As String) As Boolean
Dim xWb As Workbook
On Error Resume Next
Set xWb = Application.Workbooks.Item(Name)
IsWorkBookOpen = (Not xWb Is Nothing)
End Function

Sub Sample()
Dim xRet As Boolean
xRet = IsWorkBookOpen("TEST.XLSM")
If xRet Then
Workbooks("TEST.xlsm").Activate
Sheets("SHEET1").Select
Else
Workbooks.Open (ThisWorkbook.Path & "\TEST.xlsm")
Sheets("SHEET1").Select
End If
End Sub

 

[Marc L]

​

Do not forget at least to clear the useless workbook variable …​

 

[misiek5510]

​

Do not forget at least to clear the useless workbook variable …​

Meaning?

 

[Marc L]

​

Set ObjectVariable = Nothing before exiting the procedure, the reason why IsObject function is the best way …​