1

I have the below script to compare two reports and generate output to excel, this is generating the output but just takes report 1 minus report 2 and generating the data with differences in Report2 and it doesn't generate with header. `

# Load the Excel COM object
    $excel = New-Object -ComObject Excel.Application

# Disable alerts and screen updating for improved performance
    $excel.DisplayAlerts = $false
    $excel.ScreenUpdating = $false

# Open the two Excel sheets to compare
    $originalWorkbook = $excel.Workbooks.Open("C:\Users\Conversion\Response_V2.csv")
    $modifiedWorkbook = $excel.Workbooks.Open("C:\Users\Conversion\Response_V22.csv")

# Get the worksheets to compare
    $originalWorksheet = $originalWorkbook.Worksheets.Item(1)
    $modifiedWorksheet = $modifiedWorkbook.Worksheets.Item(1)

# Get the range of cells to compare (adjust the range as needed)
"    $originalRange = $originalWorksheet.Range("A1:AE10")
"    $modifiedRange = $modifiedWorksheet.Range("A1:AE10")

# Create a new Excel workbook to store the comparison results
"    $resultsWorkbook = $excel.Workbooks.Add()
"   $resultsWorksheet = $resultsWorkbook.Worksheets.Item(1)

# Compare the cells and copy the results to the new worksheet
"    for ($row = 1; $row -le $originalRange.Rows.Count; $row++)
{
    for ($col = 1; $col -le $originalRange.Columns.Count; $col++)
    {
        $originalValue = $originalRange.Item($row, $col).Value2
        $modifiedValue = $modifiedRange.Item($row, $col).Value2
        
        if ($originalValue -ne $modifiedValue)
        {
            $resultsWorksheet.Cells.Item($row, $col).Value2 = "$modifiedValue"
        }
    }
}
# Save the comparison results to a new Excel file
"    $resultsWorkbook.SaveAs("C:\Users\Conversion\Response_V99.xlsx")

# Close all Excel workbooks and quit Excel
"    $originalWorkbook.Close($false)
"    $modifiedWorkbook.Close($false)
"    $resultsWorkbook.Close($true)
"`your text`    $excel.Quit()

Can you help to modify the code to generate report with the variance from both the files and with header?

Rakesh Vemulapalli
  • 13
  • 3
  • 2
    It looks like you're working with `CSV` files - not Excel sheets?!?! If that'S the case you should use the built in ability to read and write CSV files with `Import-Csv` and `Export-Csv`. If you have to work wit proper Excel sheets you may take a look at the great module from Doug Finke [ImportExcel](https://www.powershellgallery.com/packages/ImportExcel). That will make your life much easier. When you actually imported the data from the files - no matter if it's `CSV` or Excel - you may user `Compare-Object` to actually compare your reports. `¯\_(ツ)_/¯` – Olaf May 13 '23 at 16:27
  • 3
    You have two `.csv` files as input, are you sure you want a `.xlsx` as output? As it is much easier (as it doesn't require excel) and probably faster to also produce a `.csv` as output. see: https://stackoverflow.com/q/1848821/1701026 – iRon May 13 '23 at 16:27

0 Answers0