How do I use NETWORKDAYS (excel function) in Access?

[khughes46]

I am trying to use NETWORKDAYS, an Excel function in Access, without any success. In my research I understand I have to somehow call the function from Excel into Access. Here is where I need the help. I have seen many examples of VBA code, no 2 are the same nor do they make sense. I need to know just how to code the call. I have been on this for 2 days and am just getting more frustrated. I thought this would be more simpler than it is. Please help.

[trevor40]

I am trying to use NETWORKDAYS, an Excel function in Access, without any success. In my research I understand I have to somehow call the function from Excel into Access. Here is where I need the help. I have seen many examples of VBA code, no 2 are the same nor do they make sense. I need to know just how to code the call. I have been on this for 2 days and am just getting more frustrated. I thought this would be more simpler than it is. Please help.

unsure if you can use that in access, but you can open an excel sheet from access, manipulate the data on the sheet to get a result and then copy that into an access form field.

and here's another different sample.

still a big work in progress, used to provide managment with time sheets in excel format, once the link is made data can be shifted between excel and access either way.

try using a pre-formatted sheet with holidays allready listed.

Private Sub Command10_Click()
'############
'############
Dim strPath As String
Dim rst As DAO.Recordset
Dim stDocName As String
Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
Dim Data_Range
Dim Worksheet_Name
Dim db As Database
Dim rs As Recordset
Dim headercell, firstcellref, nextname, FirstName, linecount, nextcellref, bb, CC, dd, rangestart, rangeend, Mt
Dim aa As Integer
sdt = Format(start_date, "dd-mm-yy")
edt = Format(End_date, "dd-mm-yy")
If IsNull(Me.employee_selected) Then
t = MsgBox("Please select an Employee First", vbOKOnly, "Missing Information")
Exit Sub
Else
xx = Me.employee_selected
fn = DLookup("[first name]", "[employees]", "[barcode] = " & Me.employee_selected)
Ln = DLookup("[last name]", "[employees]", "[barcode] = " & Me.employee_selected)
ns = DLookup("[Normal Start Time]", "[employees]", "[barcode] = " & Me.employee_selected)
ne = DLookup("[Normal End Time]", "[employees]", "[barcode] = " & Me.employee_selected)
End If
gg = "C:\aaa\Employee Time Report for - " & fn & ", " & Ln & ", " & sdt & " to " & edt & ".xls"
t = Len(Dir(gg))
If t = 0 Then
GoTo keepgoing1
Else
t = MsgBox("File already exists, Delete file and continue ?.", vbYesNo, "")
If t = vbYes Then
Kill gg
Else
Exit Sub
End If
End If
keepgoing1:
' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output", gg, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output with lunch", gg, True
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True
Excel_Workbook.Worksheets(1).Name = "Employee Time Report"
Excel_Workbook.Worksheets.Add ''After:=Sheets(Sheets.Count)
Set Current_Worksheet = Excel_Workbook.Worksheets("sheet1")
Current_Worksheet.Range("A1").Value = "Yes"
Current_Worksheet.Range("A2").Value = "No"
Current_Worksheet.Range("A4").Value = "1"
Current_Worksheet.Range("A5").Value = "2"
Current_Worksheet.Range("A6").Value = "3"
Current_Worksheet.Range("A7").Value = "4"
Current_Worksheet.Range("A8").Value = "5"
Current_Worksheet.Range("A9").Value = "6"
Current_Worksheet.Range("A10").Value = "7"
Current_Worksheet.Range("A11").Value = "8"
Current_Worksheet.Range("A12").Value = "9"
Current_Worksheet.Range("A13").Value = "10"
Current_Worksheet.Range("A14").Value = "11"
Current_Worksheet.Range("A15").Value = "12"
Current_Worksheet.Range("A4:A15").NumberFormat = "0"
Excel_Workbook.Worksheets(1).Visible = False
Excel_Workbook.Worksheets("Employee Time Report").Tab.ColorIndex = 37
Set Current_Worksheet = Excel_Workbook.Worksheets("Employee Time Report")
Excel_Workbook.Worksheets("Employee Time Report").Select
Current_Worksheet.Cells.Select
With Selection
Current_Worksheet.Cells.HorizontalAlignment = xlRight
Current_Worksheet.Cells.Font.Name = "Times New Roman"
End With
Current_Worksheet.PageSetup.Orientation = xlLandscape
Current_Worksheet.Range("A1:P1").HorizontalAlignme nt = xlCenter
Current_Worksheet.Range("A1:P1").VerticalAlignment = xlCenter
Current_Worksheet.Range("A1:P1").Font.Bold = True
Current_Worksheet.Range("A:A").NumberFormat = "d/mm/yy;@"
Current_Worksheet.Range("D:E").NumberFormat = "h:mm"
Current_Worksheet.Cells.SpecialCells(xlCellTypeLas tCell).Select
last_cell = Current_Worksheet.Cells.SpecialCells(xlCellTypeLas tCell).Address
rng1 = "A$1:P" & Mid(last_cell, 4, 3) ' Mid(Current_Worksheet.Cells.SpecialCells(xlCellTyp eLastCell).Address, 4, 3) - 1"
Current_Worksheet.Range(rng1).Select
With Selection
Current_Worksheet.ListObjects.Add(xlSrcRange, , xlYes, xlYes).Name = "List1"
End With
Current_Worksheet.Range("A$2:P" & Mid(last_cell, 4, 3)).Font.Size = 14
' set totals row
Current_Worksheet.Range("M" & Mid(last_cell, 4, 3) + 2).Formula = "=Sum($M2:$M" & Mid(last_cell, 4, 3)
Current_Worksheet.Range("N" & Mid(last_cell, 4, 3) + 2).Formula = "=Sum($N2:$N" & Mid(last_cell, 4, 3)
Current_Worksheet.Range("O" & Mid(last_cell, 4, 3) + 2).Formula = "=Sum($O2:$O" & Mid(last_cell, 4, 3)
Current_Worksheet.Range("P" & Mid(last_cell, 4, 3) + 2).Formula = "=Sum($P2:$P" & Mid(last_cell, 4, 3)
Current_Worksheet.Range("K" & Mid(last_cell, 4, 3) + 2).Value = "Totals"
Current_Worksheet.Range("K" & Mid(last_cell, 4, 3) + 2 & ":P" & Mid(last_cell, 4, 3) + 2).Borders(xlEdgeBottom).LineStyle = xlContinuous
Current_Worksheet.Range("K" & Mid(last_cell, 4, 3) + 2 & ":P" & Mid(last_cell, 4, 3) + 2).Font.Bold = True
Current_Worksheet.Range("K" & Mid(last_cell, 4, 3) + 2 & ":L" & Mid(last_cell, 4, 3) + 2).Merge
Current_Worksheet.Range("K" & Mid(last_cell, 4, 3) + 2 & ":L" & Mid(last_cell, 4, 3) + 2).HorizontalAlignment = xlCenter

