共用方式為

Compare Cells in Excel Sheets and mark differences with colors...

  • 發行項

Just got a task that there is a database table which got row add / delete / updates and wants to compare and mark out differences in Excel sheets.

Input:

  • Original Database Table named "orgTable"
  • Updated Database Table named "rstTable"
  • orgTable and rstTable are the same tables that have same schema

Output:

  • Excel file with sheets of
    1. Added rows
    2. Deleted rows
    3. Primary-key mapped orgTable rows with cell-color marked differences from rstTable
    4. Primary-key mapped rstTable rows with cell-color marked differences from orgTable

Steps:

  1. Prepare Table-View SQL Scripts
    1. Added rows view
      • select * from rstTable where rstTable.primaryKey not in (select orgTable.primaryKey from orgTable) order by rstTable.primaryKey
    2. Deleted rows view
      • select * from orgTable where orgTable.primaryKey not in (select rstTable.primaryKey from rstTable) order by orgTable.primaryKey
    3. Primary-key mapped orgTable rows view
      • select * from orgTable where orgTable.primaryKey in (select rstTable.primaryKey from rstTable) order by orgTable.primaryKey
    4. Primary-key mapped rstTable rows view
      • select * from rstTable where rstTable.primaryKey in (select orgTable.primaryKey from orgTable) order by rstTable.primaryKey
  2. Export Views to Excel Sheets
    1. Using SQL Server Data Export function to export those views to a single Excel file with multiple sheets.
  3. Using Excel VBA Macro to mark colors
    1. Open exported Excel file
    2. Open VBA Macro Editor
    3. Identify orgTable and rstTable mapped output sheets name (here defining "orgTableSheet" as Sheet3 object and "rstTableSheet" as Sheet4 object)
    4. Create the following CompareSheet function by adding a new Module:

      • Sub comparesheet()
        '''''using rstTableSheet (Sheet4) as base for compare
        For Each MyCell In Sheet4.UsedRange
        '''''first reset the cell with white background
        MyCell.Interior.ColorIndex = 0
        Sheet3.Range(MyCell.Address).Interior.ColorIndex = 0
        '''''if cell is not empty... If Trim(MyCell.Value) <> "" Then
        '''''if cells in the same position of those 2 sheets got different values...
        If Trim(MyCell.Value) <> Trim(Sheet3.Range(MyCell.Address).Value) Then
        '''''paint both cells background to red MyCell.Interior.ColorIndex = 3
        Sheet3.Range(MyCell.Address).Interior.ColorIndex = 3
        End If
        End If
        Next
        End Sub

         

    5. Run the macro to mark colors in both sheets.

FYI.

Let me know if you got more efficient ways to do this task...

Technorati tags: microsoft, office, excel, macro, VBA, tip

Comments