Comparing 2 Worksheets and Updating Rows Where Differences were Found

[briggss1]

I ran a macro that basically takes 2 worksheets that contain thousands of rows. Each row is an employee record. The current Macro looks at the Person # (ColJ in "Report" and ColC in "Exceptions") and then once a match is found between the two sheets, it then compares all contents on the matched rows, and if any differences occur, then it flags them in an exception report in a 3rd worksheet, "Exceptions". This is real handy in that it allows me (1 person) to instantly look over an entire company's employee roster from 1 month to the next and then see exactly what changed. However, I need to update the original master worksheet to reflect the updates that the exceptions flagged. For example:

On "Report" - This is a sample sheet that contains basic employee data (ignoring my own fields in ColA thru ColG). This is my live working sheet and I need it updated every month. Although this is a sample, in real live application this sheet will contain thousands of lines.

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Current A Test	Current B Test	Proposed A Test	Proposed B Test	HR	Acounts	Supervisor Confirmation Needed	Last name	First name	PersNo	Name of Manager (OM)	Organizational Unit	Position Description	Job Description
2	--	--	--	--	--	--	--	Adams	Penny	6764		Production	Technician 1	A Shift
3	--	--	--	--	--	--	--	Aparte	Isabel	1429	Jim Smith	Production	Technician 1	A Shift
4	x	x	x	x	--	--	--	Asmante	Henry	6272	Frank Laruche	Production	Technician 3	B Shift
5	x	x	x	x	F	--	--	Bering	Charles	9702	Gerald Assmague	Maintenance	Machinist	Compressors
6	x	x	x	x	--	--	--	Carter	Jack	8242	Chad Van Ripple	Administration	Accountant	Accounts Recievable
7	x	x	x	x	--	--	--	Coleman	Ronnie	8215	Kevin Hame	Maintenance	Pipefitter	Pipe Fitter Apprentice
8	x	--	x	--	--	--		Deering	Agustin	2143	JackJones	Production	Technician 3	C Shift
Report

Now each month I get a Corporate report that I just copy and past it into Sheet2 in raw data format. This will contain raw data that is more up-to-date than my "Report" sheet. There will inevitably be a whole buch of minor and mojor differences, things like: name changes, job title changes, hirngs and firings, etc. I need that Corporate report to be compared to my live report and anywhere there is an excpeption, add the entire row to a 3rd sheet ("Exceptions") and highlight the thing(s) that were different. It ultimately ends up in generating an Exceptions Report that looks as follows:

Excel 2003
	A	B	C	D	E	F	G	H
1	Last name	First name	PersNo	Name of Manager (OM)	Organizational Unit	Position Description	Job Description
2	Adams	Penny	6764	Tony O'Donel	Production	Technician 1	A Shift
3	Aparte	Isabel	1429	Jim Smith	Production	Technician 1	B Shift
4	Bering	Charles	9702	Gerald Assmague	Maintenance	Machinist	Compressors	x
5	Coleman	Ronnie	8215	Mark Tennison	Maintenance	Pipefitter	Pipe Fitter Apprentice
6	Stevens	Mike	1882	Bill Smith	Maintenance	Machinist	Compressors	x
7
Exceptions

Now in real application, this may result in hundreds (if not more) lines of minor data changes. Excluding a complete addition (hire) or deletion (fire/quit) which I will flag and remove via the "x" in ColH on "Exceptions", I would like to draft another macro that compares employee numbers ("Report" ColJ and "Exceptions" ColC) and if there is a match, then copy the results from "Exceptions", ColA thru ColG and paste UNFORMATTED values into "Report" ColH thru ColN .

If possible, this would allow me to update my master sheet with the execution of a macro vs spending hours and hours of time going through each update 1 at a time.

Can someone help me with this?

 

[chrispegg]

I've managed to get it working, but there are a couple of things that could easily result in a syntax error.

Here's the working code:

Sub Macro1()
'update existing records on worksheet "Cognos Export" based on "congnos.cgi" worksheet
'add new records to worksheet "Cognos Export" based on "congnos.cgi" worksheet

Dim objConn As Object

Set objConn = CreateObject("ADODB.Connection")

[B] Windows("Cognos.cgi").Activate
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Cognos MOR Extract.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False[/B]

