AutoHotkey Community

[Demokos]

I think there are at least three ways to send variables to Excel, and to get back a result. As there is a long time I didn't do things like that, I will speak about "theory"...

The first method, as said Philo, is to send values to an Excel spreadsheet by SendInput once Excel is started and a sheet present/open. I know there is a way, in VBA, for starting a macro when a cell is filled with a value, and, of course, to read value(s) from cell(s). There is a very long time I didn't do that, but if you go in an Excel-VBA newsgroup and ask the question, no doubt you wil have an answer. You can then send back a result with the VBA SendKeys method, from the Excel side, and read it with the OnMessage() AHK function.

The second method, may be more flexible in my mind, is ot use a text file and to write/read to/from it from both side.
AHK : FileAppend procedure to wrtie to a text file your parameters
Start excel or load a sheet if it is already started
Excel : read the text file with the OpenAsTextStream method to get the parameters
Excel compute to get the result then use the Write instruction or the WriteLine method to send back the result to the file and then use the Close instructiuon.
Eventually use VBA Sendkeys method and the OnMessage() function to say to AHK that the datas are computed...

The third method is to use the CMDRet AHK functions or DLL to get/send values from/to excel using a VBS file and the ADO capabilities of VBScript.

You can also (crime of injure-majesty !!!) use the Obj/COM functions of AutoIt by launching a compiled AutoIt script with AHK, use a file to get back the results, etc... !!!

As you see, there is a lot of ways to do the things, but for each of them, you have to make searches to reach the goal.

Hope it helps

P.S : an other way, use AHK RunWait to run a VBS file which use ADO to send/get values from Excel, and to write the result to a text file with VBS. Once the VBS script will be executed, AHK will take control again...

[Andrew]

Ahhh, the FileAppend command. Thanks so much. All of those who answered my question were giving me too much credit for asking a complex question. It was actually much simpler than you all were thinking.

I just need to use the FileAppend command to place my variable values in a file and then open the file with Excel. Voilà, my values are neatly in cells where I can use them for whatever I'd like.

I'm not a programmer and have only been using AutoHotkey for a week, yet I've been able to do some very powerful stuff with it. I'm really impressed with the program and the community of people who are here to support it. Thanks again.

[empyrean5]

The way I pass data from AHK to Excel is via the clipboard.

If I have multiple items to send, I tab-delimit them

In VBA, set a reference to "Microsoft Forms 2.0 Object Library"
(Tools - > References)

In a VBA Module:

[royc]

Hi, this is a very late listing but thought I would add a "How to" do nick's suggestion of using Environment Variable. My main problem was to run an update in Excell once only after generating changes in Access. If the user adds or deletes rows then a subsequent refresh of data would write it all to the wrong rows.! EEK!
So in a Command Mode .bat file I added this Environment variable.
SETX UpdateTrailingReady "No"
start excell to get latest data
start Access to generate some changes for excell with /wait till it finished.
kill excell (I leave Excell running while Access links to it)
SETX UpdateTrailingReady "Yes"
Start excell to apply my external data changes
TASKKILL /F /IM EXCEL.EXE after 2 mins, waits for ticker updates
Excel BeforeClose macro looks for "Yes" and runs the Refresh command eg
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Update Email Changes before close
If Environ("UpdateTrailingReady") = "Yes" Then
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Save
End If
End Sub
SETX UpdateTrailingReady "No"
end of .bat file.
Hope this helps someone. I tried to do the same thing on the Open Macro in Excell but it seems too early and prevents tickers from updating.
(Even waiting with Do Events command.)