Saving an excel file to the current date folder

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

Viewing 2 reply threads
  • 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: 1635
      Points: 3,089
      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: 108
      Points: 302
      Helping Hand
      Rank: Contributor

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

      New-Item -Type Directory -Name $CurrentDate
Viewing 2 reply threads
  • The topic ‘Saving an excel file to the current date folder’ is closed to new replies.