I am writing code in VBA to manipulate a Bloomberg screen. Bloomberg is a financial application with various data displayed on the screen. Everything was working reasonably well but I have the following problem when trying to copy the data from the screen:

When I use the copy command in the code and then manually paste the data from the clipboard to something like Notepad I get all of the data on the page. When I use the Forms reference via VBA and GetFromClipBoard I am only getting the first part of the page returned in the string.

The Bloomberg page itself has data over various tables and when using something like Notepad I get everything but using the paste method via the form reference I only get the first table. I suspect there is some code in the Forms method that is at fault. Is there an alternative to using Forms please?

Many thanks,


Neil.

- Code



Private Sub btnSend_Click()

' * Please note this references the Microsoft Forms 2.0 Object Library *

'This code does NOT make use of the API.
'It controls the Bloomberg desk-top application by sending commands.

Dim lngBLP As Long
Dim ticker As String
Dim startTime As String
Dim endTime As String
Dim currentDate As Date
Dim currentDateString As String
Dim currentHour As Integer
Dim myData As DataObject
Dim sourceText As String
Dim testr As Variant

Dim counter As Integer

Set myData = New DataObject

ticker = "EDM2"
currentHour = 2

currentDate = Date
currentDateString = Format(Month(currentDate), "00") & "/" & Format(Day(currentDate), "00") & "/" & Format(Right(Year(currentDate), 2), "00")
startTime = Format(currentHour, "00") & "00" & currentDateString
endTime = Format(currentHour + 1, "00") & "00" & currentDateString
lngBLP = DDEInitiate("winblp", "bbk")

'choose screen to work with
Call DDEExecute(lngBLP, "<blp-0>")
'ensure cursor is at starting position
Call DDEExecute(lngBLP, "<menu>")
Call DDEExecute(lngBLP, "<menu>")
'open Message page
Call DDEExecute(lngBLP, ticker & "<cmdty>VWAP<go>")

Call DDEExecute(lngBLP, "11<go>")
Call DDEExecute(lngBLP, "<tabr><blp-0>")
Call DDEExecute(lngBLP, "<tabr>" & startTime & " <blp-0>")
Call DDEExecute(lngBLP, endTime & "<go>")

Do Until isValidVWAPDataString(sourceText) Or counter = 10
    Call DDEExecute(lngBLP, "<copy>") 'copy data
    Application.Wait (Now + TimeSerial(0, 0, 2.5)) 'wait 2.5 seconds
    myData.GetFromClipboard 'get data from the clipboard
    sourceText = myData.GetText 'return data to string
    testr = myData.GetText 'return data to string
    counter = counter + 1
Loop

'close the 'conversation'
Call DDETerminate(lngBLP)

End Sub




- Pasted output using the same copy command in the code but just manually pasting to Notepad:

 BAM2 Dn 9 8 . 6 6 5  unch  98.665/98.670 274x2261                ComdtyO M O N
At DELAYED Vol 17,332 Op 98.665 Hi 98.675 Lo 98.660 OpInt 131,342
BAM2      Comdty 95) Te 96) Act 97) Expi Option Monitor: Option Monitor
BANK ACCEPT FUTR  Jun12    98.665         0%    98.665    /    98.670    Hi    98.675    Lo    98.660    Volm    17332    HV    .25 91)  News (CN)
Calc Mode Center 98.665 Strikes 18 Exch Montreal Exchang
     Calls      Puts
     Ticker    Strike    Bid    Ask    Last    Volm    OInt      Ticker    Strike    Bid    Ask    Last    Volm    OInt
     Jun 12 (42d, 6/18/12); CSize 1000000; BAM2 98.67      Jun 12 (42d, 6/18/12); CSize 1000000; BAM2 98.67
     BAM2C    97.625              1.040y                BAM2P    97.625              .001y         750
     BAM2C    97.750              .915y                BAM2P    97.750              .001y         
     BAM2C    97.875              .790y                BAM2P    97.875              .001y         750
     BAM2C    98.000              .665y                BAM2P    98.000              .001y         
     BAM2C    98.125              .540y                BAM2P    98.125              .001y         
     BAM2C    98.250              .415y                BAM2P    98.250              .001y         
     BAM2C    98.375              .290y         500      BAM2P    98.375              .001y         500
     BAM2C    98.500              .170y         1000      BAM2P    98.500              .005y         500
     BAM2C    98.625    .060    .080    .065y         3550      BAM2P    98.625              .025y         1200
     BAM2C    98.750              .015y         2250      BAM2P    98.750              .100y         8100
     BAM2C    98.875              .005y         2625      BAM2P    98.875              .215y         5000
     BAM2C    99.000              .001y         500      BAM2P    99.000              .335y         500
     BAM2C    99.125              .001y                BAM2P    99.125              .460y         
     BAM2C    99.250              .001y         6750      BAM2P    99.250              .585y         
     BAM2C    99.375              .001y                BAM2P    99.375              .710y         
     BAM2C    99.500              .001y         500      BAM2P    99.500              .835y         
     BAM2C    99.625              .001y                BAM2P    99.625              .960y         
     BAM2C    99.750              .001y                BAM2P    99.750              1.085y         
      Sep 12 (133d, 9/17/12); CSize 1000000; BAU2 98.59      Sep 12 (133d, 9/17/12); CSize 1000000; BAU2 98.59
93)  Default color legend Zoom 100%




- Data returned to the string "sourceText"

"BAM2 Dn 9 8 . 6 6 5  unch  98.665/98.670 274x2261                ComdtyO M O N
At DELAYED Vol 17,332 Op 98.665 Hi 98.675 Lo 98.660 OpInt 131,342
BAM2      Comdty 95) Te 96) Act 97) Expi Option Monitor: Option Monitor
BANK ACCEPT FUTR  Jun12    98.665     "

Are you trying to pull this off a web page?

No, it's from a Windows application called "Bloomberg".  

Actually, all good. Resolved the problem.

Resolved, HOW, please?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

User error. It was fine all along! I was just about to start parsing the data and I was checking the string in the locals window but it's length is limited there. So I assume that was the full string. DOH!