Compare 2 excel files in vb.net

I want to compare two excel workbook, i. The excel workbooks are having multiple worksheets and once the comparison is done, if both the workbooks are same it should move on to the next step,i.

Each worksheets will be having so many headers in one sheet itself, so depending on the header values it shud hit the corresponding screen in mainframes and make changes in dat. Which option will be better for my scenario? I ve heard of datasets but when we are writing the values in the dataset it will be saving the values in one dataset so will it be a trouble? Can anyone suggest me a better option.

If you only want to know whether two workbooks are the same or not, you can use "CompareSideBySide" method. The following code compare the active sheets of the two books. Or, you can compare the worksheet, by loading the range data into variants.

The following code gives us ways to control the details. Range strRangeToCheck ' or whatever your other sheet is. Else ' Cells are different. Worksheets "Sheet1". As the result, the formula in A1 will compare cell A1 in Sheet1 and Sheet2, the formula in B1 will compare cell B1 in both sheets, and so on.

The result will look similar to this:. As you can see in the above screenshot, the formula compares 2 sheets, identifies cells with deferent values and displays the differences in corresponding cells.

Please note that in the difference report, dates cell C4 are presented by serial numbers as they are stored in the internal Excel system, which is not very convenient for analyzing differences between them. To highlight cells that have different values in two sheets with the color of your choosing, use the Excel conditional formatting feature:. Where Sheet2 is the name of the other sheet you are comparing.

As the result, the cells with different values will get highlighted with the selected color:. If you are not very familiar with Excel conditional formatting, you can find the detailed steps to create a rule in the following tutorial: Excel conditional formatting based on another cell value. As you see, it's very easy to compare two Excel sheets by using formulas or conditional formats. However, these methods are not well suited for all-round comparison because of the following limitations:.

When it comes to merging different versions of the same Excel file, the Compare and Merge feature comes in handy. It is especially useful when several users collaborate on the same Excel workbook because it lets you view the changes and comments of all users at a time.

To leverage this feature, be sure to do the following preparations:. Allow Excel to save the workbook if prompted. Turning on the Track Changes feature shares the workbook automatically.

Each person who makes any edits to the shared workbook must save a copy. Now that all initial preparations are done properly, you are ready to combine the copies of a shared workbook. Although, the Compare and Merge Workbooks feature is available in all versions of Excel , Excel , Excel and lower, this command is not displayed anywhere in Excel by default. To add it to the Quick Access toolbar, perform the following steps:. When all of the users have finished working with your shared Excel workbook, you can merge all the copies into one file.

To point out the row and columns with differences, Excel highlights the column letters and row numbers in a dark red color. At the cell level, edits from different users are marked with different colors. To see who made a specific change, just hover over the cell. As you have just seen, Microsoft Excel provides a handful of features to compare data in two or more workbooks. But none of the built-in options is sufficient to comprehensively compare Excel sheets, let alone entire workbooks, spotting all the differences in values, formulas or formatting.

So, if you need advanced and really efficient means to compare two Excel files, then most likely you would have to use one of the third-party tools specially designed for comparing, updating and merging Excel sheets and workbooks. Below you will find a quick overview of a few tools that, in my opinion, are best performers in this area. The Synkronizer Excel Compare add-in can quickly compare, merge and update two Excel files saving you the trouble of searching for differences manually.

If you are looking for a quick and reliable method to compare two Excel sheets or workbooks, you will certainly find helpful the following features of Synkronizer Excel Compare:. To get some basic idea about Synkronizer Excel Compare's capabilities and performance, let's carry out a couple of field tests. Supposing you are organizing some event and, in your Excel table, you gather information about the participants such as a participant name, arrival date, number of seats, etc.

Also, you have a couple of managers in direct contact with participants and the database, and as a result, you have 2 versions of the same Excel file. Okay, let's see how efficiently Synkronizer can compare our two sheets and identify differences between them. The Synkronizer pane will show up in the left part of your Excel window, where you do the following:.

If the selected workbooks have any sheets with the same names, all those sheets will be matched and automatically selected for comparison like Participants sheets in the below screenshot.

Also, you can select worksheets manually or instruct the add-in to match sheets by other criteria, for example by worksheet type - all, protected, or hidden. Once you've selected the sheets, the Synkronizer add-in will open them side by side, arranged vertically or horizontally, like in Excel's View Side by Side mode. Select one of the following comparison options: Compare as normal worksheets - the default option that works in most cases.

Compare with link options - if the selected sheets do not contain any new or deleted rows and columns, you can compare them "1 on 1". Compare as database - recommended for comparing sheets that have a database structure.

Compare selected ranges - if you do not want to compare the entire sheets, define the ranges to be compared. Choose the content types to be compared optional. On the Select tab, in the Compare group, you can choose the content type s relevant to your current task:. Usually it takes Synkronizer only a few seconds to compare 2 sheets and present two summary reports on the Results tab:. The following screenshot shows the summary report in the upper part of the Results pane , and cell difference report in the lower part of the pane that were created for our sample sheets:.

Clicking on a difference in the detailed report will select the corresponding cells on both sheets the below screenshot shows just one sheet because there's enough room to show both :. In addition, you can create a difference repot in a separate workbook, either standard or hyperlinked, and jump to a specific difference with a mouse click:. If the two Excel files you are comparing contain multiple sheets, all matching worksheet pairs will be presented in the summary report for your perusal:.

By default, the Synkronizer Excel Compare add-in highlights all found differences, like in the following screenshot:. To highlight only the relevant differences , click the Outline button on the Results tab, and select the required options:. Top Rated Most Recent. Accept Solution Reject Solution. Well, i have an excel with over 4k lines Also, i have them written in my excel file as a tree view When you Google "excel compare sheets", you get only 15 million answers, and you found nothing useful?

Copy Code. Posted Nov pm Patrice T. I'm doing something almost exactly like that WPF application. I'm taking two spreadsheets, and merging them based on a user-specified mapping criteria with one or more matching column pairs. I'm using a Wizard-style form to guide the user through the process. Philippe Mori Nov am.

You don't really explain what to do or where to start You can read your Excel files via the Microsoft. Posted Nov pm Richard MacCutchan. I have to compare two Excel files and they are over 4k lines I don't know how much this would help me. Richard MacCutchan Nov am. Whatever form your data is held in you will have to read each item from both files to do the compare. Add your solution here. OK Paste as. Treat my content as plain text, not as HTML.

Existing Members Sign in to your account. This email is in use.