Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent workbook closing...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent workbook closing...
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent workbook closing...
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent workbook closing...
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 -----Original Message----- 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 -----Original Message----- 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 . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent workbook closing...
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 "Dave" wrote in message ... 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 -----Original Message----- 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 -----Original Message----- 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent open excel windows from automatically closing. | Excel Discussion (Misc queries) | |||
How do I prevent all excel applications from closing at once? | Excel Discussion (Misc queries) | |||
Prevent Excel closing all workbook instances when clicking on "X" | Excel Discussion (Misc queries) | |||
closing excel after closing a workbook | Excel Programming | |||
API to prevent closing Excel | Excel Programming |