This is a simple task for someone familiar with Access & VBA
Access version = 2003
Excel version = 2003 & 2007
Note: I cannot send files to coder due to privacy concerns. Coder will have to design using a mockup based on the description I've provided.
I have a directory which contains daily reports that I receive by email (Well over a 100 files file size is between 20 kb & 315 kb).
The reports are in Excel format
All the files follow the same naming pattern
XXX_Daily Prod Report_(DateTimestamp here)
Column names in the Excel files are all the same
Col1 = CustID
Col2 = CustName
Col3 = Service
Col4 = Cost
Col5 = Errors
Col6 = ShCost
Col7 = TCost
I would like to
1. For each workbook in directory that match the criteria *Daily Prod Report_*.xls or xlsx
2. Import sheet named "Report" into Access db to a table named "Importprocesser" the last field in the record should be the filename of the source excel file
3. Macro then needs to query Importprocesser table and insert into Table named "Adjustments" all the records where the field named "Errors" is equal to or greater than 10.