Solved

AUTOMATION REFERENCES

Posted on 2003-11-25
13
1,272 Views
Last Modified: 2008-02-26
I can call mathematical / statistical excel spreadsheet functions directly from access but date functions are not recognised

e.g. the CHI or Sinh functions gives the right answers:
MsgBox Excel.Application.ChiInv(0.05, 10)
MsgBox Excel.Application.Sinh(1)

Date functions such as Today or Networkdays work within Excel but gets runtime error 438 when called, as below, from Access {Object does not support this property or method}
MsgBox Excel.Application.NETWORKDAYS("11/11/2003", "23/11/2003")
MsgBox Excel.Application.TODAY()

Even the older style setup Excel.Application.WorkSheetfunction.NETWORKDAYS..........etc doesn't work!!!

The library references are ticked. So whats going on here???
John
0
Comment
Question by:jjjtuohy
  • 7
  • 4
13 Comments
 
LVL 23

Expert Comment

by:heer2351
ID: 9821605
Try it like this:

  Dim xl As Object
 
  Set xl = CreateObject("Excel.Application")
  MsgBox xl.evaluate("today()")

For NETWORKDAYS make sure the Analysis addin is activated in Excel.
0
 
LVL 3

Author Comment

by:jjjtuohy
ID: 9823688
heer,
date1, date2 are input date values

I get a #error in the target textbox when I use:

Dim xl As Object
Set xl = CreateObject("Excel.Application")
Text6.Value = xl.Evaluate("NETWORKDAYS("date1, date2)")

or even when I try
Text6.Value = xl.Evaluate("NETWORKDAYS(" & date1 & "," & date2 & ")")

The Today() function works so maybe the problem is exposing the date variables (???!!)

John
0
 
LVL 3

Author Comment

by:jjjtuohy
ID: 9823734
heer
Important points,
Networkdays is an excel worksheet function. It is not visible in EXCEL VBA even when I use application.worksheetfunction.netw...  in excel vba. How can it be visible in access VBA


There must be an answer here
John
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 3

Author Comment

by:jjjtuohy
ID: 9824329
heer,
Just for your interest, I've constructed the excel networkdays function as a single line access function

GetNumberOfWorkDays=Abs(5 - (Abs(Weekday(sStartDate, 7) - 2) + (Weekday(sStartDate, 7) - 2)) / 2 + (DateDiff("ww", sStartDate, sEndDate, 7) - 1) * 5 + (Abs(Weekday(sEndDate, 7) - 2) + (Weekday(sEndDate, 7) - 2)) / 2)

The evaluate function still does not work.
John
0
 
LVL 3

Author Comment

by:jjjtuohy
ID: 9826309
Heer,
Evaluate does not work for addin functions. For anybody who may find this entry of interest, I have included a method to use the analysis toolpak addin in excel from access

Dim objExcel As Excel.Application
   Set objExcel = CreateObject("Excel.Application")

   ' Opens the Analysis ToolPak folder
   objExcel.Workbooks.Open (objExcel.Application.LibraryPath & "\Analysis\atpvbaen.xla")

   objExcel.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)

TOTAL_NETWORK.Value = objExcel.Application.Run("atpvbaen.xla!NETWORKDAYS", START_DATE.Value, END_DATE.Value)

   objExcel.Quit
   Set objExcel = Nothing

This function is inclusive of start and end dates. My previous function is exclusive of the start date. To make the Networkdays function exclusive of start date, simply add start date to the holiday list ie replace the fifth line with:
TOTAL_NETWORK.Value = objExcel.Application.Run("atpvbaen.xla!NETWORKDAYS", START_DATE.Value, END_DATE.Value,START_DATE.Value)
In this case it doesn't seem to need encapsulation in NameValue()

Heer,thank you for your time. I'm sorry I could not in honesty give you the points because you've been brilliant help over the months. I hope (optimistically) that I may have added a new item to your knowledge. I will ask the moderator to PAQ this question.
Regards,
John
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9828460
John, no problem.

It was getting late yesterday so I had no time to investigate this any further. However I did have a look at it today and came to an almost identical solution which I will post for reference:

'--------- Start code

Sub testNetworkdays()
  Dim xl As Object
 
  Set xl = CreateObject("Excel.Application")

  intRet = fAddInLoaded("Analysis ToolPak - VBA", xl)

  MsgBox xl.Run("Networkdays", DateSerial(2003, 11, 10), DateSerial(2003, 10, 8))
End Sub

Function fAddInLoaded(strAddInTitle As String, xl As Object) As Integer
   ' Finds if an add-in is loaded and attempts to
   ' load it if it is not. Returns zero if the
   ' add-in is loaded, 1 if the function had to
   ' load the add-in, -1 if the add-in could
   ' not be located and -2 if an error occurred.

Dim adnAddIn As AddIn

On Error GoTo fAddInLoadedError

' Search through add-ins collection for
' our add-in...
For Each adnAddIn In xl.AddIns
    If adnAddIn.Title = strAddInTitle Then
        ' Found it! Is it installed?
        ' Return success value
        fAddInLoaded = 0
        If Not adnAddIn.Installed Then
            ' If it's not installed then
            ' install it...
            AddIns(strAddInTitle).Installed = True
            ' Return value indicating that we
            ' had to load the add-in
            fAddInLoaded = 1
        End If
        ' Get out now rather continuing
        ' to loop unnecessarily
        Exit Function
    End If
Next
' Add-In was not in the add-ins collection...
fAddInLoaded = -1
Exit Function
fAddInLoadedError:
' unknown error occurred
fAddInLoaded = -2
End Function
0
 
LVL 3

Author Comment

by:jjjtuohy
ID: 9830650
heer,
There's an excellent site on Analysis ToolPak Functions in VBA with that code etc.
http://www.rb-ad.dircon.co.uk/rob/excelvba/tips/index.htm


I thought it only fair to give you 100 points. Jump to:
http://oldlook.experts-exchange.com/Databases/MS_Access/Q_20810008.html

MODERATOR, YOU CAN CLOSE THIS QUESTION NOW.
John
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9833680
LOL that is exactly the site where I found the missing information.
0
 
LVL 3

Author Comment

by:jjjtuohy
ID: 9836419
I know :)
You must be a Google fan too!
John
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9836612
Yep, and a knowledge freak :) main reason for me to post on this forum is to increase my knowledge, your question has added some.
0
 
LVL 3

Author Comment

by:jjjtuohy
ID: 9836683
Add to your knowledge? This site TAUGHT me Access VBA! I hope you've copied the one-line function I constructed to do the same thing. I'm quite proud of it. There's a kind of purity in such challenges.
John
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 9842447
PAQed, with points refunded (250)

Computer101
E-E Admin
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question