Batch import Excel files into Access database(repost)
$30-80 USD
完了済み
投稿日: 12年以上前
$30-80 USD
完了時にお支払い
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
ColA = Blank
ColB = Facility
ColC = RecordNumber
ColD = Name (Last, First)
ColE = SeqID
ColF = Room
ColG = Total Score
ColH = Status
Coli = LPoint
Colj = APoint
Colk = Cpoint
Coll = Epoint
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 (Each workbook contains 2 sheets)
3. Macro then needs to query Importprocesser table and insert into Table named "Adjustments" all the records where the field named "Total Score" is equal to or greater than 10 and Status = 'A'