1

I have an Excel workbook that sends information to another application (via VBA), which in turn produces a pop-up window with the results of that information, and which I then need for my Excel workbook. Is there any way to have Excel read the information from that other application's pop-up window and paste it into itself?

[enter image description here]

Here is a picture of the dialog box. What I need is the date and time from it.

It's from a program that was written in VB6. It produces its own popup window. It looks like a custom dialog box.

I don't have access to the outside application's code. It looks like a proprietary program written in VB6 that has its own function buttons, one of which does the calculations with the inputted data, then creates a dialog box with the calculated data. There are no fields in the dialog box to grab the data, it's simply just a box containing data with an "OK" button. Right now, we copy the values down manually, then move on to the next calculation.

Thanks for your help :)

Community
  • 1
  • 1
Joshua Plyler
  • 11
  • 3
  • It's from a program that was written in VB6. It produces its own popup window. It looks like a custom dialog box. – Joshua Plyler Aug 04 '17 at 16:57
  • I'm new to this forum, is there a way to submit a picture? – Joshua Plyler Aug 04 '17 at 17:13
  • Added a picture of the dialog box. What I need is the date and time. – Joshua Plyler Aug 04 '17 at 17:20
  • How are you sending the information to the other application? – Tim Williams Aug 04 '17 at 17:50
  • Using `.sendkey` commands. – Joshua Plyler Aug 04 '17 at 17:59
  • I think you'd need to use Windows API calls to locate the popup, and then I think you could also access the text, but I'm not that familiar with the process. See https://stackoverflow.com/questions/25098263/how-to-use-findwindow-to-find-a-visible-or-invisible-window-with-a-partial-name for example. And also: https://stackoverflow.com/questions/2751400/get-the-text-within-a-dialog-box – Tim Williams Aug 04 '17 at 18:03
  • Thanks Tim. I'm able to get the Handle of the dialog box, but I'm not very familiar with Windows API so besides copying and pasting that code to find the Handle, I'm not sure where to go from there. – Joshua Plyler Aug 04 '17 at 18:17

1 Answers1

1

This worked for me, using a test dialog from a .NET Windows Forms project.

The dialog caption was "Tester!" and it contained a single label with some text. Your situation will be a little different: you will need to determine the "class" of the control containing the text you need. You can use Spy++ for this. 

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
    (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private Declare Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long



Sub main()
    Dim lngHWnd As Long
    Dim lngHWndChild As Long
    Dim lngIndex As Long
    Dim lngDlgItem As Long
    Dim lngTextLength As Long
    Dim strText As String

    lngHWnd = FindWindow(vbNullString, "Tester!")

    lngHWndChild = FindWindowEx(lngHWnd, 0&, "WindowsForms10.STATIC.app.0.3ee13a2_r17_ad1", vbNullString)

    lngTextLength = GetWindowTextLength(lngHWndChild)

    strText = Space(lngTextLength)
    GetWindowText lngHWndChild, strText, lngTextLength + 1

    Debug.Print strText

End Sub

Spy++ - press Alt+F3 then drag the "target" onto the dialog to locate it in the tree.

enter image description here

Tim Williams
  • 154,628
  • 8
  • 97
  • 125