How to determin a worksheet exist in Excel?

[Michael Gao]

Hi,
I wrote a PS script like this:

$a = New-Object -comobject Excel.Application
$a.visible = $true
$b = $a.Workbooks.Open("D:\out\M2.xlsx")
$i = 1
do {
$c = $b.worksheets.Item($i)
$c.name = $c.cells.item(2,2).value2
$i++
}while ($i -le $b.worksheets.count)

Some value may be the same, so I got error:
Exception setting "Name": "Cannot rename a sheet to the same name as another
sheet, a referenced object library or a workbook referenced by Visual
Basic."
At line 7, position 4
$c.name = $c.cells.item(2,2).value2

Does anybody met the same problem?

Thanks!

MG

 

[Shay Levi]

Try:

$xl = new-object -com excel.application
$workbook = $xl.workbooks.open("D:\out\M2.xlsx")

# filter workSheets by name
$sheet = $workbook.worksheets | where {$_.name -eq "sheetName"}

if($sheet) { "workSheet exists" }



---
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

> Hi,
> I wrote a PS script like this:
> $a = New-Object -comobject Excel.Application
> $a.visible = $true
> $b = $a.Workbooks.Open("D:\out\M2.xlsx")
> $i = 1
> do {
> $c = $b.worksheets.Item($i)
> $c.name = $c.cells.item(2,2).value2
> $i++
> }while ($i -le $b.worksheets.count)
> Some value may be the same, so I got error:
> Exception setting "Name": "Cannot rename a sheet to the same name as
> another
> sheet, a referenced object library or a workbook referenced by Visual
> Basic."
> At line 7, position 4
> $c.name = $c.cells.item(2,2).value2
> Does anybody met the same problem?
>
> Thanks!
>
> MG
>

 

[MG]

Sorry for my question is not quite clearly.
I have an Excel file that contain hundreds of worksheets. I need change each
worksheet name to their cells value (2,2). But some worksheets have same
value in cell(2,2), forexample, both of sheet1,4,5,62 have same value "name"
in cell(2,2). So only sheet1 change name to "name", other worksheets will
get error and keep their own name.
My question is, can I change these worksheets name to "name1"..."name34"
base on my script? Or how can I determin whether a worksheets named as
current cell(2,2) already exist before I change it?

Thanks.
MG

"Shay Levi" <[email protected]> wrote in message
news:[email protected]

>
> Try:
>
> $xl = new-object -com excel.application
> $workbook = $xl.workbooks.open("D:\out\M2.xlsx")
>
> # filter workSheets by name
> $sheet = $workbook.worksheets | where {$_.name -eq "sheetName"}
>
> if($sheet) { "workSheet exists" }
>
>
>
> ---
> Shay Levi
> $cript Fanatic
> http://scriptolog.blogspot.com
>

>> Hi,
>> I wrote a PS script like this:
>> $a = New-Object -comobject Excel.Application
>> $a.visible = $true
>> $b = $a.Workbooks.Open("D:\out\M2.xlsx")
>> $i = 1
>> do {
>> $c = $b.worksheets.Item($i)
>> $c.name = $c.cells.item(2,2).value2
>> $i++
>> }while ($i -le $b.worksheets.count)
>> Some value may be the same, so I got error:
>> Exception setting "Name": "Cannot rename a sheet to the same name as
>> another
>> sheet, a referenced object library or a workbook referenced by Visual
>> Basic."
>> At line 7, position 4
>> $c.name = $c.cells.item(2,2).value2
>> Does anybody met the same problem?
>>
>> Thanks!
>>
>> MG
>>

>
>

 

[Marco Shaw [MVP]]

MG wrote:

> Sorry for my question is not quite clearly.
> I have an Excel file that contain hundreds of worksheets. I need change
> each worksheet name to their cells value (2,2). But some worksheets have
> same value in cell(2,2), forexample, both of sheet1,4,5,62 have same
> value "name" in cell(2,2). So only sheet1 change name to "name", other
> worksheets will get error and keep their own name.
> My question is, can I change these worksheets name to "name1"..."name34"
> base on my script? Or how can I determin whether a worksheets named as
> current cell(2,2) already exist before I change it?

It's doable, but it is asking for too many brain cycles for a weekend... ;-)

Check back Monday or so, unless someone else beats me to a solution.

Marco

--
Microsoft MVP - Windows PowerShell
http://www.microsoft.com/mvp

PowerGadgets MVP
http://www.powergadgets.com/mvp

Blog:
http://marcoshaw.blogspot.com

 

[Shay Levi]

Hi MG,

Try this:


$xl = new-object -com excel.application
#$xl.visible=$true
$workbooks = $xl.workbooks.open("D:\out\M2.xlsx")
$sheets = $workbooks.worksheets

foreach($s in $sheets){
$name = $s.range("B2").value()
$counter=$null
while($sheets | where {$_.name -eq "$name$counter"} ){ $counter++ }
$s.name = "$name$counter"
$counter=$null
}

$workbook.save()
$xl.quit()



---
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