With objConn
  .Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;"""
  .Execute "UPDATE [MOR Oct-2012 v3 (New auto macro).xlsm].[Cognos Export$] CE INNER JOIN [cognos MOR Extract.xlsm].[Main-1$] cgi ON CE.[EMS] = cgi.[EMS] SET CE.[Month] = cgi.[Month], CE.[Date] = cgi.[Date]"
  
  .Execute "INSERT INTO [MOR Oct-2012 v3 (New auto macro).xlsm].[Cognos Export$] SELECT cgi.* FROM [cognos MOR Extract.xlsm].[Main-1$] cgi LEFT OUTER JOIN [MOR Oct-2012 v3 (New auto macro).xlsm].[Cognos Export$] CE ON cgi.[EMS] = CE.[EMS] WHERE CE.[EMS] Is Null"
  .Close

End With

Set objConn = Nothing
 
End Sub

The problems with this are:

1)As I said, the name of the master spreadsheet changes frequently, based on month and revision. I could get around this by preventing users from changing the name of the spreadsheet, however is there a way to use a wild card in the name so that revisions will not matter? The spreadsheet name would always have the same logic, I.e.:

MOR Mmm-yyyy v[x]
MOR Oct-2012 v3

2)The Cognos.cgi spreadsheet is "read only" when it is run - This prevents the code from running the updates. To get around this, I told the VBA to save "Cognos.cgi" as a macro-enabled workbook on the C drive of the machine it is being run on, before the rest of the code is called. This enables read/write, and the code runs perfectly. You can see this part in the bold code above.

3)Heading names cannot contain characters such as "#", unless there is a parenthesis that can be used to indicate that it is text, not code?

• [EMS #] threw up a code error, and there were a couple of other column headers with the hash symbol that also caused problems. Once I'd removed these from both spreadsheets, the code updated the master with the new information from the new extract without error (Yippee!)

I am actually quite astounded at the simplicity of this code - I thought it would require something far, far more complex than this; you have my thanks and respect!

Chris

 

[Fazza]

You've got it working. Well done, Chris; that is great. Addressing your comments.

1. The file name. It might be stating the obvious, but identify the file and then edit the SQL to suit. Perhaps use DIR to loop through a sub-directory finding all files matching the expected pattern & selecting the one with the highest rev number. A simple REPLACE$ can then change the SQL. Are you OK to do that?

2. You've saved a copy of Cognos.cgi file to C drive to work around the read only status. A good solution. BTW, you might additionally delete it at the end of the code?

3. You've worked out/around the headers names.

regards

 

[chrispegg]

Hi Fazza,

After it ran successfully, and I posted, it then refused to run again. I encountered two problems, one of which (I think) is resolved.

1)The VBA refused to save the Cognos.cgi export after the first successful run (it couldn't overwrite, and gave a syntax error).
- I changed the save code to:

    Windows("Cognos.cgi").Activate
    ChDir "c:\"
    ActiveWorkbook.SaveAs Filename:="C:\Temp\Cognos MOR Extract.xlsm", FileFormat:=52, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        ' FileFormat:=52 = xlOpenXMLWorkbookMacroEnabled = xlsm

Which resolved the problem, and the Cognos.cgi saves every time now.

If you could show me how to delete a file using VBA, I would appreciate that as I would prefer the deletion method to leaving files (albeit in C:\Temp).

2)The second part of the VBA will not run. I get:

Run-time error '-2147217911 (80040e09)':
Cannot update. Database of object is read-only.

Now, I checked and double-checked. Both Excel workbooks are open as read/write. I even closed down both sheets, re-opened manually, then commented out the Save part of the VBA to check it. Same error every time. I think it's something to do with the connection, but I can't work out what.

It's baffling as it worked once (only once)...

Chris

ps. I also do not know how to write that REPLACE$ VBA to work around the changing filename, if you have a workaround for this, I would be most grateful

 

[Fazza]

1. VBA to delete a file is : Kill fullfilename

2. I don't know & can't check without a fuller understanding of exactly what is happening. Also, being in Excel 2003 I can't check for your setup.

3. Replace could be like this, or google for other ideas. This is rough & untested. There are other ways.

Sub something_like_this()

Dim strFileName As String
Dim MyFileName As Variant
Dim ar As Variant

'refer help on how to make this next line more specific for your requirements
MyFileName = Application.GetOpenFilename

ar = Split(MyFileName, "\")  'or application.pathseparator
strFileName = ar(UBound(ar))

With objConn
  .Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;"""
  .Execute Replace$("UPDATE [FILENAME].[Cognos Export$] CE INNER JOIN [cognos MOR Extract.xlsm].[Main-1$] cgi ON CE.[EMS] = cgi.[EMS] SET CE.[Month] = cgi.[Month], CE.[Date] = cgi.[Date]", "FILENAME", strFileName)

cheers