VBA CODE - COPY DATA FROM EXCEL WORKBOOKS TO ACCESS TABLE

[CBS]

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 ROWS 8 to ROW 250 in each 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….

 

[Andrew Fergus]

Hi

Wow, that's quite a bit to get through. Regarding an identifier for reconciliation purposes, have you considered using a date field where the value defaults to today? Any time you add records to the table then you can find the records uploaded on a particular day using a query filtered by date.

How far have you got with the rest of this? There will already be a number of threads on MrExcel for each of the parts you described, the challenge will be combining all of the steps into one use-able routine. If you can tell us how far you have got then we can help you take this further, if needed.

Andrew