Sub Workbook_Open not called when opening spreadsheet

D

dhputnam

I have a number of spreadsheets with code in the Workbook_Open sub. This
code is not being executed when the spreadsheets are opened. Even when I put
a break point at the beginning of the code, the breakpoint is never
triggered, which leads me to believe that the sub is never being called. The
Workbook_Activate sub has the same problem.

The spreadsheets were created with Excel 2000 and were modified and saved
with Excel 2003. I did not have the problem prior to saving them with Excel
2003. If I create a new spreadsheet in Excel 2003 and copy the worksheets
and VB code into the new spreadsheet, the Workbook_Open sub is called and the
code is executed. However, all the formulas and pivot table references still
point to the original spreadsheet, and it is way too much trouble to try to
change all of these.

Does anyone have any suggestions about how to fix the original spreadsheets
to get around this problem?

Thanks.
 
D

Duncan

I have a number of spreadsheets with code in the Workbook_Open sub.  This
code is not being executed when the spreadsheets are opened.  Even whenI put
a break point at the beginning of the code, the breakpoint is never
triggered, which leads me to believe that the sub is never being called.  The
Workbook_Activate sub has the same problem.

The spreadsheets were created with Excel 2000 and were modified and saved
with Excel 2003.  I did not have the problem prior to saving them with Excel
2003.  If I create a new spreadsheet in Excel 2003 and copy the worksheets
and VB code into the new spreadsheet, the Workbook_Open sub is called andthe
code is executed.  However, all the formulas and pivot table referencesstill
point to the original spreadsheet, and it is way too much trouble to try to
change all of these.

Does anyone have any suggestions about how to fix the original spreadsheets
to get around this problem?

Thanks.

Have you tried the activate event?

D.
 
J

Jim Rech

There is no reason that event code that worked fine in Excel 2000 should not
run in 2003. I'd be happy to look at this problem workbook if you wanted to
send it. Even a version with all the sheets cleared of entries to protect
sensitive data, since the only thing that matters is the event code.

Well, it really shouldn't be if you don't have a huge number of linked
workbooks. Use Edit, Links, point to each link, and use Change Source to
change the link from the old workbook to the current one.

--
Jim
|I have a number of spreadsheets with code in the Workbook_Open sub. This
| code is not being executed when the spreadsheets are opened. Even when I
put
| a break point at the beginning of the code, the breakpoint is never
| triggered, which leads me to believe that the sub is never being called.
The
| Workbook_Activate sub has the same problem.
|
| The spreadsheets were created with Excel 2000 and were modified and saved
| with Excel 2003. I did not have the problem prior to saving them with
Excel
| 2003. If I create a new spreadsheet in Excel 2003 and copy the worksheets
| and VB code into the new spreadsheet, the Workbook_Open sub is called and
the
| code is executed. However, all the formulas and pivot table references
still
| point to the original spreadsheet, and it is way too much trouble to try
to
| change all of these.
|
| Does anyone have any suggestions about how to fix the original
spreadsheets
| to get around this problem?
|
| Thanks.
 
D

dhputnam

Thanks, Jim. I will send you a copy of the spreadsheet with some further
explanation.

Don
 
D

dhputnam

Duncan,

Thanks for the suggestion. I do know that the Workbook_Activate sub is not
being called on the open, but I have not tried to trigger the activate event
programmatically. I have a shortcut that calls the same macro that is called
by the Workbook_Open sub, so I have a manual way to proceed, but the
spreadsheet is supposed to automatically call the macro to refresh the data
in the spreadsheet.

Don
 
J

Jim Rech

Got your workbook, Don, and as I said in my email, the open code did run for
me. Life is strange, Excel stranger<g>.

--
Jim
| Thanks, Jim. I will send you a copy of the spreadsheet with some further
| explanation.
|
| Don
|
| "Jim Rech" wrote:
|
| > There is no reason that event code that worked fine in Excel 2000 should
not
| > run in 2003. I'd be happy to look at this problem workbook if you
wanted to
| > send it. Even a version with all the sheets cleared of entries to
protect
| > sensitive data, since the only thing that matters is the event code.
| >
| > >>and it is way too much trouble to try to change all of these.
| >
| > Well, it really shouldn't be if you don't have a huge number of linked
| > workbooks. Use Edit, Links, point to each link, and use Change Source
to
| > change the link from the old workbook to the current one.
| >
| > --
| > Jim
| > | > |I have a number of spreadsheets with code in the Workbook_Open sub.
This
| > | code is not being executed when the spreadsheets are opened. Even
when I
| > put
| > | a break point at the beginning of the code, the breakpoint is never
| > | triggered, which leads me to believe that the sub is never being
called.
| > The
| > | Workbook_Activate sub has the same problem.
| > |
| > | The spreadsheets were created with Excel 2000 and were modified and
saved
| > | with Excel 2003. I did not have the problem prior to saving them with
| > Excel
| > | 2003. If I create a new spreadsheet in Excel 2003 and copy the
worksheets
| > | and VB code into the new spreadsheet, the Workbook_Open sub is called
and
| > the
| > | code is executed. However, all the formulas and pivot table
references
| > still
| > | point to the original spreadsheet, and it is way too much trouble to
try
| > to
| > | change all of these.
| > |
| > | Does anyone have any suggestions about how to fix the original
| > spreadsheets
| > | to get around this problem?
| > |
| > | Thanks.
| >
| >
 
D

dhputnam

Thanks for your help. It turned out that having the Analysis Toolpack and
the Analysis Toolpack - VBA add-ins installed was causing the Workbook_Open
not to be called. I manually uninstalled these add-ins and modified the
spreadsheet to programmatically install them on the open and uninstall them
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top