Saving an excel file to the current date folder

Welcome Forums General PowerShell Q&A Saving an excel file to the current date folder

This topic contains 2 replies, has 3 voices, and was last updated by

 
Participant
1 week, 4 days ago.

  • Author
    Posts
  • #175225

    Participant
    Topics: 1
    Replies: 0
    Points: 16
    Rank: Member

    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()

     

     

  • #175282

    Participant
    Topics: 1
    Replies: 1529
    Points: 2,587
    Helping Hand
    Rank: Community Hero

    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. 😉

    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 ... 😉

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

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

  • #175486

    Participant
    Topics: 6
    Replies: 78
    Points: 143
    Helping Hand
    Rank: Participant

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

    New-Item -Type Directory -Name $CurrentDate

You must be logged in to reply to this topic.