Comparing values of two cells from different xls files

PowerShell Help
1

Dear experts!

I have 2 seperate .xls files containing information about disk space and corresponding costs. Now I need to write a script which automatically compares the columns containing the disk space of the 2 files and check wether the difference between them is greater than 10% (for example). If so, select these differing rows and create an output (either csv, html or xls). So the user can easily tell which rows need to be updated in the other file (which will later be done manually).

Here´s what I got so far:

#=============================================================================
# Open first Excel File and read from relevant sheet
#*=============================================================================

$XLSDoc = “I:\ISS\RanM\2013_Q2_Kostenaufteilung_ISS-Server.xlsm”
$SheetName = “nach TSM-Storage”
$Excel = New-Object -ComObject “Excel.Application”

$Workbook = $Excel.Workbooks.open($XLSDoc)
$Sheet = $Workbook.Worksheets.Item($SheetName)
$Sheet.Activate()

#=============================================================================
# Open second Excel File and read from relevant sheets
#*=============================================================================

$XLSDoc1 = “I:\ISS\RanM\CeBrA-Cloud_Server_2.3.xlsx”
$SheetName1 = “Abrechnung INTERN 2.3”
$Excel1 = New-Object -ComObject “Excel.Application”

$Workbook1 = $Excel1.Workbooks.open($XLSDoc1)
$Sheet1 = $Workbook1.Worksheets.Item($SheetName1)
$Sheet1.Activate()

This seems to be working so far. I can extract the values from each desired cell. For a start I tried to write a function that selects certain rows depending on the parameter $thresh:

Function SelectColumns ($thresh) {
$row = 5
$col = 3

While($Sheet.Cells.Item($row, $col).Value() -ne $null){

if($Sheet.Cells.Item($row, $col).Value() -gt $thresh){
    Write-Host $Sheet.Cells.Item($row, $col-2).Value(), $Sheet.Cells.Item($row, $col).Value()
    }
$row +=1   
}

}

Write-Host is just for testing to see if it does what I want. This function needs to be modified to compare the values from the other xls file´s disk space column. I could use some help here.

Next is a function creating the html output for the resulting rows:

Function makeHTML{
$xlHtml = 44
$missing = [type]::Missing

$Workbook.SaveAs('H:\Documents\PowerShell\kostenvergleich.html',$xlHtml,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing,$missing) #| Where-Object -EQ $selection

}

This also works fine except that it always exports the whole sheet instead of the selected rows. I tried piping the SelectColumns function to this makeHTML function to get only the selected columns and rows for my output…but didn´t work. I know about the $input variable but not sure where to put it or if there is a completely different way to do this.

any help would be greatly appreciated!!
thanks in advance

tchintchie

PS: please keep in mind that I am a complete Powershell noob (bear with me please!)

2

When you are automating Office applications, a good starting point is to see how the application does it. Office uses VBA ( Visual Basic for Applications ) to perform actions in the suite. So, start with showing the Developer Tab. Once you have the tab open, you’ll see a Record Macro button. Do everything you want to do manually in Excel. Go back to the Developer Tab, Stop Macro and then open Macros and click Edit.

Sub Macro1()
’
’ Macro1 Macro
’

’
ChDir “C:\Users\Rob\Desktop”
Workbooks.Open Filename:=“C:\Users\Rob\Desktop\test1.xlsx”
Workbooks.Open Filename:=“C:\Users\Rob\Desktop\test2.xlsx”
Windows(“test1.xlsx”).Activate
Range(“B2:C4”).Select
Selection.Copy
Windows(“test2.xlsx”).Activate
ActiveSheet.Paste
End Sub

The only things you have to wrap your head around is that VBA is being executed in Excel, so it’s context doesn’t need to be as specific and constants (none in this example, but like xlLeft, xlColorBlah) need to be statically defined ($xlLeft = -4131) in your code because they do not exist in the script’s context.

So, here is converted Powershell code:

$xl = New-Object -ComObject “Excel.Application”
$xl.Visible = $true
$xl.DisplayAlerts = $false #for debugging, no prompts to save, etc.

#Workbooks.Open Filename:=“C:\Users\Rob\Desktop\test1.xlsx”
$wb1 = $xl.Workbooks.Open(“C:\Users\Rob\Desktop\test1.xlsx”)
#Workbooks.Open Filename:=“C:\Users\Rob\Desktop\test2.xlsx”
$wb2 = $xl.Workbooks.Open(“C:\Users\Rob\Desktop\test2.xlsx”)
#Windows(“test1.xlsx”).Activate
$wb1.Activate()
#Range(“B2:C4”).Select
$wb1.ActiveSheet.Range(“B2:C4”).Select()
#Selection.Copy
$wb1.ActiveSheet.Selection.Copy()
#Windows(“test2.xlsx”).Activate
$wb2.Activate()
#ActiveSheet.Paste
$wb2.ActiveSheet.Paste($wb2.ActiveSheet.Range(“A1”))

3

Hi Rob!
So sorry I couldn´t get back to you sooner but I had problems connecting to the forums at work. Anyways, thank you very much for your input! It really helped me out a lot!
regards
tchintchie

4

Glad I was able to assist. I usually take silence as your figured it out. Trying to do research at work is like pulling teeth with every other website blocked, I feel your pain.