Programmatically opening workbooks in macros brings up Enable Macr
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 |
Programmatically opening workbooks in macros brings up Enable Macr
The Application.DisplayAlerts option didn't seem to fix my problem. Help!
Thanks, Barb Reinhardt "Barb Reinhardt" wrote: 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 |
Programmatically opening workbooks in macros brings up Enable
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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
Programmatically opening workbooks in macros brings up Enable
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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
Programmatically opening workbooks in macros brings up Enable
I did find this article in the KB. Not sure if it applies though.
http://support.microsoft.com/default...b;en-us;555263 Barb Reinhardt "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
Programmatically opening workbooks in macros brings up Enable
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. Barb Reinhardt wrote: I did find this article in the KB. Not sure if it applies though. http://support.microsoft.com/default...b;en-us;555263 Barb Reinhardt "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Programmatically opening workbooks in macros brings up Enable
Closer....<vbg
Barb Reinhardt wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Programmatically opening workbooks in macros brings up Enable
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 "Dave Peterson" wrote: 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. Barb Reinhardt wrote: I did find this article in the KB. Not sure if it applies though. http://support.microsoft.com/default...b;en-us;555263 Barb Reinhardt "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Programmatically opening workbooks in macros brings up Enable
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" wrote: 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. Barb Reinhardt wrote: I did find this article in the KB. Not sure if it applies though. http://support.microsoft.com/default...b;en-us;555263 Barb Reinhardt "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Programmatically opening workbooks in macros brings up Enable
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.) Barb Reinhardt wrote: 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" wrote: 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. Barb Reinhardt wrote: I did find this article in the KB. Not sure if it applies though. http://support.microsoft.com/default...b;en-us;555263 Barb Reinhardt "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Programmatically opening workbooks in macros brings up Enable Macr
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" wrote: 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 |
Programmatically opening workbooks in macros brings up Enable
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" wrote: 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" wrote: 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 |
Programmatically opening workbooks in macros brings up Enable
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" wrote: 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" wrote: 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" wrote: 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 |
Programmatically opening workbooks in macros brings up Enable
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Programmatically opening workbooks in macros brings up Enable
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? "Barb Reinhardt" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com