Sign in
Microsoft.com
 
Microsoft
 
 
 

none
Creating New Excel Worksheets from List RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    "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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Ultimately the question is how do I make new excel worksheets that are named with the folder names that are pulled in $dirlist 
    Wednesday, April 25, 2018 9:58 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Just use the "name" property of the directory object.

    \_(ツ)_/

    Wednesday, April 25, 2018 10:00 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Just use the code I posted.  Change the variable names to match yours.

    \_(ツ)_/

    Wednesday, April 25, 2018 10:25 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |