Copy data from an external application into Excel

[Candide]

Hello,

I try to copy data from an external application into Excel thanks to
VBA. It seems to work for Acrobat Reader but not for Bloomberg
Terminal. When I do it by hand, it is fine. When I run the macro
below, I do get the last data I have stored to the clipboard before
running the macro i.e. the macro does not copy anything new. I tried
several time periods in Wait with no luck. Any idea?


Public Sub procGrabData()
Dim lngBlp As Long

lngBlp = DDEInitiate("winblp", "bbk")
Call DDEExecute(lngBlp, "<blp-1>")
Application.Wait (Now + TimeValue("0:00:05"))

SendKeys "^a", True
Application.Wait (Now + TimeValue("0:00:05"))

SendKeys "^c", True
Application.Wait (Now + TimeValue("0:00:05"))

AppActivate "Microsoft Excel"
ActiveSheet.PasteSpecial

Call DDETerminate(lngBlp)
End Sub


BTW, I have tried DataObject but the results are the same when I
change ActiveSheet.PasteSpecial with:
Dim objData As New DataObject
objData.GetFromClipboard
ActiveCell = objData.GetText


Thank you for your help,
Xavier

 

[Peter Beach]

Hi Candide,

Bloomberg provide an XL Add-In which allows you to query their data directly
from XL. Perhaps you should contact your Bloomberg rep and see whether you
can get it. You can download the Add-In IIRC from the Bloomberg App itself.
I don't have access to a terminal but I think you can navigate to the
download section somewhere in their Help section. Virtually *any* solution
is better than one which involves SendKeys <g>.

Regards,

Peter Beach

 

[Candide]

Thank you, Peter.

Indeed, Bloomberg provides such an Add-In. Problem is that you can not
grab all the data. Some of them are just text on the Bloomberg
Terminal. I can copy the data manually but I would like to automatise
the process. Sendkeys is the only way I know but I am open to any
other technics.

Question: If I can do it manually, is it possible that VBA can not do
it thanks to Sendkeys?

Many thanks,
Xavier 'Candide'

 

[Peter Beach]

Hi Xavier,

I wasn't aware that the Add-In had such a limitation.

SendKeys (though a horrible technique) should work. If Ctrl a, Ctrl c
followed by a paste in XL work manually, then SendKeys should work. Does
the macro actually Select the whole page? Have you tried doing it using the
menu keys rather than the shortcut keys?

Sorry, I'm out of ideas :-(

Regards,

Peter Beach