Access query(with custom function) from Excel

[LightLY]

Dear Excel Gurus,

I am able to run an access query from Excel. However, if the access
query contains an Access custom function, I encountered problems
opening the RecordSet.

It seems that I am not able to retrieve data fields from Access which
are calculated from custom function using Excel. Is this a genuine
problem with Excel or did I miss out something?

I was working on this problem until 3am in the morning. Thank you very
much in advance if someone helps me.

 

[Joel]

You didn't specify what type of error you were getting. did you set these
references in the Tools - References menu in excel VBA

1) Microsoft Access 11.0 Object Library (or latest version on your PC)
2) Microsoft Active X data Object 2.8 Library (or latest version on your PC)
3) There are also two DAO libraries

 

[LightLY]

You didn't specify what type of error you were getting.

The error was "Type mismatch error". Under the watch window, inside
variable of type DAO.QueryDef, inside the property Parameters, there
was an undefined function.

The undefined function is a custom function used by the query in
Access. Thank you.

did you set these
references in the Tools - References menu in excel VBA

1) Microsoft Access 11.0 Object Library (or latest version on your PC)
2) Microsoft Active X data Object 2.8  Library (or latest version on your PC)

Yes, I have already set above 2 references.

3) There are also two DAO libraries

I also set Microsoft ActiveX Data Objects 2.8 Library.

Wouldn't the code fail compilation if I forget to set the references?

 

[Joel]

You didn't say what the error was so I didn't know if it was a compiler error
or and equation error.

http://www.cpearson.com/Excel/Call.htm

You can also run macros in other office application from VBA.