Asked by:
Creating New Excel Worksheets from List

Question
-
Hey Everyone, I am some what new to powershell and have been assigned a task that has got me stuck. 1. I need to pull a list of file names, and then create new excel worksheets with those names. 2 if the name is longer than 31 characters shorten it so excel does not error out. 3 pull all the files in the sub dir: example j:\*\*\ and put the results in the corresponding excel file. I will be totally happy if I can just get the first two parts sorted out. Below is what I came up with on my own. Also a big thanks to anyone that helps out!!!
$dirlist = gci j:\ | select-object -ExpandProperty name
$dirsblist = gci j:\*\*
$SheetVar = 1
{
$excel = new-object -comobject Excel.Application
$excel.visible = $true
$WorkBook = $Excel.Workbooks.Add()
$workbook.Worksheets.add() ###I think this is where i am going wrong
$count = $excel.worksheets.count
}
{
ForEach($name in $dirlist)
{
$Sheet = $excel.worksheets.item($sheetvar)
$Sheet.name = "$name"
}}
- Edited by cmoneyg Wednesday, April 25, 2018 8:28 PM
- Moved by Bill_Stewart Friday, July 27, 2018 6:22 PM This is not "scripts on demand"
Wednesday, April 25, 2018 8:27 PM
All replies
-
There is no 31 character limit in Excel.
To create an Excel sheet just export to a CSV and import into Excel.
gci j:\ -recurse | Select Name | Export-Csv files.csv
. .\file.csv # will open in Excel
\_(ツ)_/
Wednesday, April 25, 2018 8:49 PM -
Thank you for your response, but there is a character limit for the excel sheet names.
##https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_archive/how-to-increase-sheet-name-character-limit/f2ce24ed-36bc-45b9-a474-2dc5435adfb1?auth=1##
While your commands create a excel sheet it just dumps everything there which is not what I am going for.
Wednesday, April 25, 2018 9:02 PM -
"Sheet names"? Why would you want to name sheets after files?
To shorten a string just clip it.
'one long name 123456789012345678901234567890'.SubString(0,30)
The sheet name limit is actually 30 characters.
\_(ツ)_/
Wednesday, April 25, 2018 9:09 PM -
Well Its actually folders. The names are folder names and then the sub folders are the project #s assign to them.
So the idea is each folder has a excel sheet with its name, and then inside that sheet is there name the project #s
nameblabla:
12254659blabla
12254660blablablabla
ec. ect.
Wednesday, April 25, 2018 9:39 PM -
So what is the question? Just truncate the folder name and use it to name the sheet.
$newsheet = $workbook.WorkSheets.Add()
$newsheet.Name = $name.SubString(0,30)\_(ツ)_/
Wednesday, April 25, 2018 9:47 PM -
Ultimately the question is how do I make new excel worksheets that are named with the folder names that are pulled in $dirlistWednesday, April 25, 2018 9:58 PM
-
Just use the "name" property of the directory object.
\_(ツ)_/
Wednesday, April 25, 2018 10:00 PM -
If you would will you show me where/how you would put that in the code. Thanks
$dirlist = gci j:\ | select-object -ExpandProperty name
$dirsblist = gci j:\*\*
$SheetVar = 1
{
$excel = new-object -comobject Excel.Application
$excel.visible = $true
$WorkBook = $Excel.Workbooks.Add()
$workbook.Worksheets.add()
$count = $excel.worksheets.count
}
{
ForEach($name in $dirlist)
{
$Sheet = $excel.worksheets.item($sheetvar)
$Sheet.name = "$name"
}}Wednesday, April 25, 2018 10:05 PM -
Just use the code I posted. Change the variable names to match yours.
\_(ツ)_/
Wednesday, April 25, 2018 10:25 PM -
Here is how to add sheets to a workbook:
Get-ChildItem j:\ | ForEach-Object{ $newsheet = $workbook.WorkSheets.Add() $newsheet.Name = $_.Name.SubString(0,30) }
\_(ツ)_/
Wednesday, April 25, 2018 10:28 PM -
I think we are getting wrapped up in semantics here. Do you want a separate Excel file for each of the folders in $Dirlist and then the subfolders of that folder in the spreadsheet? Or do you want one large Excel file with a sheet for each of the folders in $Dirlist and then the contents of the subfolders in each sheet?
It is a very different approach for each of these methods.
Thursday, April 26, 2018 1:08 PM