Checking for open workbooks Repeat/Loop VBA code

[Col]

Hello all,
Am running the following code which I found on the internet to see if a
certain workbook was already open;

Function IsWbOpen(wbName As String) As Boolean
Dim i As Long
For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = wbName Then Exit For
Next
If i <> 0 Then IsWbOpen = True
End Function

This works great, - to a point, I can identify a workbook and interrogate
certain cells for information, however when I save the file, Excel inserts
the whole workbook path into the formula for example;
=IF(iswbopen("Test.xls")=TRUE,'[Test.xls]Summary'!$B$1,"No")

Becomes;

=IF(iswbopen("Test.xls")=TRUE,'C:\MyDocs\Excel\[Test.xls]Summary'!$B$1,"No")

So if I then open a workbook with the same name from within a Lotus Notes
email or from another directory the formula becomes useless.
In addition the formula doesn't automatically update when I close the
Test.xls workbook or re-open it.

To summarise what I trying to do, I have a number of offices who need to
send me their reports, I want to open a file and my summary sheet looks at
cell B1 on the Summary tab in their Test.xls file, which contains the office
name and then a separate lookup cell can then look at cell B2 for the value.

Is there a way of firstly locking the formula so Excel cannot tamper with it
and secondly keep repeating the code just like in a regular cell i.e.
=B1+C1 etc.

Thanks very much for any assistance;

Colin.

 

[Dave Peterson]

How about:

=IF(iswbopen("Test.xls")=TRUE,INDIRECT("[test.xls]Summary!$B$1"),"No")

=indirect() will return an error if the sending workbook is not open, but that's
ok with you!

ps.

I'd watch out for differences in upper/lower case, too:

Option Explicit
Function IsWbOpen(wbName As String) As Boolean
Dim i As Long
For i = Workbooks.Count To 1 Step -1
If LCase(Workbooks(i).Name) = LCase(wbName) Then Exit For
Next i
If i <> 0 Then IsWbOpen = True
End Function


pps.

Another way to check to see if a workbook is open without looping through the
workbooks collection.

Option Explicit
Function IsWbOpen(wbName As String) As Boolean
On Error Resume Next
IsWbOpen = CBool(Workbooks(wbName).Name <> "")

Hello all,
Am running the following code which I found on the internet to see if a
certain workbook was already open;

Function IsWbOpen(wbName As String) As Boolean
Dim i As Long
For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = wbName Then Exit For
Next
If i <> 0 Then IsWbOpen = True
End Function

This works great, - to a point, I can identify a workbook and interrogate
certain cells for information, however when I save the file, Excel inserts
the whole workbook path into the formula for example;
=IF(iswbopen("Test.xls")=TRUE,'[Test.xls]Summary'!$B$1,"No")

Becomes;

=IF(iswbopen("Test.xls")=TRUE,'C:\MyDocs\Excel\[Test.xls]Summary'!$B$1,"No")

So if I then open a workbook with the same name from within a Lotus Notes
email or from another directory the formula becomes useless.
In addition the formula doesn't automatically update when I close the
Test.xls workbook or re-open it.

To summarise what I trying to do, I have a number of offices who need to
send me their reports, I want to open a file and my summary sheet looks at
cell B1 on the Summary tab in their Test.xls file, which contains the office
name and then a separate lookup cell can then look at cell B2 for the value.

Is there a way of firstly locking the formula so Excel cannot tamper with it
and secondly keep repeating the code just like in a regular cell i.e.
=B1+C1 etc.

Thanks very much for any assistance;

Colin.