' SORT BY NAME IN THEN DATE IN
Current_Worksheet.Range(rng1).Sort Key1:=Current_Worksheet.Range("A8"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
Current_Worksheet.Range("A:A").ColumnWidth = 8
Current_Worksheet.Range("B:B").ColumnWidth = 4
Current_Worksheet.Range("C:C").ColumnWidth = 7
Current_Worksheet.Range("D").ColumnWidth = 5.5
Current_Worksheet.Range("E:E").ColumnWidth = 7.5
Current_Worksheet.Range("F:F").ColumnWidth = 6.75
Current_Worksheet.Range("G:G").ColumnWidth = 10
Current_Worksheet.Range("H:I").ColumnWidth = 14
Current_Worksheet.Range("J:J").ColumnWidth = 6
Current_Worksheet.Range("K:K").ColumnWidth = 14
Current_Worksheet.Range("L:L").ColumnWidth = 2
Current_Worksheet.Range("M:M").ColumnWidth = 8
Current_Worksheet.Range("N:N").ColumnWidth = 8
Current_Worksheet.Range("O:O").ColumnWidth = 8
Current_Worksheet.Range("P:P").ColumnWidth = 8
Current_Worksheet.Range("A2").Select
Current_Worksheet.Rows("1:1").Insert Shift:=xlDown
Current_Worksheet.Rows("1:1").Insert Shift:=xlDown
Current_Worksheet.Rows("1:1").Insert Shift:=xlDown
Current_Worksheet.Rows("1:1").Insert Shift:=xlDown
Current_Worksheet.Range("A1").FormulaR1C1 = "Employee Time Tracking - " & fn & " " & Ln & ", Normal Work Hours - " & ns & " to " & ne
Current_Worksheet.Range("A1").Cells.HorizontalAlig nment = xlLeft
Current_Worksheet.Range("A1:P1").MergeCells = True
Current_Worksheet.Range("A1:P1").Font.Bold = True
Current_Worksheet.Range("A1:P1").Font.Size = 14
Current_Worksheet.Range("A1:P1").Font.Name = "Times New Roman"
Current_Worksheet.Range("A1:P1").Interior.ColorInd ex = 35
Current_Worksheet.Range("A1:P1").Borders(xlEdgeTop ).LineStyle = xlContinuous
Current_Worksheet.Range("A1:P1").Borders(xlEdgeBot tom).LineStyle = xlContinuous
Current_Worksheet.Range("A1:P1").Borders(xlEdgeLef t).LineStyle = xlContinuous
Current_Worksheet.Range("A1:P1").Borders(xlEdgeRig ht).LineStyle = xlContinuous
last_cell = Current_Worksheet.Cells.SpecialCells(xlCellTypeLas tCell).Address
Current_Worksheet.Range("A5:P5").WrapText = True
Current_Worksheet.Range("5:5").RowHeight = 31.5
Current_Worksheet.Range("5:5").VerticalAlignment = xlBottom
''Current_Worksheet.Columns("J:K").Delete
Current_Worksheet.Range("A$5:i" & Mid(last_cell, 4, 3) - 2).Borders(xlEdgeRight).LineStyle = xlContinuous
Current_Worksheet.Range("A$5:i" & Mid(last_cell, 4, 3) - 2).Borders(xlEdgeTop).LineStyle = xlContinuous
Current_Worksheet.Range("A$5:i" & Mid(last_cell, 4, 3) - 2).Borders(xlEdgeBottom).LineStyle = xlContinuous
Current_Worksheet.Range("A$5:i" & Mid(last_cell, 4, 3) - 2).Borders(xlEdgeLeft).LineStyle = xlContinuous
Current_Worksheet.Range("A$6:i" & Mid(last_cell, 4, 3) - 2).Borders(xlInsideVertical).Weight = xlHairline
Current_Worksheet.Range("A$6:i" & Mid(last_cell, 4, 3) - 2).Borders(xlInsideHorizontal).Weight = xlHairline

Current_Worksheet.Range("M$5:P" & Mid(last_cell, 4, 3) - 2).Borders(xlEdgeRight).LineStyle = xlContinuous
Current_Worksheet.Range("M$5:P" & Mid(last_cell, 4, 3) - 2).Borders(xlEdgeTop).LineStyle = xlContinuous
Current_Worksheet.Range("M$5:P" & Mid(last_cell, 4, 3) - 2).Borders(xlEdgeBottom).LineStyle = xlContinuous
Current_Worksheet.Range("M$5:P" & Mid(last_cell, 4, 3) - 2).Borders(xlEdgeLeft).LineStyle = xlContinuous
Current_Worksheet.Range("M$6:P" & Mid(last_cell, 4, 3) - 2).Borders(xlInsideVertical).Weight = xlHairline
Current_Worksheet.Range("M$6:P" & Mid(last_cell, 4, 3) - 2).Borders(xlInsideHorizontal).Weight = xlHairline
Current_Worksheet.Rows("4:4").Insert Shift:=xlDown
Current_Worksheet.Rows("4:4").Insert Shift:=xlDown
Current_Worksheet.Range("A8:P" & Mid(last_cell, 4, 3) + 2).Font.Size = 16
Current_Worksheet.Range("A8:L" & Mid(last_cell, 4, 3) + 2).Font.Size = 10
Current_Worksheet.Range("A6:P6").Font.Size = 14

' set spaces between list columns
' Current_Worksheet.Range("G7").Interior.ColorIndex = 2
Current_Worksheet.Range("L7").Interior.ColorIndex = 2
' Current_Worksheet.Range("G7").Value = ""
Current_Worksheet.Range("L7").Value = ""
With Current_Worksheet ' set list row titles
.Range("A7").Value = "Date"
.Range("B7").Value = "Day"

.Range("F7").Value = "Time @ Lunch"
.Range("G7").Value = "Lunch End"
.Range("H7").Value = "Time Worked Less Lunch"
.Range("I7").Value = "Miniutes worked"
.Range("J7").Value = ""
.Range("K7").Value = "Actual Hours"
.Range("M7").Value = "Ordinary Hours"
.Range("N7").Value = "Time and a Half"
.Range("O7").Value = "Double Time"
.Range("P7").Value = "Total Hours"
End With

Current_Worksheet.Range("A6").Value = "Data from "
Current_Worksheet.Range("A6:F6").MergeCells = True
Current_Worksheet.Range("A6:F6").HorizontalAlignme nt = xlCenter
Current_Worksheet.Range("H6").Value = "Manager's Amendments"
Current_Worksheet.Range("H6:K6").MergeCells = True
Current_Worksheet.Range("H6:K6").HorizontalAlignme nt = xlCenter
Current_Worksheet.Range("M6").Value = "Payroll Offce to Calculate"
Current_Worksheet.Range("M6:P6").MergeCells = True
Current_Worksheet.Range("M6:P6").HorizontalAlignme nt = xlCenter
'normal time
Current_Worksheet.Range("M8").Formula = "=IF(OR($B8 = " & Chr(34) & "Sat" & Chr(34) & ", $B8 = " & Chr(34) & "Sun" & Chr(34) & "), 0, IF($K8 > 8, 8, $K8))"
'time and a half
Current_Worksheet.Range("N8").Formula = "=IF($B8=" & Chr(34) & "Sun" & Chr(34) & ",0,IF(AND($B8=" & Chr(34) & "Sat" & Chr(34) & ",$K8<=4),$K8,IF(AND($B8=" & Chr(34) & "Sat" & Chr(34) & ",$K8>4),4,IF(AND($B8<" & Chr(34) & "Sat" & Chr(34) & ",$K8>8,$K8<=11),$K8-8,IF(AND($B8<" & Chr(34) & "Sat" & Chr(34) & ",$K8>11),11-8,0)))))"
'double time
Current_Worksheet.Range("O8").Formula = "=IF($B8=" & Chr(34) & "Sun" & Chr(34) & ",$K8,IF(AND($B8=" & Chr(34) & "Sat" & Chr(34) & ",$K8>4),$K8-4,IF(AND($B8=" & Chr(34) & "Sat" & Chr(34) & ",$K8<=4),0,IF(AND($B8<" & Chr(34) & "Sat" & Chr(34) & ",$K8>11),$K8-11,0))))"
'sub totals Row
Current_Worksheet.Range("P8").Formula = "=SUM($M8:$O8)"
' formula for row total hours paid Current_Worksheet.Range("Q8").Formula = "=($O8*2)+($N8*1.5)+$M8"
Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 2).Value = "Employee Verification (Please Sign)"

