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>
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>
Last edited:
