My Tool for comparing two EXCEL workbooks using EXCEL-VBA

How to compare two Excel WorkBooks?

There are many tools and solutions for comparing two workbooks being used. Many MVP for EXCEL suggested a method for this. I want to share the method which I am currently using for comparing two books. For this purpose I created a small and simple workbook.

Here is the screen shot of the workbook. Bigger view of the same screen shot

You can email me to have your own copy of this tool free of cost.

I dont know how to publish this XLS file for sharing. I need help in this regard.

One more quick method for single sheet workbook is

• Open two books in different windows
• Create a new blank workbook
• Type the formula =[Book2]Sheet1!$A$1=[Book1]Sheet1!$A$1
• Change the above formula from absolute address to relative address (remove $)
• =[Book2]Sheet1!A1=[Book1]Sheet1!A1
• Copy this formula to required range area
• You can see TRUE or FALSE based on the values in both sheets
• You can use FORMAT / CONDITIONAL FORMATTING
• set Cell Value Equal to FALSE
• select FORMAT
• select PATTERNS and select a flourescent color.
  
  I hope you will like and use this trick for comparing two sheets.
  
  Cheers friends! Have a look at my other posts also. karthiganesh@yahoo.com

What is a PivotTable?

A PivotTable is a summary table of voluminous data. It used to summarize the data based on the selected data values. Consider
the following example

SalesPerson	Month	Volume
CHAN	OCT-07	450
LEE	NOV-07	430
MANI	NOV-07	230
CHAN	NOV-07	110
MANI	DEC-07	420
LEE	DEC-07	110
CHAN	DEC-07	210

From the above table,

• If we need the total sales volume of each SalesPerson, then we can use DATA / SUBTOTAL command for each change in
  SalesPerson
• If we need the total sales volume of each Month, then we can use DATA / SUBTOTAL command for each change in Month
• If we need SalesPerson and Month wise breakup in a matrix format, we cannot use DATA / SUBTOTAL command

PivotTable can be used for the above summary report. A sample PivotTable will look like this.

	OCT-07	NOV-07	DEC-07	Total
CHAN	450	110	210	770
LEE		430	110	540
MANI	230	420	650
Total	450	770	740	1920

Procedure

1. Prepare the List (You can type or fetch the data from any ODBC compatible database. Refer my earlier post in this blog)
2. Select the List
3. Select Data / PivotTable and PivotChart Report menu command
4. Select the output location (it can be a new sheet or part of the existing sheet)
5. Select the LAYOUT button and
6. Place the SalesPerson in Row area
7. Place the Month in Column area
8. Place the Volume in the Data area
9. Then Finish, now you can see the PivotTable

In the same way you can generate PivotChart

Sample Scenarios

1. A Warehouse wise, product summary
2. Yearwise, Monthwise, Product Sales/Purchase
3. Department wise, Year wise admission for a college

Other Points

• You can have 3 dimensional PivotTables by using Page Field

Once Again I am here to help in EXCEL based solutions, as a service to society. Your comments are driving force behind the success of this blog.

How can I learn EXCEL Programming easily, quickly and effectively?

You can learn EXCEL Programming very easily!
Believe me it is a child play!!
Yet the result will be an Expert Solution!!!

If you know EXCEL operations very well, you can code for the same. For example, you may wish to subtotal the EMP records based on DEPT. (Refer my previous Blog Post for programmatically fetching data from ORACLE to EXCEL Worksheet).

You know using DATA / SUBTOTAL menu. If you want to do the same from a EXCEL VBA macro, then You need not learn EXCEL VBA language. Here is the shortcut.

Procedure
----------

• Start recording macro. (TOOLS / MACRO / RECORD NEW MACRO)
• Now you can start working as usual. In this case you find the subtotal of the EMP records based on DEPT.
• Stop recording macro (TOOLS / MACROS / STOP RECORDING)
• Now Start VBE (Visual Basic Editor) (TOOLS / MACROS / Visual Basic Editor)
• In the Project Explorer, Expand the Modules. You can see 'Module1'. Open the 'Module1' by double clicking it.
• VOILA!!! You can see the code for finding subtotal.
• COPY, PASTE and CUSTOMIZE

