Paste\Return into Cell A1 of open Excel sheet

Question

I wish to take a string generated in PowerShell and paste\return that string into Cell A:1 of an OPEN spreadsheet. I have researched this but I dont understand the excel specific syntax. Do I need to identify and declare the PID for the specific Excel workbook thats running and if so how?

I know how to create a new workbook and add the text to cell A:1 but not one which is already open. It must also "press return" to execute the spreadsheet functions once pasted.

I dont know where to start other than: ((new-object -com "excel.application").Workbooks.Add()).application.Visible=$True

I have scoured the web but have not found any examples that make sense. Please help

Suhas Parameshwara · Accepted Answer · 2019-07-22 08:34:14Z

0

You can achieve it by using the below script

#Specify the path of the excel file
$FilePath = "path\test.xlsx"

#Specify the Sheet name
$SheetName = "Sheet1"

# Create an Object Excel.Application using Com interface
$objExcel = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')

# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false

# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)

# Load the WorkSheet 'BuildSpecs'
$WorkSheet = $WorkBook.sheets.item($SheetName)
$WorkSheet.Cells.Item(1,1) = "Link" #Updates the first cell (A1)
$WorkBook.Save()
$WorkBook.Close()

Make sure the file has write permissions from PowerShell scripts.

If you want to edit the sheet which is already open, change the path of the file and mention the sheet name properly and it would work as expected.

edited Jul 22, 2019 at 8:34

answered Jul 22, 2019 at 7:38

Suhas Parameshwara

1,4749 silver badges18 bronze badges

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

6 Comments

IanB Over a year ago

Thanks Ng-Suhas, will test this asap

Suhas Parameshwara Over a year ago

@IanB Test this script and if you find any issues, feel free to ping me back. If it works, please accept the answer. Thanks

IanB Over a year ago

Will it work if the Excel Sheet is already open? I also assume “link” on third last line is just the text I want to paste into cell A:1 ?

Suhas Parameshwara Over a year ago

Yes, it should work even if the excel file is open! And yes the link on the third last line is the text that you want to replace into the cell A:1.

IanB Over a year ago

And will it invoke a return (press enter) once the cell A:1 has the texted pasted?

|

Collectives™ on Stack Overflow

Paste\Return into Cell A1 of open Excel sheet

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related