>> Calling a macro in Excel

[Jonathan]

Hi, I'm calling a macro in Excel. If I don't have any arguments then it is
successful. However, with arguments if fails?

I can call this macro from within the workbook successfully.

This is the first line of the macro...

Public Sub AddProduct(ByRef AddProductSuccessful As Boolean, _
ByVal newSheetName As String, Optional CalledByUser As Boolean =
True)

This is the line in Access to call the above macro in Excel...

mxlApp.Run "(basAddProduct.AddProduct(" & fAddProductSuccessful & ", " &
Chr(34) & !spSupplierCode & Chr(34) & ", False))"

Any ideas or recommendations appreciated

Many thanks,
Jonathan

 

[Duane Hookom]

This looks like a purely Excel programming question. You should ask in an
Excel NG rather than an Access NG.

 

[John Nurick]

Hi Jonathan,

It would have helped if you told us *how* it was failing (what error
messages, on what line of code, etc.).

But

1) Excel.Application.Run needs a comma-separated list of arguments.
You're passing it a single string of text that looks like a
comma-separated list of arguments enclosed in parentheses. Check Excel
Help.

2) If there's a possibility of multiple copies of the workbook being
open, you may need to disambiguate AddProduct further by including the
workbook name, e.g.
XXX.xls!basAddProduct.AddProduct

On the other hand you don't need the module name unless you have more
than one AddProduct procedure defined in the workbook.

5) The AddProductSuccessful argument is declared as ByRef Boolean, but
the variable you are trying to pass is called fAddProductSuccessful.
This seems wrong, unless you are using a naming convention in which the
prefix "f" means "Boolean". If you're using AddProductSuccessful to get
a return value, why not declare AddProduct as a Function rather than a
Sub?