Retrieve Data to Excel file from other software

kosan

New Member
Joined
Aug 29, 2017
Messages
2
Dear All,

I got a question on the Excel VBA with other software running in my PC.
I created a excel file and used DDE to connect the data from the trading software (SPTrader) is fine.
Now I want to retrieve the traded record back to the excel file for other purpose.
Then I used WinAPI FindWindow and FindWindowEX to find the window of traded record field under the account section in the SPTrader. I also can use ShowWindow (SW_MAXIMIZE/ SW_RESTORE) to test the window and proofed I was get a correct area.
On these area, the software is only allow to use mouse right-click then select the “Copy all Trade” from drop down list. Not allow Ctrl+C.
But when I use the SendMessage (CB_GETLBTEXT, CB_SELECTSTRING, LB_GETTEXT, LB_GETITEMDATA, LB_GETTEXTLEN, LVM_GETITEM, LVM_GETITEMTEXT, WM_GETTEXT...) and pointed to the window (by hwnd) for getting records but it return 0.
Now I just use the VBA code to set the whole software to a specify position and sizing for window. Then call the mouse cursor to the location and do the right-click and select the “Copy all Trade”, and paste into the excel file.
So, my question is: Is it possible to retrieve data from the trade record area by VBA? What is the type of this area? Table, Listbox, Recordset, DataGrid, StringGrid…? How to retrieve data when a trade is done without manually run the function?

However I also found the video is exactly what I want. YouTube

Thank you very much!

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Public Sub TradesOrder()
Dim mainwnd As String
Dim mainwnd_ac As String
Dim hwnd As String
Dim Chwnd1 As String
Dim Chwnd2 As String
Dim Chwnd3 As String
Dim Chwnd4 As String
Dim Chwnd5 As String
Dim Chwnd6 As String
Dim Chwnd7 As String

Dim wkb As Workbook
Dim wb As String
Dim sht As String
Dim dirty As Long

wb
= "SPTrader_Excel_KK.xlsm"

sht
= "SPTrader_XLS"

On Error Resume Next
Set wkb = Workbooks("SPTrader_Excel_KK.xlsm")

If wkb Is Nothing Then
Workbooks
.Open (ThisWorkbook.Path & "" & wb)
Workbooks
(wb).Activate
Worksheets
(sht).Activate
Else
Workbooks
(wb).Activate
Worksheets
(sht).Activate
On Error GoTo 0
End If

mainwnd
= Worksheets(sht).Range("AC1").Value
mainwnd_ac
= Worksheets(sht).Range("AC2").Value

hwnd
= FindWindow(vbNullString, mainwnd)
Chwnd1
= FindWindowEx(hwnd, 0&, "MDIClient", vbNullString)
Chwnd2
= FindWindowEx(Chwnd1, 0&, "TfrmAccBox", vbNullString)
Chwnd3
= FindWindowEx(Chwnd2, 0&, "TPageControl", vbNullString)
Chwnd4
= FindWindowEx(Chwnd3, 0&, "TTabSheet", "Order")
Chwnd5
= FindWindowEx(Chwnd4, 0&, "TAdvStringGrid", vbNullString)
Chwnd6
= FindWindowEx(Chwnd5, 0&, "TAdvRichEdit", vbNullString) 'TAdvRichEdit 'TGridDatePicker

SetWindowPos hwnd
, HWND_NOTOPMOST, 0, 0, 850, 620, SWP_SHOWWINDOW
BringWindowToTop Chwnd2
BringWindowToTop Chwnd4
ShowWindow Chwnd4
, SW_NORMAL
SetCursorPos
500, 540 'x and y mouse position
mouse_event MOUSEEVENTF_RIGHTDOWN
, 0, 0, 0, 0 'RightClick
mouse_event MOUSEEVENTF_RIGHTUP
, 0, 0, 0, 0 'RightClick
Sleep
100
mouse_event MOUSEEVENTF_RIGHTDOWN
, 0, 0, 0, 0 'RightClick
mouse_event MOUSEEVENTF_RIGHTUP
, 0, 0, 0, 0 'RightClick
SendKeys
"{DOWN}"
SendKeys
"{DOWN}"
Sleep
100
SendKeys
"{ENTER}"

Worksheets
(sht).Activate
Worksheets
(sht).Range("C25").Select
Range
("C25").PasteSpecial xlPasteAll

End Sub</code>
E1MWs.png


ipcsQ.jpg
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,223,885
Messages
6,175,181
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top