Automating Import Process - Microsoft Community

before question, may offer bit of background , trying accomplish.  work educational book publishing company.  production of material in preparation of peak summer season driven prior annual sales, adjusted various factors, , result of process list of needs printed in order meet , exceed demand.  person responsible purchasing materials meet demands of production has been working process using excel, has largely been time-laden effort.

to save time, created tool in access 2016 imports pos data 1 month @ time (that's limitation beyond control), extracts clean master sku's other "junk" data contained in pos export, breaks apart combos base sku's (the lowest sold skus make combo), , consolidates count of standalone skus former combo counterparts resulting in final count of base skus regardless of how sold.  takes final list , converts final material usage report identifying every component required produce print order, comparing against inventory determine short , positive.  material usage , purchase planning tool.

that said, works, not code programmer.  built tool using variety of access queries, bit of code behind forms, , macros initiate process @ click of button.  knowledge of vba limited.

i want further automate import process of tool.  right now, import specification file expects text file specific generic name in given folder.  each time run tool, import text file reflecting sales given month.  parameter query prompts user define period associated import.  means person saves monthly pos export file must remember month represented generic text filename (ex. salesreportbysku).  today file might contain october data.  next month same file contain november data.  rather save pos export file more descriptive filename (ie. nov2017), import folder contain multiple files of monthly sales data reflecting multiple years. stands, import specification file expects specific filename. don't know how programmatically bypass import specification contains cast-in-stone filename. 

now question:  possible modify (or create anew) import method will:

1.  scan make sure source import folder exists in expected drive location

2.  read files within import source folder , test whether filenames valid (ie. expected)

3.  scan archive table see whether found filename exists within archive , if so, not import it, , conversely,

4.  if scanned import file not found in db archive, import file normally, , lastly

5.  repeat process files within import source folder until files found imported, stamping date , import filename archive table.

okay, better stop before novel turns tolstoy tome. i'm not asking create thing me; i've done it, improve upon , make more streamlined , more efficient, import process.

any feedback, hints, suggestions appreciated.

i think asking many question although complete picture. going should create table stores of setup information such paths, etc.

applicationoptions (table)
optionid         autonumber primary key
optionname   text "import source folder"
optionvalue    text "c:\mydata\pos_csv_files\"

might have record "filenames valid" testing

1) determine if have folder file in can use dir() function:
dim strposfolder string
dim strfirstfilename string

' folder table

strposfolder = dlookup("optionvalue","applicationoptions","optionname='import source folder'")

' find first file in folder

dir(strposfolder)

' subsequent calls dir() return next file in folder

once have working, let know.

Office / Access / Windows 10 / Office 2016