transferring fields of worksheets in workbooks in a directory on my C: to an Access T

[cbs81]

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 like a temp file in access, then using vba, move it to the actual real file at any time., 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..

I done some research and know we can use ADO or the DoCmd.TransferSpreadsheet function.

I just don’t know how to actually write this synax and the best way to do this.

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

thankyou again to all….

[Rabbit]

Why would you have to do it one element at a time? Can't you just import the entire excel table, do whatever you need to do with that table, and then move the file?

This can probably be done with 5-10 lines of code.

[ADezii]

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 like a temp file in access, then using vba, move it to the actual real file at any time., 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..

I done some research and know we can use ADO or the DoCmd.TransferSpreadsheet function.

I just don’t know how to actually write this synax and the best way to do this.

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

thankyou again to all….

I just want to make sure that I am crystal clear on exactly what you are requesting and illustrating:

1. You have 4 Workbooks representing 4 bus operator clients.
2. Each Workbook consists of 31 individual Worksheets named 1st March 2007, 2nd March 2007,...31st March 2007.
3. The data from all 124 Worksheets has to transferred to and appended to an Access Table named Bus Operator Adjustments.
4. Only Rows 8 through 250 on each Worksheet contain relevant data and need to be imported.
5. Once an individual Workbook has been processed, it should automatically be moved to a Directory named C:\CashReconciliation\Processed.
6. The most recently imported Records from this process need to somehow be uniquely identified, so that at any given time they can be moved back into the actual file so that you can perform your own reconciliation.
7. Is this correct? Did I miss anything?