Pause Macro Until File is Downloaded and Opened

[jziobro4]

Hello:

I have the below macro which pulls in an excel file from the Bloomberg application. Microsoft excel does not stop processing the VBA. I need the macro to stop until the file named "grid*.xls" is opened, and then start again so I can copy and paste the data.

Please advise - been stuck on this for a while


Sub GetBBGFile()


AppActivate ("2-BLOOMBERG")




Application.SendKeys "CTS~", False
Application.Wait Now() + TimeValue("00:00:01")
Application.SendKeys "1~", False
Application.Wait Now() + TimeValue("00:00:01")
Application.SendKeys "TH10~", False
Application.Wait Now() + TimeValue("00:00:03")
Application.SendKeys "14~", False
Application.Wait Now() + TimeValue("00:00:01")
Application.SendKeys "2~", False




End Sub


Sub Copy()


Call GetBBGFile


Windows("London eSpeed 30019491 .xlsm").Activate


For Each w In Workbooks
If w.Name Like "*grid*" Then
Windows(w.Name).Activate
Exit For
End If
Next w


Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows("London eSpeed 30019491 .xlsm").Activate
Sheets("BBG").Select
Range("A2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Summary").Select


End Sub

 

[yky]

You said "until the file named "grid*.xls" is opened." Where is the code that opens the file? Anyway, try putting "DoEvents" after the open command. See if it works.

 

[jziobro4]

This macro exports a excel file from Bloomberg:

Sub GetBBGFile()


AppActivate ("2-BLOOMBERG")




Application.SendKeys "CTS~", False
Application.Wait Now() + TimeValue("00:00:01")
Application.SendKeys "1~", False
Application.Wait Now() + TimeValue("00:00:01")
Application.SendKeys "TH10~", False
Application.Wait Now() + TimeValue("00:00:03")
Application.SendKeys "14~", False
Application.Wait Now() + TimeValue("00:00:01")
Application.SendKeys "2~", False




End Sub