0

I would like to have a VBA script run in Excel and, at a certain point, pause until a specific workbook opens. Then continue the script in the original workbook. This because the original workbook needs to import data from the newly opened workbook, then close that new workbook after.. I assume that I should be using some sort of Windows event handler that watches for things like a file opening.
Versions in use are MS windows 10 and excel 2013

Community
  • 1
  • 1
madmouse
  • 1
  • 1
  • 2
  • 1
    How is the other workbook being opened? Is your code opening it? If Yes then you should include your code in the question. – Tim Williams Sep 22 '16 at 00:00
  • ...if No then you need to explain exactly what is going on. – Tim Williams Sep 22 '16 at 00:23
  • Tim; Thanks for the quick response. The other workbook is being opened from an external application (SAP) The user will run a listing in SAP and export it into a new excel workbook named "worksheet in basis (1).xlsx. unfortunatly, this is default and can not be changed. When that workbook opens, I would like to be able to have the original workbook notice it and perform a routine to extract the data from the new workbook and paste it into a set sheet. – madmouse Sep 22 '16 at 00:48
  • Is the workbook with the code already open in Excel? If yes then when the report opens does it open in the same instance as the one where your code is? – Tim Williams Sep 22 '16 at 01:35
  • Tim: The workbook with the code is open in Excel. A few routines have already been executed and it needs to wait for the new workbook with the extracted SAP data to open. When the new workbook opens, it is in a new instance of Excel. – madmouse Sep 22 '16 at 02:00
  • It's not that simple to work across Excel instances, but it could be done. Try this for starters: https://social.msdn.microsoft.com/Forums/office/en-US/e3e99712-01a7-483e-bf0e-52bb1f94889c/how-to-use-accessibleobjectfromwindow-api-in-vba-to-get-excel-application-object-from-excel?forum=exceldev – Tim Williams Sep 22 '16 at 06:33

1 Answers1

5

WithEvents allows us to monitor ActiveX Events that are exposed to Automation. Here I declare a variable WithEvents to monitor the Excel.Application NewWorkbook event.

enter image description here

Option Explicit
Private WithEvents ExcelApp As Excel.Application

Private Sub ExcelApp_NewWorkbook(ByVal Wb As Workbook)
    If Wb.Name = "MyWorkbook.xlsm" Then Call ProcessMyWorkbook
End Sub

Private Sub Workbook_Open()
    Set ExcelApp = Application
End Sub