Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lost reference to workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lost reference to workbook
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.xl a") End If Pete wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lost reference to workbook
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 wrote in message ... 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.xl a") End If Pete wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lost reference to workbook
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.xl a") End If Maybe you could post the version of the code you used. Pete wrote: 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 wrote in message ... 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.xl a") End If Pete wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lost reference to workbook
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text color is lost when using cell reference | Excel Discussion (Misc queries) | |||
formatting of cells used as abs or rel reference is lost | Excel Discussion (Misc queries) | |||
Lost XLQuery reference | Excel Discussion (Misc queries) | |||
lost workbook | Excel Worksheet Functions | |||
name reference got lost? | Excel Worksheet Functions |