Lost reference to workbook

[Pete]

Hi all-

I have an application in which I use separate workbooks to store
persistent data. When I open (or create) these "data workbooks", I set
IsAddin = true, so the user is unable to see the data worksheets
directly.

I maintain a global variable DataWb as Workbook to access the single
active data workbook.

The problem is: When I am debugging, and the VBE is "reset", my global
variable DataWb loses its value (that is, it is re-initialized), but
the data workbook itself remains open. Since the data workbook has
IsAddin set, it does not appear in the Workbooks collection.

Does anyone know how to re-establish access to this data workbook
after a reset?

Thanks very much,
Pete

 

[Dave Peterson]

Have an initialization routine that checks that variable:

Dim dataWB As Workbook

Set dataWB = Nothing
On Error Resume Next
Set dataWB = Workbooks("youraddin.xla")
On Error GoTo 0

If dataWB Is Nothing Then
Set dataWB = Workbooks.Open(Filename:="x:\yourpath\youraddin.xla")
End If

 

[Pete]

Hi Dave -

Thanks very much for your reply -- it works.

However I still have a problem. If the workbook in question was
"dirty", then I lose all my changes. When I re-open the file, I see
the message: "Foo.xla is already open. Reopening will cause any
changes you made to be discarded. Do you want to reopen Foo.xla?"

Does anyone have any idea how to handle this problem?

Thanks,
Pete

 

[Dave Peterson]

That surprises me.

Dim dataWB As Workbook

'This portion tries to set your variable to the addin that's already open
Set dataWB = Nothing
On Error Resume Next
Set dataWB = Workbooks("youraddin.xla")
On Error GoTo 0

'This portion is checking to see if that workbook/addin is already open.
If dataWB Is Nothing Then
Set dataWB = Workbooks.Open(Filename:="x:\yourpath\youraddin.xla")
End If

Maybe you could post the version of the code you used.

 

[Pete]

Dave -

Thanks very much -- your code works great! While preparing a small
code example to post, I realized my problem: I was assuming that since
I could not "see" the addin by looping through the Workbooks
collection, then I must re-open the addin (thereby losing any dirty
changes).

I now realize that addins can be accessed by indexing into the
Workbooks collection by workbook name (eg,
Workbooks("youraddin.xla")), yet not by looping through the Workbooks
collection (eg, for i = 1 to Workbooks.Count).

Thanks again for the help. This solves my problem.