AutoHotkey Community

Andrew · Joined: 10 Oct 2006 Posts: 16

Is it possible to pass a variable to Excel so it can be used by a VBA subroutine? Thanks.

slomz · Joined: 03 Sep 2006 Posts: 608 Location: Iowa, U.S.

Yes it is.

Andrew · Joined: 10 Oct 2006 Posts: 16

OK, I'll bite. How do I do it?

I'm writing a VBA subroutine to process a bunch of files in Excel. The quantity of files and file names change daily. VBA doesn't appear to have an "ifexists" function or the ability to open files using wildcards. Therefore, I would like to save all the file names I need to process in AutoHotkey and pass them to Excel.

slomz · Joined: 03 Sep 2006 Posts: 608 Location: Iowa, U.S.

I know it is possible and may deal with dll calls, but I have not done anything with dll calls before so your not in luck.

Andrew · Joined: 10 Oct 2006 Posts: 16

Can anyone tell me how it's done or point me to some documentation on the subject? Thanks.

PhiLho · Posted: Tue Oct 17, 2006 11:33 am Post subject:

Open a new sheet and put the needed info in cells of this sheet... Then VBA can access this info easily.
_________________

vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")

Andrew · Joined: 10 Oct 2006 Posts: 16

Thanks, PhiLho. That's what I don't know how to do... "put the needed info in cells of this sheet". The variables have been assigned a value in AutoHotkey and I want Excel to read the values into a spreadsheet.

I think I need to use the EnvSet command and open Excel with the Run command. But I don't know exactly where to put the EnvSet command. Am I on the right track here?

PhiLho · Posted: Wed Oct 18, 2006 9:26 am Post subject:

I don't see how it relates to EnvSet.
Just Run Excel, wait for it, then Send the values separated by {Enter} or {Right}, they should go in separate cells.
_________________

vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")

nick · Joined: 24 Aug 2005 Posts: 345 Location: Berlin / Germany

PhiLho · Posted: Wed Oct 18, 2006 12:45 pm Post subject:

I am a bit rusty on VBA. Does it has methods to get environment variables? And to read a file on disk? If so, these might be interesting alternatives, indeed.
_________________

vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")

Andrew · Joined: 10 Oct 2006 Posts: 16

OK, maybe I just don't know how to do it. My AutoHotkey script contains:

INTMX=value1
EnvSet,INTMX
Run, Excel.exe

Once Excel is open, I run a VBA script that contains:

Sub Macro1()
MsgBox INTMX
End Sub

And Excel displays an empty message box. The variable doesn't appear to have been passed. This seems really simple. What am I doing wrong?

Thanks.

BoBo · Guest

I doubt that you can use AHK's EnvSet to set a system environment variable. Even Run, %comspec% set won't work ... AFAIK Confused

PhiLho · Posted: Wed Oct 18, 2006 2:19 pm Post subject:

BoBo, I had same doubts, so I read the manual:

Andrew · Joined: 10 Oct 2006 Posts: 16

Thanks for your replies. In case it's not obvious, I'm very much a noobie to all of this. I'm not even a programmer by trade. I don't necessarily need to use the EnvSet command. I just want to pass some variables with their values from AutoHotkey to Excel. Has anyone done this before?

nick · Joined: 24 Aug 2005 Posts: 345 Location: Berlin / Germany