Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Toolkit in Access (Office 97)

    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.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Toolkit in Access (Office 97)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Toolkit in Access (Office 97)

    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?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Toolkit in Access (Office 97)

    I found this sample after I posted last. It should get you started:
    <pre> 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
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Toolkit in Access (Office 97)

    Try going to this post <post#=187790>post 187790</post#> it may be useful
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •