Programmatically opening workbooks in macros brings up Enable Macr

[Barb Reinhardt]

I've been programmatically opening workbooks using Excel 2003 using the
following statement

Set oWB = Workbooks.Open(oldPath, UpdateLinks:=False, ReadOnly:=True)

Today, for the first time, every time a workbook is opened, I get the "Do
you want to enable macros" question. To make it even more complicated, our
organization is just starting to deploy office 2007, so I'm not sure if any
of these workbooks have been edited using 2007.

I'm thinking that using Application.DisplayAlerts = FALSE before the open
statement should resolve my problem, but I'm wondering why it started now.
These workbooks have had macros for over a year.

Any ideas?

Thanks,
Barb Reinhardt

 

[Barb Reinhardt]

The Application.DisplayAlerts option didn't seem to fix my problem. Help!

Thanks,
Barb Reinhardt

 

[Dave Peterson]

I don't think I've ever seen excel do this.

Just some things to try...

Have you closed excel and restarted?
Have you rebooted your pc?

If you have and neither helped, take a look at AutomationSecurity in VBA's help.

But I don't think I've ever had to use this when using code to open other
workbooks that contain macros.

 

[Barb Reinhardt]

I've not seen it until today. I had to reboot my computer this morning but
will be trying that now. When I add the automation security info to the
code, it opens the workbook but the code just stops running after the
Workbook.Open code.
Thanks,
Barb Reinhardt

 

[Barb Reinhardt]

Dave, I've tried rebooting and have another problem. I don't get the Macro
question, but once the workbook opens, Execution on the code ends. I think
I'm going to need to see if Excel needs to be repaired. Any other
suggestions?

Thanks,
Barb Reinhardt

 

[Barb Reinhardt]

I did find this article in the KB. Not sure if it applies though.

http://support.microsoft.com/default.aspx?scid=kb;en-us;555263

Barb Reinhardt

 

[Dave Peterson]

Do you start the code that opens the workbook by using a shortcut key
combination? If yes, then do you have a shift-key in that shortcut.

If you do, then this applies. Remove the shift-key from the shortcut key
combination and the code should continue.

 

[Dave Peterson]

Closer....<vbg>

Dave, I've tried rebooting and have another problem. I don't get the Macro
question, but once the workbook opens, Execution on the code ends. I think
I'm going to need to see if Excel needs to be repaired. Any other
suggestions?

Thanks,
Barb Reinhardt

 

[Barb Reinhardt]

I didn't run it using a shortcut key and never have. I've tweaked the code
a bit because the file path I'm opening was kinda kludgy, but it still opens
the file and then stops execution.

Thanks for your help thus far.

Barb Reinhardt

 

[Barb Reinhardt]

Dave, it started working again and I didn't change anything that would "fix"
it. I'm guessing poltergeists are on my machine!

Thanks for your help.
Barb Reinhardt

 

[Dave Peterson]

I hate when there's no explanation for the fix. But I do like that it works!

I don't see anything in that line of code that would cause the procedure to
stop.

You don't have anything hidden by "on error resume next" statements do you?
(Just a silly guess.)

Dave, it started working again and I didn't change anything that would "fix"
it. I'm guessing poltergeists are on my machine!

Thanks for your help.
Barb Reinhardt

 

[Charlie]

Make sure you have no Excel sessions open (close them all.) Then start
Excel, go to

Tools --> Macros --> Security ...

and set the security level to "Low", then Exit Excel. Now whenever you open
a workbook that has macros you will not get the warning message. Just be
sure to only open workbooks you trust.

That setting is an "Application Setting" not a "Workbook Setting." In other
words, YOUR version of Excel will not ask you to enable macros but other
users may get the warning, depending on the state of their application
settings. Sending them your workbook after you change an application setting
will NOT change their application settings. Application settings are not
kept in workbooks; they are stored on your box for the benefit of your copy
of Excel.

At our office they have instituted startup scripts that run at bootup (and
sometimes at various times who-knows-when) that set that nasty flag to
"Medium." I'm in and out of my own workbooks all day long so of course I
trust them! What I do is almost never turn off my computer and I almost
always have a copy of one or another workbook open, so if their nasty script
runs when my back is turned, whenever I close a workbook the current state is
saved over whatever they try to set it to!

Regards,
Charlie

 

[Barb Reinhardt]

This is a macro that only I run, and when I change an Application setting
(screenupdating, displayalerts, etc) at the beginning of execution, I
generally change it back at the end. This is something that just started
this week after months of the code working fine. It's most strange.

Barb Reinhardt

 

[Charlie]

So, did you go to Tools --> Options --> Security and check your security
level? Set it to Low to get rid of the dialog box.

 

[Barb Reinhardt]

I don't think you are following me. The dialog box never appeared on
Workbook Open until yesterday and I've been running it this way for over a
year with the security setting at medium.

Barb Reinhardt

 

[Charlie]

Have you talked with your Security Administrator or Network Administrator or
whoever manages your workstations and asked them if there was a Windows
Update or patch or something that would now make your "Medium" setting cause
the pop-up to appear? That's what happened at our office.

Regardless of whether or not someone has changed something, have you tried
setting security to Low to get rid of the message?