Vista - Capture a string of characters and use as filename.

I have hundreds of Excel files created using Powershell with generic filenames.

The first Cell (A,1) contains infomation I would like to use as the filename
for the Excel file.

The first 4 characters (in the Cell A,1) are the same in each file. I want
to capture the 5th through x character, with x being the character preceding
the ',' comma and save the Excel file as this string.

How can I do this?

$xl = new-object -com excel.application

foreach($xls in (dir d:\scripts\temp *.xls)){
#$xl.Visible=$true
$workbook = $xl.workbooks.open($xls.fullname)
$sheet = $workbook.worksheets | where {$_.name -eq "sheet1"}
$name = $sheet.range("A1").value()
$newName = $name.substring(4,($name.indexOf(",")-4))
$workbook.close()
move-item $xls.fullName ($xls.directoryName + "\$newName.xls")
}

$xl.quit()



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

Shay,

Thanks so much! I had to modify just a little since my worksheets were
labeled as the filename as well (exluding the extension). Your help was
appreciated greatly!


"Shay Levi" wrote:

That solution from Shay is going right into my "Scriptbits" container
in ASE.

One question though..shouldn't the $newName = $name.substring(4,
($name.indexOf(",")-4))
be $newName = $name.substring(4,($name.indexOf(",")-1)) to crank the
index back 1 place before the ","?

Just wanted clarification because I'll definitely use this one.

Thanks Shay!

Stuart

Hi Stuart,



I tested it against a string that looks like:

PS > $name = "sameSELECTME,bla bla"


Using -1 returns:

PS > $name.substring(4,($name.indexOf(",")-1))
SELECTME,bl


While -4 returns:

PS > $name.substring(4,($name.indexOf(",")-4))
SELECTME



I used -4 because you have to take into count the first 4 fixed characters
(remove them). Here's another example:

# get all characters until the comma sign
PS > $name.substring(0,$name.indexOf(","))
sameSELECTME

#get all characters until the comma sign and then remove the first 4 characters
PS > $name.substring(0,$name.indexOf(",")).remove(0,4)
SELECTME



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