# Solved: Powershell - Retrieving Excel Sheet Name



## LyrradMelon

Hello,

I am working on a Powershell script that will verify data and formatting in excel spreadsheets. The only function I need to have to figure out is how to get the sheet name from the file. I'm not very familiar with how to use the functions yet, but have used them before. The problem is that I can't figure out a command for retrieving a sheet name in an excel file. As long as the sheet name is in any part of the output, I can work with it from there. Any help would be appreciated.

Thanks,

Darryl


----------



## Squashman

Probably something like this from what I read.

$xldoc = New-Object -comobject Excel.Application
$workbook = $xldoc.Workbooks.Open("Path to some file")
$worksheet = $workbook.worksheets.item(1)


----------



## Squashman

Or did you actually want the name of the w3orksheet you are accessing.

$wksname = $worksheet.name


----------



## Squashman

created a new workbook called squashtest.xls. I created a worksheet in the workbook and named it SquashSheet.

Then in powershell you can get the worksheet name like this.


Code:


PS C:\Users\Squash\Documents> $xldoc = new-object -comobject Excel.application
PS C:\Users\Squash\Documents> $workbook = $xldoc.Workbooks.Open("squashtest.xls")
PS C:\Users\Squash\Documents> $worksheet = $workbook.worksheets.item(1)
PS C:\Users\Squash\Documents> $worksheet.name
SquashSheet
PS C:\Users\Squash\Documents>


----------



## Squashman

Just started using PowerShell a few weeks ago and I must say I enjoy how robust it is. Didn't even know that you could do this with excel. It has given me a ton of ideas on how to use it at work.


----------



## LyrradMelon

Thanks a lot, this was killing me yesterday. the line I was looking for was "$wksname = $worksheet.name"
I was over-thinking the whole process, and I'm pretty sure the reason I couldn't find an answer by searching was because how obvious the answer is. The script is now complete!
I just started using Powershell a few weeks ago as well, and its great. Everything can be done on Powershell, it's just a matter of finding the correct commands. Excel is a headache at times, and if you are going to get into Excel more, keep an eye on your processes because it may not be ending the process cleanly 

Have a great day...you've started my Friday in the best way!

Darryl


----------



## Squashman

You can lookup all available properties and methods for the object by piping it to the GET-MEMBER cmd.

Give this a try and you will understand what I mean


Code:


PS C:\Users\Squash\Documents> $xldoc = new-object -comobject Excel.application
PS C:\Users\Squash\Documents> $workbook = $xldoc.Workbooks.Open("squashtest.xls")
PS C:\Users\Squash\Documents> $worksheet = $workbook.worksheets.item(1)

Then at PS prompt do this.


Code:


$workbook | get-member
$worksheet | get-member


----------

