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

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
>

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" <no@xxxxxx> wrote in message
news:95d8089330ab78ca9b6ccefa9d40@xxxxxx

>
> 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 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

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" <no@xxxxxx> wrote in message
M> news:95d8089330ab78ca9b6ccefa9d40@xxxxxx
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
>>>

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

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

MG

"Shay Levi" <no@xxxxxx> wrote in message
news:89228ed230c488ca9c992bb4dfe2@xxxxxx

> 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" <no@xxxxxx> wrote in message
> M> news:95d8089330ab78ca9b6ccefa9d40@xxxxxx
> 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
>>>>

>
>