MasterBlaster
5/21/2007 4:33:00 PM
I'm trying to utilize an analysis function called XIRR and I found a snippet
of code. Once it gets to this line:
'Run the AutoOpen macro in the add-in
objExcel.Workbooks("atpvbaen.xla").RunAutoMacros(Microsoft.Office.Interop.Excel.XlRunAutoMacro.xlAutoOpen)
it blows up and jumps out of the function and doesn't generate an error or
anything. Any help would be appreciated. See full function code below:
Public Function xl_xIRR(ByRef dt As DataTable) As Double
Try
'Call Excel's XIRR() function. This is found in the Analysis
Toolpak add-in.
Dim objExcel As Microsoft.Office.Interop.Excel.Application
Dim MyDataRow As DataRow
Dim cnt As Int16 = 0
Dim Val As Double = 0
Dim Dates() As Date = New Date(dt.Rows.Count) {}
Dim CF() As Double = New Double(dt.Rows.Count) {}
For Each MyDataRow In dt.Rows
cnt += 1
Dates(cnt - 1) = MyDataRow("TradeDate")
CF(cnt - 1) = MyDataRow("Amount")
Next
objExcel = CreateObject("Excel.application")
'Open the Analysis Pack add-in
objExcel.Workbooks.Open(objExcel.Application.LibraryPath &
"\Analysis\atpvbaen.xla")
'Run the AutoOpen macro in the add-in
objExcel.Workbooks("atpvbaen.xla").RunAutoMacros(Microsoft.Office.Interop.Excel.XlRunAutoMacro.xlAutoOpen)
'Call the XIRR function
Val = objExcel.Application.Run("atpvbaen.xla!XIRR", CF, Dates)
'Cleanup
objExcel.Quit()
objExcel = Nothing
Return Val
Catch ex As Exception
Throw ex
End Try
End Function