Hi, I hope somebody can help as this is an issue I have struggled with for many years and always given up.
Each day I recieve excel files from one of my customers. They are an export from their database that
contain and order number, address, items they have orderded and the quantity orderded.
Each day I have to print these out and then manually type them back into an access database that we use to
control the shipping.
Given that they are in excel format and are a single workbook there must be a way to extract the data
automaticly and place it into a table ready to be imported.
The only spanner in the works is that the data for the order number and address is always in the same cell
but the product and the quantity can sometimes be B30, B32 or B33. Also the amount of products on each
page can change so sometimes there can be 10 products, sometimes only 1.
What I need is a way to place all these files in a folder. The file name is also the order number followed
by a -uk for UK - Europe, - Ireland and so on. then have something run through the files and produce me
with a table that says.
Order number, address, product1, quantity
Order number, address, product2, quantity
Order number, address, product3, quantity
and so on.
Then I can simply import this into my access database.
Can anybody help? I have tried so many times and always failed
Can post example order files if needed.
Each day I recieve excel files from one of my customers. They are an export from their database that
contain and order number, address, items they have orderded and the quantity orderded.
Each day I have to print these out and then manually type them back into an access database that we use to
control the shipping.
Given that they are in excel format and are a single workbook there must be a way to extract the data
automaticly and place it into a table ready to be imported.
The only spanner in the works is that the data for the order number and address is always in the same cell
but the product and the quantity can sometimes be B30, B32 or B33. Also the amount of products on each
page can change so sometimes there can be 10 products, sometimes only 1.
What I need is a way to place all these files in a folder. The file name is also the order number followed
by a -uk for UK - Europe, - Ireland and so on. then have something run through the files and produce me
with a table that says.
Order number, address, product1, quantity
Order number, address, product2, quantity
Order number, address, product3, quantity
and so on.
Then I can simply import this into my access database.
Can anybody help? I have tried so many times and always failed
Can post example order files if needed.
