Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Open Wkbook Disabling Macros
Hi,
I need to open a workbook with VBA and disable the macros. How would I do this? Workbooks.Open("C:\Documents and Settings\Me\Desktop\New Workbooks\MywkBook.xls") -- Thanks for your help. Karen53 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Open Wkbook Disabling Macros
If you want to stop the events from running when you open a workbook with code you can use
Application.EnableEvents = False Code Application.EnableEvents = True -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi, I need to open a workbook with VBA and disable the macros. How would I do this? Workbooks.Open("C:\Documents and Settings\Me\Desktop\New Workbooks\MywkBook.xls") -- Thanks for your help. Karen53 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Open Wkbook Disabling Macros
Hi Ron,
Thank you for the reply. Sorry, I wasn't clear with what I needed. The workbook being opened comes up with the security window asking to 'Enable' or' Disable Macros'. I need to select 'Disable' with vba. Application.EnableEvents is already false. Is there a way to do this? -- Thanks for your help. Karen53 "Ron de Bruin" wrote: If you want to stop the events from running when you open a workbook with code you can use Application.EnableEvents = False Code Application.EnableEvents = True -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi, I need to open a workbook with VBA and disable the macros. How would I do this? Workbooks.Open("C:\Documents and Settings\Me\Desktop\New Workbooks\MywkBook.xls") -- Thanks for your help. Karen53 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Open Wkbook Disabling Macros
Hi Karen
Look in the VBA help for Application.AutomationSecurity = msoAutomationSecurityForceDisable Then open the file -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi Ron, Thank you for the reply. Sorry, I wasn't clear with what I needed. The workbook being opened comes up with the security window asking to 'Enable' or' Disable Macros'. I need to select 'Disable' with vba. Application.EnableEvents is already false. Is there a way to do this? -- Thanks for your help. Karen53 "Ron de Bruin" wrote: If you want to stop the events from running when you open a workbook with code you can use Application.EnableEvents = False Code Application.EnableEvents = True -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi, I need to open a workbook with VBA and disable the macros. How would I do this? Workbooks.Open("C:\Documents and Settings\Me\Desktop\New Workbooks\MywkBook.xls") -- Thanks for your help. Karen53 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Open Wkbook Disabling Macros
Thanks, Ron.
It works. The problem is it is stopping the macros in the calling workbook as well. Can I stop only the macros in the newly opened workbook? Is it possible? --- Thanks for your help. Karen53 "Ron de Bruin" wrote: Hi Karen Look in the VBA help for Application.AutomationSecurity = msoAutomationSecurityForceDisable Then open the file -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi Ron, Thank you for the reply. Sorry, I wasn't clear with what I needed. The workbook being opened comes up with the security window asking to 'Enable' or' Disable Macros'. I need to select 'Disable' with vba. Application.EnableEvents is already false. Is there a way to do this? -- Thanks for your help. Karen53 "Ron de Bruin" wrote: If you want to stop the events from running when you open a workbook with code you can use Application.EnableEvents = False Code Application.EnableEvents = True -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi, I need to open a workbook with VBA and disable the macros. How would I do this? Workbooks.Open("C:\Documents and Settings\Me\Desktop\New Workbooks\MywkBook.xls") -- Thanks for your help. Karen53 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Open Wkbook Disabling Macros
Ron,
Here's my code. Do I perhaps have it placed incorrectly? Set wbkCopyTo = ThisWorkbook FromwbkPath = Application.GetOpenFilename(Filefilter:="Excel Files,*.xls") If FromwbkPath = False Then Exit Sub 'user hit cancel End If Call GetNamePath(FromwbkName, FromPath, FromwbkPath) 'just the filename wbkCopyFromName = Mid(FromwbkPath, InStrRev(FromwbkPath, "\") + 1) On Error Resume Next Set wbkCopyFrom = Workbooks(wbkCopyFromName) On Error GoTo 0 If wbkCopyFrom Is Nothing Then 'save the current security setting secAutomation = Application.AutomationSecurity 'disable the automatic Security Application.AutomationSecurity = msoAutomationSecurityForceDisable Set wbkCopyFrom = Workbooks.Open(FromwbkPath) 'set security back to original setting Application.AutomationSecurity = secAutomation On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file--in use?" Exit Sub Else -- Thanks for your help. Karen53 "Ron de Bruin" wrote: Hi Karen Look in the VBA help for Application.AutomationSecurity = msoAutomationSecurityForceDisable Then open the file -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi Ron, Thank you for the reply. Sorry, I wasn't clear with what I needed. The workbook being opened comes up with the security window asking to 'Enable' or' Disable Macros'. I need to select 'Disable' with vba. Application.EnableEvents is already false. Is there a way to do this? -- Thanks for your help. Karen53 "Ron de Bruin" wrote: If you want to stop the events from running when you open a workbook with code you can use Application.EnableEvents = False Code Application.EnableEvents = True -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi, I need to open a workbook with VBA and disable the macros. How would I do this? Workbooks.Open("C:\Documents and Settings\Me\Desktop\New Workbooks\MywkBook.xls") -- Thanks for your help. Karen53 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Open Wkbook Disabling Macros
This doesn't sound like excel's usual behavior to me.
If you're opening a workbook from code in another workbook's project, then excel should already trust that workbook. I don't have a suggestion why this is happening to you or how you can avoid it, but something ain't normal. If you create a couple of small workbooks and do a test, do you see the security prompt? Karen53 wrote: Hi Ron, Thank you for the reply. Sorry, I wasn't clear with what I needed. The workbook being opened comes up with the security window asking to 'Enable' or' Disable Macros'. I need to select 'Disable' with vba. Application.EnableEvents is already false. Is there a way to do this? -- Thanks for your help. Karen53 "Ron de Bruin" wrote: If you want to stop the events from running when you open a workbook with code you can use Application.EnableEvents = False Code Application.EnableEvents = True -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi, I need to open a workbook with VBA and disable the macros. How would I do this? Workbooks.Open("C:\Documents and Settings\Me\Desktop\New Workbooks\MywkBook.xls") -- Thanks for your help. Karen53 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Open Wkbook Disabling Macros
Hi Dave,
I want it to trust the opening workbook but not trust the workbook being opened. It does work as you say, it trusts both workbooks. But, I don't want the macros in the newly opened workbook to run. I think now it's not possible, the macos are either on or off for all open workbooks. Another question though, if my second workbook is already open, my code does not process. If I have the code open the workbook, it processes. Do I have something wrong? -- Thanks for your help. Karen53 "Dave Peterson" wrote: This doesn't sound like excel's usual behavior to me. If you're opening a workbook from code in another workbook's project, then excel should already trust that workbook. I don't have a suggestion why this is happening to you or how you can avoid it, but something ain't normal. If you create a couple of small workbooks and do a test, do you see the security prompt? Karen53 wrote: Hi Ron, Thank you for the reply. Sorry, I wasn't clear with what I needed. The workbook being opened comes up with the security window asking to 'Enable' or' Disable Macros'. I need to select 'Disable' with vba. Application.EnableEvents is already false. Is there a way to do this? -- Thanks for your help. Karen53 "Ron de Bruin" wrote: If you want to stop the events from running when you open a workbook with code you can use Application.EnableEvents = False Code Application.EnableEvents = True -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi, I need to open a workbook with VBA and disable the macros. How would I do this? Workbooks.Open("C:\Documents and Settings\Me\Desktop\New Workbooks\MywkBook.xls") -- Thanks for your help. Karen53 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Open Wkbook Disabling Macros
Dave,
Never mind. I found why it wouldn't process if the workbook was already open. Thanks -- Thanks for your help. Karen53 "Dave Peterson" wrote: This doesn't sound like excel's usual behavior to me. If you're opening a workbook from code in another workbook's project, then excel should already trust that workbook. I don't have a suggestion why this is happening to you or how you can avoid it, but something ain't normal. If you create a couple of small workbooks and do a test, do you see the security prompt? Karen53 wrote: Hi Ron, Thank you for the reply. Sorry, I wasn't clear with what I needed. The workbook being opened comes up with the security window asking to 'Enable' or' Disable Macros'. I need to select 'Disable' with vba. Application.EnableEvents is already false. Is there a way to do this? -- Thanks for your help. Karen53 "Ron de Bruin" wrote: If you want to stop the events from running when you open a workbook with code you can use Application.EnableEvents = False Code Application.EnableEvents = True -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi, I need to open a workbook with VBA and disable the macros. How would I do this? Workbooks.Open("C:\Documents and Settings\Me\Desktop\New Workbooks\MywkBook.xls") -- Thanks for your help. Karen53 -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Open Wkbook Disabling Macros
Just to beat a dead horse...
Ron's suggestion of disabling events should stop any code from running in the workbook that's being opened in code. The only other way to run a macro in that second workbook is to explicitly call it. And that's easy to avoid <vbg. Karen53 wrote: Hi Dave, I want it to trust the opening workbook but not trust the workbook being opened. It does work as you say, it trusts both workbooks. But, I don't want the macros in the newly opened workbook to run. I think now it's not possible, the macos are either on or off for all open workbooks. Another question though, if my second workbook is already open, my code does not process. If I have the code open the workbook, it processes. Do I have something wrong? -- Thanks for your help. Karen53 "Dave Peterson" wrote: This doesn't sound like excel's usual behavior to me. If you're opening a workbook from code in another workbook's project, then excel should already trust that workbook. I don't have a suggestion why this is happening to you or how you can avoid it, but something ain't normal. If you create a couple of small workbooks and do a test, do you see the security prompt? Karen53 wrote: Hi Ron, Thank you for the reply. Sorry, I wasn't clear with what I needed. The workbook being opened comes up with the security window asking to 'Enable' or' Disable Macros'. I need to select 'Disable' with vba. Application.EnableEvents is already false. Is there a way to do this? -- Thanks for your help. Karen53 "Ron de Bruin" wrote: If you want to stop the events from running when you open a workbook with code you can use Application.EnableEvents = False Code Application.EnableEvents = True -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Karen53" wrote in message ... Hi, I need to open a workbook with VBA and disable the macros. How would I do this? Workbooks.Open("C:\Documents and Settings\Me\Desktop\New Workbooks\MywkBook.xls") -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Cell in WKBook 1 From WkBook 2 | Excel Discussion (Misc queries) | |||
How can i Enable/Disable CommandButtons from Wkbook Open event ??? | Excel Programming | |||
Disabling Macros | Excel Programming | |||
Disabling macros with Shift+[OPEN] | Excel Programming | |||
Open Method disabling Macros | Excel Programming |