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

 

[Rafi]

Hi Alex,

I ma not sure I understand. I have enclosed the access and Excell code
below and could use your help with understanding how to implement this code.
What I need to do is return to Access the value of stFileName.

MS Access

Private Sub cmdConsolidate_Click()
Dim XL as Object
If XL is Nothing Then
Set XL = CreateObject("Excel.Application")
End If
XL.Workbooks.Open strUser & "\Application
Data\Microsoft\Excel\XLstart\Personal.xls"
XL.Application.Run "Personal.XLS!MergeFiles.MergeFiles"
XL.Workbooks.close
XL.Quit
End Sub


Excel Code

Sub MergeFiels()
Dim stFileName as String
.....
.....
.....
With Application.FileDialog(msoFileDialogOpen)
If .Show = -1 Then
stFileName=.SelectedItems(1)
..Execute
Else
MsgBox "Action Canceled"
Exit Sub
End If
End With
....
....
....
End Sub
End sub

Thanks for your help

 

[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
*