Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Excel SendMessage WM_COPYDATA to AHK script


  • Please log in to reply
No replies to this topic
Staubi
  • Members
  • 1 posts
  • Last active: Mar 24 2014 11:40 AM
  • Joined: 03 Mar 2014

Hi,

 

I spent quite some time to find a solution for transferring data from Excel to AHK and came across the Windows SendMessage API.

Unfortunately I didn't find a working example about how to get WM_COPYDATA to work from within VBA. So I did some experiments and finally got it to work.

 

Here is a little example on how to use SendMessage with WM_COPYDATA to an AHK script. So you can remote control AHK from within a VBA application like Excel, Word or Outlook and transfer data.

 

Put the follwowing code into a VBA module in Excel:

Option Explicit

Type COPYDATASTRUCT
    dwData As Long
    cbData As Long
    lpData As Long
End Type

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

Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
    (ByVal hwnd As Long, ByVal wMsg As Long, _
    ByVal wParam As Long, lParam As Any) As Long

Const WM_COPYDATA = &H4A
Const LNULL = 0&

Public Function GetAhkHwnd()
    Dim hwnd As Long
    Dim iCounter As Integer
    hwnd = FindWindow("AutoHotkey", "D:\Autohotkey\test\Receiver.ahk - AutoHotkey v1.1.07.03")
    While hwnd = 0 And iCounter < 5
        Shell ("C:\Program Files\AutoHotkey\AutoHotkey.exe D:\Autohotkey\test\Receiver.ahk")
        hwnd = FindWindow("AutoHotkey", "D:\Autohotkey\test\Receiver.ahk - AutoHotkey v1.1.07.03")
        iCounter = iCounter + 1
    Wend
    GetAhkHwnd = hwnd
End Function

Public Function test(sMsg As String)
    Dim lReturn As Long
    Dim hwnd As Long
    Dim cds As COPYDATASTRUCT
    'Dim sMsg As String
    Dim iErrReturn As Integer
        
    hwnd = GetAhkHwnd()
    If hwnd = 0 Then
        iErrReturn = MsgBox("Could not start AHK!", vbOK + vbCritical + vbApplicationModal, "Test SendMessage")
    Else
        cds.dwData = 0
        cds.cbData = Len(sMsg) * 2 + 2
        cds.lpData = StrPtr(sMsg)
        lReturn = SendMessage(hwnd, WM_COPYDATA, LNULL, cds)
        If lReturn <> 1 Then MsgBox ("Message not received by Receiver.ahk!")
    End If
End Function

Create file Receiver.ahk and insert the following code (taken from AHKL documentation):

#SingleInstance
OnMessage(0x4a, "Receive_WM_COPYDATA")  ; 0x4a is WM_COPYDATA
return

Receive_WM_COPYDATA(wParam, lParam)
{
    StringAddress := NumGet(lParam + 2*A_PtrSize)  ; Retrieves the CopyDataStruct's lpData member.
    CopyOfData := StrGet(StringAddress)  ; Copy the string out of the structure.
    ; Show it with ToolTip vs. MsgBox so we can return in a timely fashion:
    ;ToolTip %A_ScriptName%`nReceived the following string:`n%CopyOfData%
	TrayTip, Receiver, %A_ScriptName%`nReceived the following string:`n%CopyOfData%, 3, 1
    return true  ; Returning 1 (true) is the traditional way to acknowledge this message.
}

Don't forget to customize the path for AHK executable and the path to Receiver.ahk in VBA module lines 22, 24 and 25.

 

To run the example just call the function test(sMsg As String) for example with the following sub routine within a sheet. It will transfer the currently selected cells (just the address - not the content but this should be no problem, too) to Receiver.ahk.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    test (Target.Address(ReferenceStyle:=xlA1))
End Sub

Maybe my coding is not that pretty as I am no professional developer. So any suggestion for improvement is absolutely welcome! :-)

 

Hopefully this is of use for anybody ;-)

 

 

Regards

Martin