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.

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 <> "")
On Error GoTo 0
End Function



Col wrote:
>
> 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