PDA

View Full Version : Transfer Data From Excel to Access Table



cbs81
05-14-2007, 07:18 PM
Hi all,

Problem:
Transferring data from workbooks that are stored in a particular directory from Excel to a table in Access. When done, the workbooks that have been processed are automatically moved into a directory named “Processed” . All using VBA.

This is the pretty complex VB Problem that I need some expert solution to.

Overview.
I have created a cash reconciliation application in Excel saved by month for each of my Bus Operator clients. When this cash reconciliation is completed this is saved to a certain directory. For illustrative purposes, I would have the following workbooks at any instance in a directory:

C:\\CashReconciliation

Containing Workbook files:
CashReconciliation of Bribie for March 2007-05-15
CashReconciliation of Sunbus for March 2007-05-15
CashReconciliation of Caboolture for March 2007-05-15
CashReconciliation of Clarks for March 2007-05-15


The layout of Each workbook are exactly the same.

Each tab in each workbook represents the days in a month. For example, for CashReconciliation of Bribie for March 2007-05-15. worksheet1 is named 1st March 2007, worksheet2 is named 2nd March 2007 and so on……

Each worksheet layout is identical, except they obviously contain different data corresponding to the relevant day.

Each line between row 8 to row 250 in each worksheet contains data about each bus driver. Their driver name, driver number, cash takings, adjustment amount, adjustment reasons, tickets sold, machine number (they use to issue tickets).

Now here is the challenge. I need some complex code that will go to each sheet (representing each day of the month)…. go to each record/line and upload each row of data contained in the Excel columns named:

Driver name, Driver number, cash takings, adjustment reasons and tickets sold

To a table I created in Access named : Bus operator adjustments
(field names are the same i.e Driver name, Driver number, cash takings, adjustment reasons and tickets sold )

This whole process needs to be done for each workbook in the directory:

C:\\CashReconciliation

For example (as above):
CashReconciliation of Bribie for March 2007-05-15
CashReconciliation of Sunbus for March 2007-05-15
CashReconciliation of Caboolture for March 2007-05-15
CashReconciliation of Clarks for March 2007-05-15

Once this VBA code is run for each workbook, I would like each workbook automatically moved to:

C:\\CashReconciliation\PROCESSED

Another request is to identify the most recent copied records from this whole process above in the table, using some sort of identifier, so we can do our own reconciliation. Between workbooks and copied information.


That is briefly my problem identification. Im a beginner VBA programmer slowly getting better. Need some help from anyone out there that has done something similar/ or someone that knows VBA like a pro..

Thankyou sooooo much in advance for reading this post and hopefully coming up with a solution for me..

Thankyou again to all….

Edited 15-May-07 by geekgirlau. Reason: convert text to proper case

Ken Puls
05-16-2007, 09:06 AM
Here, have a look at this article (http://www.excelguru.ca/node/18) and see if it helps get you started.

I'd suggest you break this down to bite sized pieces and start with the upload portion first. Once you have that built, you can search our KB for examples of how to loop through different files in a directory.

HTH,