Saving an excel file to the current date folder

PowerShell Help
1

Hello,

I’m new to PowerShell scripting. I created a test script to convert a *.csv file from another folder and I want to save in a folder with the date. EX: c:\TEST_Convert\09072019\Convertfile_09072019.xls. Can you tell me how to add the date in the path?

Set-Location C:\Test_Convert
$newfolder=New-Item -Type Directory $(Get-Date -UFormat ‘%m%d%Y’)
$CurrentDate = Get-Date -UFormat ‘%m%d%Y’
$xl = new-object -comobject excel.application
$xl.visible = $true
$Workbook = $xl.workbooks.open(“C:\TEST_Copy\09072019\TestFile.csv”)
$Worksheets = $Workbooks.worksheets
$Workbook.SaveAs(“C:\TEST_Convert"$newfolder”\ConvertFile_$CurrentDate.xls”)
$Workbook.Saved = $True
$xl.Quit()

 

 

2

First: You forgot to format your code as code. Please use the code tag button when you post code.
Second: You forgot to tell what’s not working. :wink:

You don’t need to use quotation marks around variables - sometimes that’s even counterproductive if you don’t use the right ones as you can see in your command $Workbook.SaveAs … :wink:

You may take a look at the great module from Doug Finke ImportExcel. It will make your life easier I think. :wink:

Last problem I can see: you create a variable $Worksheets but you’re never using it.

3

Looks like you’re pretty much there. You just need to create the new directory using New-Item.

New-Item -Type Directory -Name $CurrentDate