CODEWITHSUNDEEP
excelpowershell
user3565164
user3565164

Reputation: 441

Creating Powershell script to loop through excel file and create folders

I'm new to Powerscript and looking at writing a solution to loop through an excel file and create a folder for each row in the file.

At the moment I have the following:

$objExcel = new-object -comobject excel.application 

$objExcel.Visible = $True 

$ExcelFilesLocation = “D:\Users\”

$UserWorkBook = $objExcel.Workbooks.Open($ExcelFilesLocation + “ProjectCodes.xlsx”) 

$UserWorksheet = $UserWorkBook.Worksheets.Item(1)

$intRow = 2

Do {

 $Projectcode = $UserWorksheet.Cells.Item($intRow, 1).Value()

 $pos = $userLogOnEmail.IndexOf(“@”)

           $intRow++

           } While ($UserWorksheet.Cells.Item($intRow,1).Value() -ne $null)

$objExcel.Quit()

$a = Release-Ref($UserWorksheet)

$a = Release-Ref($UserWorkBook) 

$a = Release-Ref($objExcel)

The idea is to loop through the project code column for each row. Then create a folder that is named for the project code.

Upvotes: 0

Views: 8879

Answers (2)

ConanW
ConanW

Reputation: 486

Having spent painful hours wrangling Excel COM objects with PowerShell, my advice is to give up! Office COM objects are confusing, unwieldy and slow.

Replace the technology you use to access Excel in PowerShell with something better. For example, this module: https://github.com/dfinke/ImportExcel. You can use Install-Module ImportExcel -Scope CurrentUser if you're using PS 5.

Apart from being easier to use and much faster it doesn't require Excel to be installed, making your final script more portable.

Admitedly, you don't get full Excel functionality with this module but since you seem to be doing no more than reading cells, you should be fine.

Alternatively, Save your Excel file as CSV and use Import-Csv instead.

Upvotes: 1

G42
G42

Reputation: 10019

To create a new directory, use New-Item

For example, assuming $Projectcode is a string containing a valid path:

New-Item -Path $Projectcode -Type Directory

Upvotes: 0

Related Questions