Comparing values of two cells from different xls files

Tagged: , , ,

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 3 years, 8 months ago.

  • Author
    Posts
  • #8820
    Profile photo of tchintchie
    tchintchie
    Participant

    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!)

  • #8842
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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"))

  • #9760
    Profile photo of tchintchie
    tchintchie
    Participant

    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

  • #9765
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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.

You must be logged in to reply to this topic.