Run an Excel Macro from Access and return a variable

[Rafi]

I need to invoke an excel macro from Access and return the variable
strFileName from Excel. I can invoke the Excel Macro but do not know how to
get the variable back into Access

My Acces code is

XL.Run "PERSONAL.XLS!DHL.DHL where DHL is the name of the module and that of
the Sub.

Thanks

 

[Alex Dybenko]

Hi,
run method also returns the value of function its calling:

dim varResult as variant

varResult=XL.Run("PERSONAL.XLS!DHL")

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[strive4peace]

Hi Rafi,

according to the directions that Alex gave you (Hi Alex!), instead of this:

XL.Application.Run "Personal.XLS!MergeFiles.MergeFiles"

you would do this:

myVar = XL.Application.Run( "Personal.XLS!MergeFiles")

WHERE
myVar is a variable in your code
MergeFiles is defined to be a Function that returns a value (as opposed
to a Sub)


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Alex Dybenko]

Thanks Crystal, exactly what I meant!

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Rafi]

Crystal,

I have changed my Aceess and Excel code as you suggested and now I get a
run-time error 1004 - the macro Personal.xls!Mergefiles cannot be found.
What am I doing wrong?

Thanks for all the help

FileName = XL.Application.Run( "Personal.XLS!MergeFiles")

And changed the Excel Sub Routine to a Function

Function MergeFiels(FileName)

 

[strive4peace]

you're welcome, Alex ... nice to "talk" <smile> -- hope you and your
family are doing well


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

Hi Rafi,

you need to spell the name the same ...

you have:
MergeFiles
MergeFiels

also, it is good in your function declaration to define the data type of
the result...

Function MergeFiels(FileName) as string

-- and then you have to WRITE the result to the function name!

MergeFiels = stFileName


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*