excelpowershellpowershell-4.0

Powershell: Select Range with column Number


I have a Script which took the Column number($lc) and Row -Number ($sr / $lr).

Now I#d like to selcet a Range from ($lc - 7)($sr) to ($lc)($lr).

Problem I don't how to Convert Column Number into letter? OR How can I adress a Range by using the Number?

$file = "C:\Test\Excel_Matrix.xlsx"
#Program definieren
$excel = New-Object -ComObject Excel.Application
#im hintergrund ausführen
$excel.visible = $false


    $table = $null
    $workbook = $excel.workbooks.open($file)
    $table = $workbook.Worksheets.Item("Matrix")

    ###########
    #Basisdaten
    ###########
    
   

        $table = $null
        $workbook = $excel.workbooks.open($file)
        $table = $workbook.Worksheets.Item("Matrix")
        
        IF($table -ne $null)
        {
            #Last Row lr
            $LetzteZeile = $table.Range("A10:A1000").Find("S999").row
            # start row sr
            $StartZeile = $table.Range("A10:A1000").Find("S002").row
            # last column lc
            $LetzteSpalte = $table.UsedRange.Find($LastText).Column
            
            $StartZeile
            $LetzteZeile
            

            **### DON'T WORK ###**   
            
            $table.Range(.Cells($StartZeile,1),.Cells($LetzteZeile,$LetzteSpalte)
   
        }

        $workbook.Close()
        $excel.Quit()
        $excel = $null

Thanks for your time.


Solution

  • Thats wath realy work:

    $exBefRange = ($table.Cells.Item($StartZeile.Row, 6).address($false,$false) + ":" + $table.Cells.Item($LetzteSpalte.Row, ($LetzteSpalte-9)).address($false,$false))
    

    But maybe, many ways go to Rome.