Need To Export Excel Data To Access Database Daily

[NFLnut]

I am VERY new to Access, and have a spreadsheet that is generated every day that I need to selectively export data from to an Access database. My staff creates a new spreadsheet/workbook every day (which are named "072614" for today, "072714" for tomorrow, etc) and I download these from the office computer every night. On each row of the spreadsheet, a customer record is created, and some customers end up having a balance due which I need to create a database for to keep track of. Additionally, there are two (actually three, but on the sample sheet that I am sharing, I only entered two) different types of balance due that I have to keep separate so that my staff can then work with it daily in a separate database and update accordingly as the customer makes payments. I would like to add each day's balance dues to one continuously growing database. If a customer row has an entry in either one of the "Balance Due" columns, then a record will be created in Access, otherwise the row will be skipped and no customer record will be created. Also, eventually I would like it to search for an existing customer record for the same name and birthdate (this sample sheet doesn't have birthdates for any of the 'customers,' but on the actual sheets that we create daily, every customer's birthdate is entered) and append that customer's existing Access record rather than creating a new one.

What would be the best and easiest way to do this? I have tried some tutorials on how to export data to Access, but none of them really fit what I need to do. Plus, as I said, I am very new to Access as the last 20 years I have worked almost exclusively with Excel. The spreadsheets contain a lot of VBA code to expand column widths when a cell is selected for my staff to read codes and long descriptions in a drop down list, so I don't want to 'break' those when scraping data to the database.

I tried importing an Excel spreadsheet, but when I do so (perhaps because the spreadsheets are worked upon by my staff with Excel 2002 -- because they prefer the older version and they are reluctant to learn the ribbon -- and the spreadsheets are saved in an .XLS format(?). When I used the "Ribbon --> External Data --> Import & Link --> [from] Excel," I always get an error ("The wizard is unable to access information in the file. Please check that the file exists and is in the correct format"). Also, I would like to automate this process as much as possible, as it will have to be done every day. My actual Excel spreadsheet (multiple pages .. probably about 40 worksheets, but the first worksheet is the one that I will be scraping the data from) is loaded with VBA that I created, so I am familiar with VBA programming, but I am by no means an expert or even an advanced VBA programmer.

Attached is a sample of the main page of the spreadsheet, the best I could figure out how to paste an existing spreadsheet:

Working Daysheet-3.xls


Balance Due Balance Due
Bill # Last Name First Name MI Birthday Proc Code Proc Code Proc Code Proc Code Diagnosis Diagnosis Charge Spcl Charge Type # 1 Type # 2


1 Smith Bob 50501 50501 360.08 360.04 $200.00 - 120 20
2 Smith Mary 50503 360.01 $20.00 -
3 Jones Jim 50506 50505 360.03 $30.00 - 10
4 Marks Cindy 50508 360.05 $20.00 - 10
5 Bellini Martin 50501 50504 360.07 $100.00 -
6 Grant Frances 50503 360.02 $20.00 -
7 $- -
8 $- -
9 $- -
10 $- -