M> Sorry for my question is not quite clearly.
M> I have an Excel file that contain hundreds of worksheets. I need
M> change each
M> worksheet name to their cells value (2,2). But some worksheets have
M> same
M> value in cell(2,2), forexample, both of sheet1,4,5,62 have same value
M> "name"
M> in cell(2,2). So only sheet1 change name to "name", other worksheets
M> will
M> get error and keep their own name.
M> My question is, can I change these worksheets name to
M> "name1"..."name34"
M> base on my script? Or how can I determin whether a worksheets named
M> as
M> current cell(2,2) already exist before I change it?
M> Thanks.
M> MG
M> "Shay Levi" <[email protected]> wrote in message
M> news:[email protected]
M>

>> Try:
>>
>> $xl = new-object -com excel.application
>> $workbook = $xl.workbooks.open("D:\out\M2.xlsx")
>> # filter workSheets by name
>> $sheet = $workbook.worksheets | where {$_.name -eq "sheetName"}
>> if($sheet) { "workSheet exists" }
>>
>> ---
>> Shay Levi
>> $cript Fanatic
>> http://scriptolog.blogspot.com

>>> Hi,
>>> I wrote a PS script like this:
>>> $a = New-Object -comobject Excel.Application
>>> $a.visible = $true
>>> $b = $a.Workbooks.Open("D:\out\M2.xlsx")
>>> $i = 1
>>> do {
>>> $c = $b.worksheets.Item($i)
>>> $c.name = $c.cells.item(2,2).value2
>>> $i++
>>> }while ($i -le $b.worksheets.count)
>>> Some value may be the same, so I got error:
>>> Exception setting "Name": "Cannot rename a sheet to the same name as
>>> another
>>> sheet, a referenced object library or a workbook referenced by
>>> Visual
>>> Basic."
>>> At line 7, position 4
>>> $c.name = $c.cells.item(2,2).value2
>>> Does anybody met the same problem?
>>> Thanks!
>>>
>>> MG
>>>

 

[Michael Gao]

while($sheets | where {$_.name -eq "$name$counter"} ){ $counter++ }

That's what I needed! Thank you very much.

MG

"Shay Levi" <[email protected]xxxx> wrote in message
news:[email protected]

> Hi MG,
>
> Try this:
>
>
> $xl = new-object -com excel.application
> #$xl.visible=$true
> $workbooks = $xl.workbooks.open("D:\out\M2.xlsx")
> $sheets = $workbooks.worksheets
>
> foreach($s in $sheets){
> $name = $s.range("B2").value()
> $counter=$null
> while($sheets | where {$_.name -eq "$name$counter"} ){ $counter++ }
> $s.name = "$name$counter"
> $counter=$null
> }
>
> $workbook.save()
> $xl.quit()
>
>
>
> ---
> Shay Levi
> $cript Fanatic
> http://scriptolog.blogspot.com
>
> M> Sorry for my question is not quite clearly.
> M> I have an Excel file that contain hundreds of worksheets. I need
> M> change each
> M> worksheet name to their cells value (2,2). But some worksheets have
> M> same
> M> value in cell(2,2), forexample, both of sheet1,4,5,62 have same value
> M> "name"
> M> in cell(2,2). So only sheet1 change name to "name", other worksheets
> M> will
> M> get error and keep their own name.
> M> My question is, can I change these worksheets name to
> M> "name1"..."name34"
> M> base on my script? Or how can I determin whether a worksheets named
> M> as
> M> current cell(2,2) already exist before I change it?
> M> Thanks.
> M> MG
> M> "Shay Levi" <[email protected]> wrote in message
> M> news:[email protected]
> M>

>>> Try:
>>>
>>> $xl = new-object -com excel.application
>>> $workbook = $xl.workbooks.open("D:\out\M2.xlsx")
>>> # filter workSheets by name
>>> $sheet = $workbook.worksheets | where {$_.name -eq "sheetName"}
>>> if($sheet) { "workSheet exists" }
>>>
>>> ---
>>> Shay Levi
>>> $cript Fanatic
>>> http://scriptolog.blogspot.com
>>>> Hi,
>>>> I wrote a PS script like this:
>>>> $a = New-Object -comobject Excel.Application
>>>> $a.visible = $true
>>>> $b = $a.Workbooks.Open("D:\out\M2.xlsx")
>>>> $i = 1
>>>> do {
>>>> $c = $b.worksheets.Item($i)
>>>> $c.name = $c.cells.item(2,2).value2
>>>> $i++
>>>> }while ($i -le $b.worksheets.count)
>>>> Some value may be the same, so I got error:
>>>> Exception setting "Name": "Cannot rename a sheet to the same name as
>>>> another
>>>> sheet, a referenced object library or a workbook referenced by
>>>> Visual
>>>> Basic."
>>>> At line 7, position 4
>>>> $c.name = $c.cells.item(2,2).value2
>>>> Does anybody met the same problem?
>>>> Thanks!
>>>>
>>>> MG
>>>>

>
>

 

[Yasar Celik]

$Exl = New-Object -ComObject "Excel.Application"
$Exl.Visible = $false
$Exl.DisplayAlerts = $false

$WB_efat = $Exl.Workbooks.Open("sample.xlsx",0,$false)

$file_exist = "No exist"

foreach ( $deger in $WB_efat.worksheets() )
{
if ($deger.name -eq "summarylist" ) { $file_exist = "Yes exist" }
}

$file_exist