Run-Time Error when using an Excel function in Access

bigmeadow24 · Apr 20, 2011

I'm trying to use the Networkdays function from Excel in Access 2010 - I found a vba function online, but it's a bit on the slow side so I was wanted to try this and see if it was faster. I ended up with this code:

Code:

Public Function Networkdays(ByVal StartDate As Date, ByVal EndDate As Date) As Long
    
Networkdays = Excel.WorksheetFunction.Networkdays(StartDate, EndDate)

End Function

When I run a query that uses this function, it runs fine and displays what I believe to be the correct results. However, as soon as I click anything, I get the following error:

"Run-time error '-2147417843 (8001010d)':
Automation error
An outgoing call cannot be made since the application is dispatching an input-synchronous call."

At this point the dialogue box that error pops up in won't go away (regardless of whether I click "End", "Debug", or try to x out of the dialogue box), and I have to force close Access. Googling that error got me a whole bunch of results I couldn't even begin to understand. I'm hoping there's just some sort of "end" or "stop" vba code I can use to avoid this, or something similar. Thanks!

bigmeadow24 · Apr 20, 2011

Norie said:
Can you post the code for the UDF? Or a link to where you found it?

It's the fNetWorkDays function here:

http://www.utteraccess.com/forum/fNetWorkdays-fAddWorkDay-t1349593.html

bigmeadow24 · Apr 20, 2011

Well, regardless of whether I'm making a mistake by using the Excel formula in lieu of the UDF, the issue is rendered moot by the fact that the Excel formula method is INSANELY slow. So that's out the window. Thanks for taking the time to respond anyway.

Norie · Apr 20, 2011

That could be something to do with creating all those instances of Excel, even though they are short-lived.

By the way, do you want to include (or I suppose it's exclude really) holidays?

bigmeadow24 · Apr 25, 2011

Norie said:
That could be something to do with creating all those instances of Excel, even though they are short-lived.

By the way, do you want to include (or I suppose it's exclude really) holidays?

Yeah, I talked to a developer that sits next to me that thought the same thing - while I assumed Access was just grabbing a formula from Excel and then using it, it was probably "opening" Excel, using the formula, and then closing Excel. Oh, technology.

As for holidays, yes, I'd like to exclude them. I'm currently doing so (I believe) using a holiday table named "tblHolidays" per the UDF's instructions.

mallard1976 · Apr 26, 2011

is this function faster (it does look simpler) ?

Code:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name:     WorkingDays2
' Inputs:   StartDate As Date
'   EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date:     May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM Holidays", dbOpenSnapshot)
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
intCount = 0
Do While StartDate <= EndDate
rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
StartDate = StartDate + 1
Loop
WorkingDays2 = intCount
Exit_WorkingDays2:
Set rst = Nothing
Set DB = Nothing
Exit Function
Err_WorkingDays2:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select
End Function

Run-Time Error when using an Excel function in Access

bigmeadow24

Board Regular

bigmeadow24

Board Regular

Excel Facts

bigmeadow24

Board Regular

Norie

Well-known Member

bigmeadow24

Board Regular

mallard1976

New Member

Similar threads

Forum statistics

Share this page

Run-Time Error when using an Excel function in Access

bigmeadow24

Board Regular

bigmeadow24

Board Regular

Excel Facts

bigmeadow24

Board Regular

Norie

Well-known Member

bigmeadow24

Board Regular

mallard1976

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock