VBA programming for Excel

gilead · 09-03-2006, 03:25 PM

Hello folks,

I desperately need some help with a macro I'm writing for Excel:

With the Macro I would like to open another workbook and extract some data to the actual workbook (where the Macro is embedded). It all worked fine until I came across an error in a hidden macro in this other workbook- the error has no impact on the data I would like to retrieve, it just pops up an error message saying "error in hidden workbook macro".
I have found one way to solve this problem: open the other workbook 'manually' through Excel and click on disable macros when asked.
No error comes up and my macro works fine.
It seems like I'm unable to get this written in VBA , as it would smooth out the whole process.

Any ideas how I can open the workbook in VBA without enabling the other macros??

Your assistance is much appreciated.

**Glaswegian** · 09-03-2006, 04:16 PM

Hi

Opening another workbook using VBA will automatically disable any Open Event macros. What is this other macro doing and how is it being initiated? What versions of Excel are you using?

gilead · 09-04-2006, 05:33 AM

Hi Glaswegian,

I'm using MS Excel 2000.
The exact (error) message is :" compile error in hidden module: This workbook" and OK button
When I open the workbook with this error by hand choosing disable macros then I can run the macros in my workbook without problems. It is only when I open the error workbook through VBA that this error comes up. Furthermore after hitting the ok button nothing else happens - I can't even bring my macro to resume with the next line.
I have tried the "on error resume next" method , but this doesn't work either.

The workbook with the error includes loads of pivottables and graphs, wrapped with a menusheet and some macros that get the data to the worksheets in the first place. Unfortunately this workbook is protected and all the macros are hidden/ inaccessible for me- which is fine by me since I only need one pivottable and the data behind it. But this message box stops me accessing the data automatically....

**Glaswegian** · 09-04-2006, 06:24 AM

Hi

I don't think there is a way to disable this programmatically. Since it appears with a 'Compile' error I would suggest that the author needs to fix something! Sorry if that's not much help though.

You could always have a look at the sites of two MVP experts and see if they have any tips on this.

Chip Pearson
John Walkenbach

09-03-2006, 03:25 PM	#1 (permalink)
gilead Registered User Join Date: Aug 2006 Posts: 3 OS: WIN XP	VBA programming for Excel Hello folks, I desperately need some help with a macro I'm writing for Excel: With the Macro I would like to open another workbook and extract some data to the actual workbook (where the Macro is embedded). It all worked fine until I came across an error in a hidden macro in this other workbook- the error has no impact on the data I would like to retrieve, it just pops up an error message saying "error in hidden workbook macro". I have found one way to solve this problem: open the other workbook 'manually' through Excel and click on disable macros when asked. No error comes up and my macro works fine. It seems like I'm unable to get this written in VBA , as it would smooth out the whole process. Any ideas how I can open the workbook in VBA without enabling the other macros?? Your assistance is much appreciated.

09-03-2006, 04:16 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,408 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi Opening another workbook using VBA will automatically disable any Open Event macros. What is this other macro doing and how is it being initiated? What versions of Excel are you using? __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

09-04-2006, 06:24 AM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,408 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi I don't think there is a way to disable this programmatically. Since it appears with a 'Compile' error I would suggest that the author needs to fix something! Sorry if that's not much help though. You could always have a look at the sites of two MVP experts and see if they have any tips on this. Chip Pearson John Walkenbach __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

09-04-2006, 05:33 AM	#3 (permalink)
gilead Registered User Join Date: Aug 2006 Posts: 3 OS: WIN XP	Hi Glaswegian, I'm using MS Excel 2000. The exact (error) message is :" compile error in hidden module: This workbook" and OK button When I open the workbook with this error by hand choosing disable macros then I can run the macros in my workbook without problems. It is only when I open the error workbook through VBA that this error comes up. Furthermore after hitting the ok button nothing else happens - I can't even bring my macro to resume with the next line. I have tried the "on error resume next" method , but this doesn't work either. The workbook with the error includes loads of pivottables and graphs, wrapped with a menusheet and some macros that get the data to the worksheets in the first place. Unfortunately this workbook is protected and all the macros are hidden/ inaccessible for me- which is fine by me since I only need one pivottable and the data behind it. But this message box stops me accessing the data automatically....