New Workbook Open procedure

[kwoifo]

Hi,

Is there any way to capture the event of when a new workbook is opened in the excel application ? I am running some code that requests data from another application. The way that application returns the data is by opening a new workbook (with a random name) with the data in it, about 30 seconds after I run my code. I am trying to get the code to automatically recognize when a new workbook is opened, and then copy/paste a range into my active workbook.

I cannot find a way to do this. I tried using Application.Wait to pause the code for 30 seconds, but since this hangs the application, the new workbook is only opened after the 30 seconds and after the copy/paste module is run . I guess I would have to use some sort of event handler procedure to capture the new workbook being opened but can't figure out how.

Might anyone have any tips on how this can be done ? Would be a huge help. Thanks a lot in advance!

 

[kwoifo]

I am trying to do this through a loop but am encountering issues -- it doesn't seem to recognize when a new workbook is opened:

Do Until Workbooks.Count = Workbooks.Count + 1
MsgBox "Waiting for new data file to open"
Loop


Dim Wb As Workbook
Dim wbs As Workbooks
Set wbs = Application.Workbooks
For Each Wb In wbs
If Left(Wb.Name, 4) = "ad" Then
Windows(Wb.Name).Activate
Range("A1:G500").Select
Selection.Copy
Windows("datanalysis.xlsm").Activate
Range("A5").Select
ActiveSheet.Paste
End If

Next Wb


End Sub


End Sub

 

[kwoifo]

I was able to partly remedy the issue using
Application.OnTime Now + TimeValue("0:00:30"), "GetData"
Where GetData is my copy/paste code from the new workbook. This works ok , but would be an issue if the new workbook doesn't get opened within the selected timeframe. No way to capture the even of a new workbook being opened ?
Thanks in advance