Excel XIRR function in Access 2007 VBA causes run-time error

[Guest]

I have VBA code in Access 2003 application which uses automation to run an
Excel accessible-only function XIRR (from Analysis Toolpak Add-in) working
fine until I converted the application over to Access 2007 (on a newly
formatted computer with Office 2007 Pro freshly installed on Windows XP).

I am now getting a run-time 1004 error: Cannot run the macro 'XIrr'. The
macro may not be available in this workbook or all macros may be disabled.

Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
objExcel.RegisterXLL objExcel.Application.LibraryPath &
"\ANALYSIS\ANALYS32.XLL"
results = objExcel.Run("XIrr", p, d) '*** ERROR ON THIS LINE ***
where p and d are arrays of dollar amounts and dates respectively.

I have verified the following:
- Access references Excel 12 Object Library
- Analysis ToolPak Add-in activated in Excel 2007 -- function works fine in
worksheet
- Macro settings in Excel 2007 Trust Center set to enable all (not
recommended)

I even replaced objExcel.Run command with basic function 'Sum' with the same
error results. Does anyone have any ideas for me?

 

[ashish24naik]

Hello,

I am also facing a simillar. Did you find a solution for the same.

Thanks and Regards!
Ashish Naik