I need "On Error" help

[GregJG]

I use this code to open a workbook

Set SourceWb = Workbooks.Open("f:\book1.xls")

but, if the workbook is already open, I'm guessing I would need to us
an "on error resume next"

But everything I have tried is not working.

can someone help

 

[Guest]

Since trying to open an already open workbook does NOT throw an error, it can't be trapped with an On Error statement. Rather, the condition is best handled by directly testing for the open book, something like this ...

For Each WB In Workbooks
bWBOpen = (StrComp(wb.FullName, "F:\book1.xls", vbTextCompare) = 1)
If bWBOpen Then
Set SourceWB = WB
Exit For
End If
Next
If Not bWBOpen Then Set SourceWB = Workbooks.Open("F:\book1.xls")

Tom Lavedas
===========

 

[Chip Pearson]

Greg,

You should first test to see if the workbook is already open, and
if not, open it. For example,

Dim WB As Workbook
Dim SourceWB As Workbook
For Each WB In Workbooks
If StrComp(WB.FullName, "H:\Book1.xls", vbTextCompare) = 0
Then
' wb is open
Set SourceWB = WB
Exit For
End If
Next WB
If SourceWB Is Nothing Then
Set SourceWB = Workbooks.Open("H:\Book1.xls")
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com