Data from clipboard using forms different than expected.

[Mightyginger]

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

 

[vbajock]

Are you trying to pull this off a web page?

 

[Mightyginger]

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

 

[Mightyginger]

Actually, all good. Resolved the problem.

 

[SkipVought]

Resolved, HOW, please?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mightyginger]

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!