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!

Norie · Apr 20, 2011

What exactly is 'Excel' in the code? Is it an instance of Excel created somewhere else?

bigmeadow24 · Apr 20, 2011

Honestly, I'm not completely sure. In the Tools -> References menu of the VBA Editor window, I checked the box next to the Microsoft Excel 14.0 Object Library, and then was able to call the Networkdays function. Didn't do anything else (as far as I know).

Norie · Apr 20, 2011

Why not use the function you found? It does work after all.

Perhaps it could even be rewritten for exactly what you need?

bigmeadow24 · Apr 20, 2011

The function I found gives slightly different answers than the Excel formula, generally when a start or end date falls on a weekend (but not always). I haven't been able to track down where exactly the code is deviating from the Excel formula, and I'd rather not just live with the results. The speed with which the function operates is more just an annoyance, I guess.

Anyway, I may have found a work around:

Code:

Public Function Networkdays(ByVal StartDate As Date, ByVal EndDate As Date) As Long

Dim objExcel As Excel.Application

Set objExcel = CreateObject("Excel.Application")

Networkdays = Excel.WorksheetFunction.Networkdays(StartDate, EndDate)

objExcel.Quit
Set objExcel = Nothing

End Function

I'm also keeping Excel closed while I run the query. However, when I run the query now the little "working" icon next to the mouse icon keeps blinking on and off, as if it's cycling through the function multiple times (once per record, maybe?). Also, it's slower. Makes me think the possible solution I found is less than ideal.

boblarson · Apr 20, 2011

bigmeadow24 said:

Code:

Public Function Networkdays(ByVal StartDate As Date, ByVal EndDate As Date) As Long
 
Dim objExcel As Excel.Application
 
Set objExcel = CreateObject("Excel.Application")
 
Networkdays = Excel.WorksheetFunction.Networkdays(StartDate, EndDate)
 
objExcel.Quit
Set objExcel = Nothing
 
End Function

You should be referencing objExcel and not Excel:

Rich (BB code):

Networkdays = objExcel.WorksheetFunction.Networkdays(StartDate, EndDate)

The quickest way to have problems with Excel and Access is to refer to Excel without going through an instantiated object. See here for an article I wrote about that (and that the Microsoft Access Team published for me in the Access Team Blog back in September of 2010).

Norie · Apr 20, 2011

I just tried your original code and it works fine.

Still wouldn't recommend using the Excel function for this.

As Bob has said you could run into problems with the code you posted, and I'm not sure what sort of impact creating an instance of Excel every time the function is called could be.

bigmeadow24 · Apr 20, 2011

boblarson said:
You should be referencing objExcel and not Excel:

Rich (BB code):

Networkdays = objExcel.WorksheetFunction.Networkdays(StartDate, EndDate)

The quickest way to have problems with Excel and Access is to refer to Excel without going through an instantiated object. See here for an article I wrote about that (and that the Microsoft Access Team published for me in the Access Team Blog back in September of 2010).

Thank you! Does this make the quit statement unnecessary or do I still need that?

bigmeadow24 · Apr 20, 2011

Norie said:
I just tried your original code and it works fine.

Still wouldn't recommend using the Excel function for this.

As Bob has said you could run into problems with the code you posted, and I'm not sure what sort of impact creating an instance of Excel every time the function is called could be.

Gotcha. I'm going to keep fiddling with the user-defined function and see if I can get it to work more quickly/accurately. Thanks for the advice.

Also, some of the things I read online made it seem like allowing Access to reference the Excel libraries was more like a one-time import type thing. Did I misinterpret that, is there a better way of using the Excel function, or did I just kind of throw myself down the rabbit hole on this one?

Norie · Apr 20, 2011

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

Run-Time Error when using an Excel function in Access

bigmeadow24

Board Regular

Excel Facts

Norie

Well-known Member

bigmeadow24

Board Regular

Norie

Well-known Member

bigmeadow24

Board Regular

boblarson

MrExcel MVP

Norie

Well-known Member

bigmeadow24

Board Regular

bigmeadow24

Board Regular

Norie

Well-known Member

Similar threads

Forum statistics

Share this page

Run-Time Error when using an Excel function in Access

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

MrExcel MVP

Well-known Member

Board Regular

Board Regular

Well-known 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