Purpose: To retreive monthly the total headcount for UK permanent & temp employees only, broken down into Building 1 and Building 2.
I have a headcount spreadsheet that holds the following details in separate worksheets:
permanent employees (perm_emp) - only need three columns to perform calculations "Employee ID", "Building", "Country" (There are 26 columns)
temp employees (temp_emp) - "Employee ID", "Building", "Country"
So far can import the worksheets with the attached code that I have reused from someone elses post (thanks!).
Now I need to:
1) Perform a distinct count on the Employee ID, only where the Employee is from the UK and either Building A or B (A&B are only in the UK). This is for both Permanents and Temps.
2) Append this figure as 'Total Headcount Building 1' and ''Total Headcount Building 1' to a Results table as the previous months results. Currently have 3 months data that has been manually entered working on automating all data going in to this database. So the logic is you would be importing data on the 1st working day of the month for the previous months data. Is there a way of checking what the last months entry was and then automatically date stamping the data being imported?
e.g Date_Created Total Headcount Building 1 Total Headcount Building 2
Jan-07 1000 500
Last Import== > Feb-07 1012 497
Auto Date ==> Mar-07 1015 515
3) Deleting the tables Headcount_Import_perm_emp & Headcount_temp_emp.
After any tips, pointers, code for 1 - 3 above. Am I going about this the right way? (I'm not asking for someone to write the code for me, just pointers to any examples is fine for each section)
Dim objXL As Object, xlSht As Object
Dim strWSname As String
Dim i As Integer, j As Integer
Dim sFile, sTable
sFile = "\\anetworkshare\test\test.xls"
sTable = "Headcount_Import"
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Open sFile, , True
For i = 1 To .worksheets.Count
strWSname = .worksheets(i).Name
Set xlSht = .activeworkbook.worksheets(i)
'Get header names
For j = 1 To xlSht.usedrange.Columns.Count
' Test msg box
' MsgBox xlSht.cells(1, j).Value
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"" & sTable & "_" & strWSname & "", sFile, True, "" & strWSname & "!"
Set objXL = Nothing