Solved

AUTOMATION REFERENCES

Posted on 2003-11-25
13
1,258 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now