Disabling Events
I have a VBA procedure that runs in Outlook. It opens an Excel
spreadsheet (A.xls), reads some data from sheet "B" and then closes A.xls There are macros in A.xls and in my Personal.xls that will execute on open. I do not want that to happen when A.xls is opened from Outlook. I have tried using the EventsEnable = False (which works when one spreadsheet is opening another), but that does not seem to work here. The problem is not the macros in A.xls but the macros in Personal.xls. One of those macros does a lot of fetching from yet other spreadsheets, build menus, and other stuff. If that macro executes, it seems to get confused and generate a compiler error. The Outlook procedure still runs correctly to completion but I would like to eliminate the error. Any help would be appreciated. The relevant VBA code (in Outlook) is below. Thanks ' ' define Excel types ' Dim xLApp As Excel.Application Dim xLBook As Excel.Workbook Dim xLSheet As Excel.Worksheet ' ' traditional definitions ' Dim xLPath As String Dim xLFileName As String ' ' define location and filename of target spreadsheet ' xLPath = "D:\TestArea" xLFileName = "A.xls" ' ' define application object ' set events to false (so macros do not run on startup) ' Set xLApp = CreateObject("Excel.Application") xLApp.EnableEvents = False ' ' open target spreadsheet ' define target worksheet ' xLApp.Workbooks.Open FileName:=xLPath + "\" + xLFileName Set xLSheet = xLApp.Workbooks(xLFileName).Worksheets("B") ' ' perform spreadsheet operations ' ... do various things ... ' ' close spreadsheet ' misc housekeeping ' xLApp.Workbooks(xLFileName).Close --- Message posted from http://www.ExcelForum.com/ |
Disabling Events
I believe automation and enableevents do not work as expected, at least with
Excel 2000: http://support.microsoft.com/default...26&Product=xlw As a workaround can your code close Personal before opening the other workbook? -- Jim Rech Excel MVP |
Disabling Events
Jim: thanks for your reply.
You suggested: "As a workaround can your code close Personal before opening the other workbook" I do not know what this means. Please clarify. Approaches I have considered: 1. disabling the execution of any macros in A.xls or Personal.xls That is what I was trying to do with EnableEvents 2. programmatically mod the code lines in Personal.xls that call the problem-creating procedure before I open A.xls and then restoring the code after I close A.xls I know how to do that, it just seems ugly. 3. programmatically move Personal.xls to another folder before opening A.xls and then moving it back after I close A.xls Also ugly 4. bypassing the calls in Personal!Workbook_Open that create the problem by testing what other workbooks are open. Unfortunately, Personal.xls opens before anything else so the only WB it can see is itself Thanks MWE --- Message posted from http://www.ExcelForum.com/ |
Disabling Events
If Personal.xls is causing a problem when another workbook is opened (I'm
taking your word that it is so) then close it. Set xLApp = CreateObject("Excel.Application") xLApp.Workbooks("Personal.xls").Close, False xLApp.Workbooks.Open FileName:=xLPath + "\" + xLFileName -- Jim Rech Excel MVP |
Disabling Events
Jim: thanks (again).
Personal.xls is not causing the problem when another spreadsheet i opened. Rather Personal.xls is causing the problem when it opens. That may not have been very clear, but I did pretty much state that i considerations #2, #3 and #4. On the off chance that I did not understand the sequence of events, tried the extra line of code you suggested. It did not work. At this point, option #3 is the easiest. MW -- Message posted from http://www.ExcelForum.com |
All times are GMT +1. The time now is 10:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com