CODEWITHSUNDEEP
excelpowershell
IanB
IanB

Reputation: 271

Paste\Return into Cell A1 of open Excel sheet

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

Upvotes: 0

Views: 556

Answers (1)

Suhas Parameshwara
Suhas Parameshwara

Reputation: 1454

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.

Upvotes: 0

Related Questions