If you ask me what is EXCEL-VBA programming!!!

for a NOVICE Programmers

RECORD, COPY, PASTE and CUSTOMIZE

For Advanced Programmers

Will tell in the subsequent posts. Keep visiting my Blog.

How to display the EMP records from ORACLE?

A 100 million question among IT managers???

MS-EXCEL is one of the most useful business tool, but can I display the RECORDS from our company Database... say from ORACLE?
This will enable the executive to process the data as per their working style and make useful decision!!! This facility offers flexibility and fidelity.

Here is a simple solution from kg.

MS-EXCEL can access data from any ODBC compatible RDBMS. Being a popular RDBMS of many corporate choice, I am going to demonstrate how to access ORACLE data using MS-EXCEL.

Step by Step Procedure

• Create a Data Source Name in ODBC. Using
  Control panel
  Administrative Tools
  Data Sources (ODBC)
  Add an User DSN (You can get help from competitive person from your organization)
• Start MS-EXCEL
• Start Visual Basic Editor (Tools / Macro / Visual Basic Editor)
• In the VBE (Visual Basic Editor), view the Project Explorer
  (VIEW / Project Explorer) I use CTRL+R key.
• Double click on the ThisWorkBook. You can see the CODE WINDOW. In this select WorkBook in the Object List and select Open in the Event List.
  
• Type the following code in Workbook_Open procedure
  
  Private Sub Workbook_Open()
  Dim cnEmp As ADODB.Connection
  Dim rsEmp As ADODB.Recordset
  
  If cnEmp Is Nothing Then
  Set cnEmp = New ADODB.Connection
  With cnEmp
  .ConnectionString = "DRIVER={Microsoft ODBC for Oracle};UID=SCOTT;PWD=TIGER;"
  .Open
  End With
  End If
  
  If rsEmp Is Nothing Then
  Set rsEmp = New ADODB.Recordset
  rsEmp.Open "select * from emp", cnEmp, adOpenStatic, adLockBatchOptimistic
  End If
  
  'To display Column names
  For i = 1 To rsEmp.Fields.Count
  Sheets("Sheet1").Cells(3, i).Value = rsEmp.Fields(i - 1)
  End If
  Sheets("sheet1").Range("a4").CopyFromRecordset rsEmp
  End Sub
  
  Here I uploaded the excel workbook also.

I hope that this is a simple yet more useful solution. Once you tasted, I am sure you will not leave this elegant tool. It can be put into effective use by ORACLE DBAs.

Once again, You can email me your queries. I am ready to give my assistance.

What is Excel Programming?

MS-EXCEL can be programmed to meet our custom requirement effectively. An EXCEL prgrammer write code in VBA (Visual Basic for Applications). Hence it is also called as EXCEL-VBA Programming.

So any existing VB Programmer can do EXCEL prgramming.

Here is a simple solution to add two numbers. It is just to show how to write VBA Functions. So dont argue with me what is the use of this solution. I know that the simple and well known cell formula =A1+B1 will do this.

• Start Visual Basic Editor using TOOLS / MACRO / Visual Basic Editor. (I use ALT+F11 key combination!!!)
• Insert a module (Insert / Module)
• Type the following in the module

Public Function add(ByVal a As Integer, ByVal b As Integer) As Long

add = a + b

End Function

• Now in the worksheet type the following table

TRAVEL FOOD TOTAL

JAN-08 230 430 =add(a1,b1)

FEB-08 210 545 copy the above formula

• In the total column enter the following formula =add(a2,b2) and drag below.

I hope that this will give you a fundamental idea for novice user. For advanced use, they can write many sophisticated function in this way.

Feel free to contact me for any assistance (ofcourse I will assist you free of cost only). karthiganesh@yahoo.com

What can you do using MS-EXCEL?

Literally You can use MS-EXCEL for anything...

* Worksheet
* Word processing
* Presentation
* Front End Tool for popular RDBMS
....

and You too can add one more item to this list...

Here is the list for Advanced / Specialised applications

* Business Intelligence Tool
* What-If Analysis Tool
* Real Time Calculations for Production Specialists
* Statistical Analysis Tool
* Charting Tool

and more.