Excel add in functions not working when being opened through acces

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

havocdragon - 19 Sep 2006 17:44 GMT

Hey all.

So I have a procedure I run in access, that in part of it, opens excel, and
runs a macro from excel. However this uses the function 'networkdays' which
is an excel add in. When I run the code from excel directly it works
fine...but when access opens it, it does not recognize any add-in functions.

Is there any way around this?

Reply to this Message

Armando Blanco - 19 Sep 2006 20:10 GMT

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

HTH

Armando Blanco
Microsoft MVP

> Hey all.
>
[quoted text clipped - 7 lines]
>
> Is there any way around this?

Reply to this Message

Ralph - 19 Sep 2006 23:17 GMT

I was able to solve this by opening atpvbaen.xla in my code. The code below
opens a workbook from Access and adds the Networkdays. Maybe you could adjust
your macro in Excel to do the same. atpvbaen.xla is the Analysis ToolPak -
VBA, you will need to select that Add In in Excel too.

Dim xlApp As New Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWbA As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strPath As String

strPath = CurrentProject.Path & "\"
Set xlWb = xlApp.Workbooks.Open(strPath & "New Microsoft Excel
Worksheet.xls")

Set xlSheet = xlWb.Worksheets("Sheet2")

Set xlWbA = xlApp.Workbooks.Open(xlApp.Application.LibraryPath & _
"\Analysis\atpvbaen.xla")

xlSheet.Range("C1").Formula = "=NETWORKDAYS(A1,B1)"

xlWb.Save
xlWb.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlWb = Nothing
Set xlWbA = Nothing
Set xlApp = Nothing
MsgBox "done"

> Hey all.
>
[quoted text clipped - 4 lines]
>
> Is there any way around this?

Reply to this Message

havocdragon - 20 Sep 2006 20:52 GMT

Didnt work, I tried several different methods with the below code, but
ultimately end up getting a #Value error (yes I know how to use the
networkdays function).

Adversely, if I load that atpvbaen.xla manually after opening an excel sheet
from access it appears to work (which it wouldnt if I tried using those
formulas after opening excel through access)

> I was able to solve this by opening atpvbaen.xla in my code. The code below
> opens a workbook from Access and adds the Networkdays. Maybe you could adjust
[quoted text clipped - 36 lines]
> >
> > Is there any way around this?

Reply to this Message

Ralph - 20 Sep 2006 22:21 GMT

Hard to help you without seeing your code, one line I forgot in mine was to
close xlWBA at the end of the sub.

You might try:

http://support.microsoft.com/kb/198571/en-us

> Didnt work, I tried several different methods with the below code, but
> ultimately end up getting a #Value error (yes I know how to use the
[quoted text clipped - 44 lines]
> > >
> > > Is there any way around this?

Reply to this Message

havocdragon - 21 Sep 2006 18:35 GMT

I used the exact code you posted, I added xlapp.visible = True so that I
could see it working or not.

> Hard to help you without seeing your code, one line I forgot in mine was to
> close xlWBA at the end of the sub.
[quoted text clipped - 51 lines]
> > > >
> > > > Is there any way around this?

Reply to this Message

Ralph - 22 Sep 2006 14:36 GMT

The code looks for a Date in Cells A1 and B1 on Sheet2, is it possible you
did not enter dates in those cells? That is the only way I could get it to
retun #Value. When testing with dates in those cells and not opening the xla
it returned #Name.

> I used the exact code you posted, I added xlapp.visible = True so that I
> could see it working or not.
[quoted text clipped - 54 lines]
> > > > >
> > > > > Is there any way around this?

Reply to this Message

havocdragon - 28 Sep 2006 18:49 GMT

It's giving me that error because the formula does not exist in excel when I
open excel from access. It's easy to proove. When I open excel normally, I
can go to Insert>Function, and there is networkdays. When I open an excel
spreadsheet (even through the method you gave), i go to Insert>Function and
networkdays is not there, nor are any formulas added through the extra
add-ins.

What version of excel do you have? I am using 2000.

> The code looks for a Date in Cells A1 and B1 on Sheet2, is it possible you
> did not enter dates in those cells? That is the only way I could get it to
[quoted text clipped - 59 lines]
> > > > > >
> > > > > > Is there any way around this?

Reply to this Message

Ralph - 29 Sep 2006 20:46 GMT

Excel 2003

The following will load the addin too.

Dim xlApp As New Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWbA As Excel.Workbook
Dim xlA As Excel.AddIn
Dim xlSheet As Excel.Worksheet
Dim strPath As String

strPath = CurrentProject.Path & "\"
Set xlWb = xlApp.Workbooks.Open(strPath & "New Microsoft Excel
Worksheet.xls", 0)

Set xlSheet = xlWb.Worksheets("Sheet2")

Set xlWbA = xlApp.Workbooks.Open(xlApp.Application.LibraryPath & _
"\Analysis\atpvbaen.xla")

Set xlA = xlApp.AddIns.Add(xlApp.Application.LibraryPath &
"\Analysis\atpvbaen.xla")
xlA.Installed = True

xlSheet.Range("C1").Formula = "=NETWORKDAYS(A1,B1)"

xlWb.Save
xlWb.Close
xlWbA.Close
Set xlA = Nothing
Set xlSheet = Nothing
Set xlWb = Nothing
Set xlWbA = Nothing
Set xlApp = Nothing
xlApp.Quit

> It's giving me that error because the formula does not exist in excel when I
> open excel from access. It's easy to proove. When I open excel normally, I
[quoted text clipped - 68 lines]
> > > > > > >
> > > > > > > Is there any way around this?

Reply to this Message