' With Current_Worksheet.Range("F" & Mid(last_cell, 4, 3) + 2).Validation
' .Delete
' .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet1!$A$1:$A$2"
' .IgnoreBlank = True
' .InCellDropdown = True
' .ShowInput = True
' .ShowError = True
' End With
' With Current_Worksheet.Range("$K8:K" & Mid(last_cell, 4, 3)).Validation
' .Delete
' .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=sheet1!$A$4:$A$15"
' .IgnoreBlank = True
' .InCellDropdown = True
' .ShowInput = True
' .ShowError = True
' End With
Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 2).HorizontalAlignment = xlLeft
Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 5).HorizontalAlignment = xlLeft
Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 6).HorizontalAlignment = xlLeft
Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 5).Value = " '!' Double check the selected number"
Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 6).Value = " 'X' Indicates an error occurred, please correct the information in the corresponding line."
Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 5).Font.Color = -16776961
Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 6).Font.Color = -16776961
Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 5).Font.Bold = True
Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 6).Font.Bold = True

Current_Worksheet.PageSetup.PrintArea = ("$A$1:$P$" & Mid(last_cell, 4, 3) + 8)
Current_Worksheet.Range("A3").Value = "Pay Period - " & Format(Forms![main menu]![start_date], "DDD D, MMM YYYY") & " ~ to ~ " & Format(Forms![main menu]![End_date], "DDD D, MMM YYYY")
Current_Worksheet.Range("A3:P3").MergeCells = True
Current_Worksheet.Range("A3:P3").Font.Bold = True
Current_Worksheet.Range("A3:P3").Font.Size = 14
Current_Worksheet.Range("A3:P3").Font.Name = "Times New Roman"
Current_Worksheet.Range("A7:P7").Font.Size = 10
Current_Worksheet.Range("8:8").Select
Excel_Application.ActiveWindow.FreezePanes = True
Current_Worksheet.Range("A8").Select
Set Current_Worksheet = Excel_Workbook.Worksheets("Employee Time Report")
Excel_Workbook.Worksheets("Employee Time Report").Select
With Current_Worksheet.PageSetup
.PrintTitleRows = ""
.CenterHeader = ""
' .RightFooter = "Date &[Date]"
' .CenterFooter = "Date &[Date]"
.LeftMargin = 0
.RightMargin = 0
.TopMargin = 0
.BottomMargin = 0
End With
Excel_Workbook.SaveAs filename:=gg, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=True
' tt = MsgBox("Output to Excel complete, Use Alt + TAB to switch to Excel and view results", vbOKOnly, "TimeTracK Report")
End Sub

[Missinglinq]

Here's a couple of references on using Excel Functions in Access

Access 2000 (good for 2003 as well, although the Library Reference may be a different version)

http://support.microsoft.com/kb/198571

Access 2007 (Good for 2010, I believe)

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

Linq ;0)>