Creating a database from multiple files

[TubbzUK]

Hi,

I have a folder filled with reports from shifts.
Each file has a worksheet in it called batch summary.
this has multiple lines of data that hold information on batches.

What i need to happen is when the user hits a button, it will loop through the batch summary and write each row to another workbook called database.
if the row has no value in column D then it will skip to the next row.

I had got this working with microsoft Access and writing the rows to a table in a database. low and behold i have been told that acess is no option.

an example of a shift report file is attached called zz1.xlsm (batch summary work sheet is the one i need to extract the data from) and the code for writing it to the database is below

Please Login or Register  to view this content.

my main question is how can i replicate the code above to do the same thign to an excel work book instead of an access database?

it needs to open the work book, loop through copy row from batch summary if column D <> "" and paste row into next empty line of in the database file, save both files and exit.

Been racking my brains over this and do not know where to begin...

Appreciate any help,

Regards,

Alan

[TubbzUK]

forgot to mention that in the access version there is a check to see if there has been a previous upload. this checks to see if there are any records with a matching filename and if there is delete them before uploading the new entries.
I need to try and keep this functionality by checking if there isany rows with that filename and then deleting them before copying if i can.

Thanks,

[xlbiznes]

try this , this is a generic vba to merge data with condition and also has an option to select a specific sheet.

[TubbzUK]

hi, thanks for the reply.

had a quick look and will try using today.
If i changed the condition to D1 <>""
will that copy every row from the sheet i specify into the workbook that is highlighted in the b1?

If thats the case then thats exactly what i am looking for ;-)

Regards,

Alan

[TubbzUK]

hi,
just been lookign in more detail and what i am after is the oppsite of this.

What i have is a blank template that people fill in. this generates a sheet called batch summary which contains the data from everything entered. then when they click a button this will be uploaded to a seperate workbook that is in oe place that everyone always writes too.

Then once i have the uploading bit working, i need it to make sure its only uploaded once. SO before it writes the batch sumamry to it, it checks for any lines with that filename and if it finds some it deletes them before writing the new lines in.

[TubbzUK]

Hi,

made a solution now.

the code is as follows

Please Login or Register  to view this content.

Thanks for the help.

[xlbiznes]

You are welcome, glad i could help.