Analysis Pack - VBA problem

I'm stumped. We have an Access 97 db which opens an Excel 97 spreadsheet. Code follows


Sub XLopen()

Dim objXLApp As New Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet

    Const adhcXLSName = "ins.xls"
    Const adhcXLSPath = "G:\Accounting\MIS Reports\insurance\"

'Create an object from the XLS file
Set objXLBook = objXLApp.Workbooks.Open(adhcXLSPath & adhcXLSName)

'Set parent property of workbook object to get a pointer to excels application
    Set objXLApp = objXLBook.Parent

'Set XL and the workbook to visible
    objXLApp.Visible = True
    objXLBook.Windows(1).Visible = True


We use the EOMONTH function to calculate some dates. Unless we do the following we get #NAME? for the EOMONTH function

    objXLApp.AddIns("analysis toolpak - vba").Installed = False
    objXLApp.AddIns("analysis toolpak").Installed = True
    objXLBook.VBProject.References.AddFromFile "c:\program files\Microsoft Office\Office\Library\Analysis\Atpvbaen.xla"

After running this code we get #VALUE! for the EOMONTH function

The strange thing is that if I change the excel file name and use another spreadsheet that has the same EOMONTH functions in it the EOMONTH function will work OK. And if I open a new worksheet in the same excel application the EOMONTH function will wok in the new worksheet.


Any suggestions will be greatly appreciated. I know that upgrading to XP may solve it but thats not possible.


Thanks in advance

Glenn
Start Free Trial