Powershell: Select Range with column Number

Question

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

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

The problem is, I don't know how to convert a Column Number into a letter. OR How can I address 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.

Have you considered using the great module from Doug Finke ImportExcel. That could make your life way easier. 😉👍🏼 — Olaf
– Olaf, Commented Sep 29, 2023 at 9:54
Wath ist the corrcet Syntax for R1C1? I tired $table.Range($table.Cells($Step.Row, 6),$table.Cells($Step.Row, ($LetzteSpalte-9))) AND $table.Range($table.Cells.Item($Step.Row, 6).FormulaR1C1,$table.Cells.Item($Step.Row, ($LetzteSpalte-9)).FormulaR1C1) But it Don't works... — TheRob87
– TheRob87, Commented Sep 30, 2023 at 23:29

mklement0 · Accepted Answer · 2023-09-29 13:47:02Z

1

The (implied parameterized .Item() property of) the .Cells property does accept numeric values to identify a cell, namely 1-based row and column numbers.

It looks like the only problem with your code was that you didn't specify a range/worksheet object on which to call .Cells():

Therefore:

$table.Range(
  $table.Cells($StartZeile, 1),
  $table.Cells($LetzteZeile, $LetzteSpalte)
)

You probably thought that $table could be omitted before the .Cells() calls because VBA allows you to do so via With statements, such as in the following example, where the object to operate on in property access / method calls inside the block is implied by the With operand:

' !! Works in VBA only.
With Worksheets(1) 
    .Range(.Cells(1, 1), _ 
           .Cells(10, 10))
End With

However, PowerShell has no equivalent construct.

edited Sep 29, 2023 at 13:47

answered Sep 29, 2023 at 13:40

mklement0

453k68 gold badges729 silver badges989 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheRob87 Over a year ago

Unliky IT don't work :S I Tried: $table.Range($table.Cells($Step.Row, 6),$table.Cells($Step.Row, ($LetzteSpalte-9))) AND $table.Range($table.Cells.Item($Step.Row, 6).FormulaR1C1,$table.Cells.Item($Step.Row, ($LetzteSpalte-9)).FormulaR1C1)

mklement0 Over a year ago

@TheRob87, I don't know what your comment is trying to say, especially since it uses different variables ($Step) and arguments (-9, -6) than in the question, but, as far I can tell, the solution in this answer works, and addresses the question you asked.

TheRob87 · Accepted Answer · 2023-11-11 22:12:30Z

-1

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.

edited Nov 11, 2023 at 22:12

answered Sep 30, 2023 at 23:49

TheRob87

1468 bronze badges

1 Comment

mklement0 Over a year ago

This uses different variables ($Step) and arguments (6, -9) than what is in your question and seemingly attempts to return an address of the range in question, which is not what your question asks for.

Collectives™ on Stack Overflow

Powershell: Select Range with column Number

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related