CODEWITHSUNDEEP
excelpowershell-2.0
user5013
user5013

Reputation: 1011

Can't select Excel sheet from PowerShell

I am just learning PowerShell and am unfortunately stuck with using v2 instead of the latest version. What I am trying to do is open Excel 2010 and select a specific worksheet. The problem is that I cannot select the worksheet I am interested in. Here is my code:

$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=$True
$workbook=$objExcel.Workbooks.Open($excelfile)
$worksheet = $workbook.worksheets | where {$_.name -eq 'AnalysisMain'}
Write-Output "worksheet: $worksheet"

I have verified that the sheet name is spelled correctly and the code looks good to me however the result is that no worksheet is ever returned - the result is null.

Where have I gone off the rails?

Upvotes: 4

Views: 14412

Answers (2)

Ananthan Anand
Ananthan Anand

Reputation: 11

Copying excel data based on the range on the sheet and writing into the email body

$excel = New-Object -ComObject excel.application
$excel.Visible = $true
$workbook = $excel.workbooks.open('D:\Projects\working\data.xlsm')
$sheet = $workbook.Worksheets.Item('Graph')
$sheet.activate()
$rangeSource=$sheet.range("A50","M73")
$rangeSource.Copy() | out-null
$Results = Get-Clipboard -TextFormatType Html | select -skip 7 | Out-String

Following data will be skipped

Version:1.0 StartHTML:0000000174 EndHTML:0000035258 StartFragment:0000001674 EndFragment:0000035206 SourceURL:file:///D:\Projects\working\data.xlsx

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166790

In my testing this:

$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=$True
$workbook=$objExcel.Workbooks.Open('C:\\_Stuff\\test.xlsx')
$worksheet = $workbook.worksheets.item('Sheet1')
Write-Output $worksheet.name

Produced the expected output "Sheet1"

This also worked:

$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=$True
$workbook=$objExcel.Workbooks.Open('C:\\_Stuff\\test.xlsx')
$worksheet = $workbook.worksheets | where {$_.name -eq 'Sheet1'}
Write-Output $worksheet.name

Are you sure your $workbook object is being set ?

Upvotes: 6

Related Questions