Using Toolkit in Access (Office 97)

Topic

New Reply

I want to be able to access the Analysis Toolkit functions Workday and NetWorkDays from within Access. I can access the Excel Object library, but how do I additionally access these functions? Andy.

Reply | Quote

Replies

Hi Andy,
I think you’d be better off writing your own function for this. The Analysis Toolpak is an Excel-specific .xll library so you would need to write a VBA wrapper for these functions that registered the xll and the functions in it. I guess the Application.RegisterXLL method would be involved but I’ve never used it so I can’t say how easy it is. It seems an unnecessary overhead though to load Excel in order to access a function that isn’t part of Excel anyway!
Hope that’s of some use?
PS You may want to look at this in the Access Web.

Reply | Quote

I suspect I will create my own versions of these functions, but I know that the Excel Analysis Toolkit functions can be referred to from within Access – I’ve done it before but ages ago.. Andy. Anyone else recall how to do this?

Reply | Quote

I found this sample after I posted last. It should get you started:

    Dim oXl As Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim oRange As Excel.Range
    Dim oAddIn As Excel.AddIn

    'Launch Excel and make it visible
    Set oXl = CreateObject("Excel.application")
    oXl.Visible = True
    Set oBook = oXl.Workbooks.Add
    Set oSheet = oBook.Worksheets.Item(1)
    
    ' Add the Excel Analysis ToolPak library
    oXl.AddIns.Add FileName:=oXl.LibraryPath & "analysisanalys32.xll"
    Set oAddIn = oXl.AddIns.Item("Analysis ToolPak")
    
    ' Register all the Analysis ToolPak functions
    '  See Microsoft Knowledge Base Article Q108888, or Q213489
    oXl.RegisterXLL "Analys32.xll"
    
    ' Add the Excel Analysis ToolPak - VBA AddIn -
    '  it's the Automation interface to the Analysis ToolPak library
    ' Now open the .xla so that you can run its Auto_Open macro now, and others later.
    ' See Microsoft Knowledge Base article Q108888, or Q213489
    oXl.Workbooks.Open oXl.LibraryPath & "analysisatpvbaen.xla"
    oXl.Workbooks("atpvbaen.xla").RunAutoMacros 1

Hope that helps.

Reply | Quote

Try going to this post post 187790 it may be useful

Reply | Quote