Prevent workbook closing...

[Dave]

Excel 2000 SP-3, Win2000

Hi- I've been puzzling on this one for a while and can't
find an answer in the archives- can anyone help?

When Excel starts it opens a default blank workbook. If I
then open a previously saved workbook then the default
workbook is automatically closed before the selected
workbook is openned.

My problem is that this behaviour occurs when openning
files from an add-in, for example:

1) User opens Excel and is presented with the default
workbook.
2) User imediately runs my add-in from a custom menu item
3) My add-in does something like this:
Sub MY_ADDIN
Dim rngR as Range
Dim wsData as Worksheet
Dim strData as String

Set rngR = Selection
Set wsData = Workbooks.Open("C:\Test.xls").Sheets(1)
strData = wsData.Range("A1").Formula
wsData.Parent.Parent.Close(False)
rngR.Formula = strData
End Sub

When the file Test.xls is openned, this has the undesired
effect of closing the default workbook. The range rngR is
lost and I get an Error 424 (Object required) error on the
last line.

How can I prevent the default workbook from being closed
in this situation?

Cheers,
Dave

 

[Frank Stone]

hi,

Putting data in the default blank wb will prevent it from
closing.
add a line to your code like
range("a1").value = 1 'or something like that
do this prior to opening and closing the test workbook.
Regards
Frank

 

[Sharad Naik]

In your add-in just add a code to enter something somewhere in book1, before
opening the test.xls.
Like :-
ActiveSheet.Cells(200,200).Value = 1

With some data entered, Me.Saved is not true and the book1 will not close.
You may add to clear the contents of above cell, after the
test.xls is opened.

Sharad

Sharad

 

[Dave]

Frank, Sharad,

Thanks for the suggestion, I guess this is a good
workaround although it would be good to prevent the
default behaviour in the first place.

Cheers,
Dave

 

[Sharad Naik]

Think of it Dave, preventing the default behaviour itself will be nothing
but another work around.
You will have to either set it on each machine seprately or do it through
the code in your add-in.
So at the end it's just another work around.

Sharad