> 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.
>
> "Ralph" wrote:
>
> > 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.
> >
> > "havocdragon" wrote:
> >
> > > I used the exact code you posted, I added xlapp.visible = True so that I
> > > could see it working or not.
> > >
> > >
> > >
> > > "Ralph" wrote:
> > >
> > > > 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> > > >
> > > >
> > > >
> > > > "havocdragon" wrote:
> > > >
> > > > > 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)
> > > > >
> > > > > "Ralph" wrote:
> > > > >
> > > > > > 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"
> > > > > >
> > > > > > "havocdragon" wrote:
> > > > > >
> > > > > > > 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?