Deactivating auto macros

[Guest]

Hi everyone,

Is it possible to deactivate auto macros when I open a workbook in VBA?

Thanks.

 

[Chip Pearson]

Hold down the SHIFT key when you open the workbook.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:[email protected]...

 

[Guest]

Hi Chip,

I know that trick, but my macro opens 60 workbooks from a folder and I want
to deactivate those macros in VBA.

Workbooks.Open Filename:=strTemplatePath & strTemplateName

Thanks.

 

[Chip Pearson]

The Auto_Open macro will not run if the workbook is opened by
VBA. To disable the Workbook_Open event procedure, use

Application.EnableEvents = False
' open the workbook
Application.EnableEvents = True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




message

 

[Guest]

Hi Chip,

This seems to do the job. I will check it out a bit more tomorrow.

Thank you for your time.

 

[Guest]

Can you tell me how to open the workbook by VBA? I have incorrect logic in
the macro which is run in the open event, so my macro updates the wrong
sheet. I don't want to have to retype all the correct informaton in this
sheet so I'd like to disable this macro and then fix it.

 

[Guest]

Hi Jebeh -

I know you posted this awhile ago, but I found Chip Pearson's answer useful
and saw your post so I decided to try to help.

The general was to open a workbook in VBA is

Workbooks.Open(fileName)

As far as disabling macros to open the workbook, you can probably get away
with

Application.EnableEvents=False
Workbooks.Open(fileName)


However, here is an even fancier way that I came up with and works really
well. This uses Excel Automation. Try This:

Sub test_Automation_Open_WB()
Dim XL As Excel.Application
Dim wb As Workbook
Dim fileName As String
Dim wb_Name As String

wb_Name = "TEST__someStuff.xls"

fileName = ThisWorkbook.Path & "\" & wb_Name

Set XL = CreateObject("Excel.Application")

With XL
.EnableEvents = False
.Visible = True

Set wb = XL.Workbooks.Open(fileName, False)
End With
End Sub

This should easily do all you need. It will open a new application of Excel
and then your workbook and will suppress all workbook.open events and pop-ups
to disable macros, etc.

Hope that helps,

Best Regards,

Chris ([email protected])