Select All Cells in Active Worksheet using ComOject Excel.Application in Powershell

Question

Very new to Powershell.

Situation:

My script below is meant to do the following:

1. Open New Workbook
2. Put "This is A1" in the cell 'A1'
3. Select all cells in the Active Worksheet
4. Set the selection to TEXT data format

Here is my Script for Powershell:

$excel = New-Object -Com Excel.Application
$excel.visible = $true
$book = $excel.Workbooks.Add()
$book.ActiveSheet.Range("A1").Value = "This is A1"
$worksheet = $book.ActiveSheet.UsedRange.Cells
$colcount = $worksheet.columns.count
$copyrange = $worksheet.range("1:$colcount").numberformat = "@"

Issue:

I don't know how to write Select All in the command because when I run my script, it only makes 'A1 & B1' TEXT cells and everything else is left as GENERAL format. I would like to make the Range Dynamic.

This is my script in VBA:

Sub TEXT_FORMAT()

    ActiveCell.FormulaR1C1 = "This is A1"
    Cells.Select
    Selection.NumberFormat = "@"

End Sub

The Script I tried to follow:

How to select all in excel using Powershell

Can anyone please help me out with how I can make this more dynamic with my SELECT ALL statement?

Much appreciated.

Answer 1

Try this out, it worked for me. You can apply the number formatting by either selecting ALL rows (or) ALL columns

$excel = New-Object -Com Excel.Application
$excel.visible = $true
$book = $excel.Workbooks.Add()
$worksheet = $book.ActiveSheet
$worksheet.Range("A1").Value2 = "This is A1" # Use the property 'Value2'
$worksheet.Rows.NumberFormat = "@"
# $worksheet.Columns.NumberFormat = "@" # this also sets number format of all cells to 'Text'