variances in two worksheets with ado and vba

Hi all,

The 2 worksheets have similar data which I need to check for variances .

The column headings from A to L are:Status, Empl ID, Name, Dept,Job Code, Job Family, No Mths Emp, FTE; Start Mth, End Mth, Total Hrs, Area.

Sheet1 is the "Empl ID" old data and sheet2 the "Empl ID" data for checking against sheet1 - the real workbook may have over 3000 by two records to cross reference

sheet3 is to list any possible changes

the code below is from watersev which solved a similar problem

Please Login or Register  to view this content.

this is where i need help to construct a query/array to suit

Please Login or Register  to view this content.

thanks

try one close but no banana

Please Login or Register  to view this content.

try two this will find changes in two identical table layout

Please Login or Register  to view this content.

looking like ado to vba may not quite be what i need and may have to stick with

Please Login or Register  to view this content.

Not 100% sure I follow but in terms of flagging differences between existing items:

Please Login or Register  to view this content.

My SQL is pretty average but you might find you can create a UNION query to add to the above items since added to the 2nd set and those present in the 1st set but removed from 2nd (right joins etc...)

You didn't say what was wrong with your attempts, but I'm guessing the parentheses in the WHERE clause:

Please Login or Register  to view this content.

Do you need a comparion row by row ? (eg. file1.row1 to file2.row1;
file1.row2 to file2.row2, file1.row3 to file2.row3 ......etc.)
To compare 2 Excelfiles:

Please Login or Register  to view this content.

But you can also use Excel's VBA

Please Login or Register  to view this content.

Wow thanks fellas for the two approaches,

To clarify for each "Empl ID" in sheet2 - search in sheet1 "Empl ID" for possible match then if found check to see if there are any differences/changes in data between sheets for that “Empl ID”

@ Donkeyote - your layout has explained a lot about the ado structure - I will research create a UNION and SQL query in general.

@ snb - two table approach - didnt know it was possible and yes need to loop and compare rows .

Originally Posted by pike

To clarify for each "Empl ID" in sheet2 - search in sheet1 "Empl ID" for possible match then if found...

If you only need to detail those rows from sheet2 that are found also in sheet1 then you shouldn't need to UNION etc

The UNION of queries would I think be necessary should you wish to detail not only the EmplID's that exist yet vary in both sets but also those EmplID's that exist in one set and not the other (either/or)

Hi Do
thanks for your tuition and invaluable help.
found a good tutorial site

http://www.w3schools.com/sql/default.asp

your code works as desired - apologies for wasting your time - but in the interim i added a few helper columns and combining lookup, lodgic and error functions - excel processed and crunched the data in a blink of an eye.

I also tried to learn something from your question.

I came up with:

Please Login or Register  to view this content.

hi snb

thanks for the interest
I have made some changes workbook name.path and sheets ranges, but get an "to few parameter expected 24" error .
I'll test it again tomorrow with the data - all it take is one space to many

Please Login or Register  to view this content.

thanks

It's rather tricky to use this in the same workbook (because of sheets(1).cells.clear.)
But if you insist:

Please Login or Register  to view this content.

Just as an FYI, you should avoid using ADO on open workbooks due to memory leaks.