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

T

tcoles

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel XIRR function in Access 2007 9
Use XIRR FUNCTION IN A VB 2005 PROGRAM 0
XIRR #NAME? error - Toolpak IS installed 1
Excel function XIRR in VBA 2
setting object to excel application 3
XIRR function in Excel 2007 2
run an Excel macro from Access : Run time error 1004 2
Convert Excel code to work in Access 1

Top