Excel add in functions not working when being opened through acces

[Guest]

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?

 

[Armando Blanco]

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

 

[Guest]

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"

 

[Guest]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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