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 $- -

[alansidman]

In looking at your excel sheet there are many issues that will cause problems trying to integrate with Access.

1. Your table does not start in Cell A1. All field names must be in the first row of your spreadsheet
2. You have the same field names for multiple fields. Access cannot deal with this. Each field must be uniquely named
3. You have empty columns in your table (no field name). This confuses Access.

You need to make sure that any data you want to import or link to Access needs to be normalized. Suggest you read the following on Relational Data Bases and how they need to be set up. Once you have a normalized record set, then it will be easy to discuss how to get the data into Access.

http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[alansidman]

crossposted to: http://www.mrexcel.com/forum/microso...ase-daily.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).