locked
Not being able to save files based on Dates prompted RRS feed

  • Question

  • Hi guys,

    Im having trouble trying to save reports based on dates prompted as in the file name does not show up in Temp folder at all. (already created)

    [DateTime]$Date1 = Read-Host "Enter the first date(yyyy-mm-dd)" 
    [DateTime]$Date2 = Read-host "Enter the second date(yyyy-mm-dd)"
    
    write-host "Generating the the Report............";
    $a = $a + 1; 
    Import-CSV -Path 'C:\Temp\Report.csv' |  Where { ([DateTime]$_.'Date 1' -ge [DateTime]$Date1) -and ([DateTime]$_.'Date 2' -le [DateTime]$Date2) -and ($_.'Group Tasks' -in $GroupArray) } | Export-Excel -Path "C:\Temp\Report.xls"
    $excel2 = New-Object -ComObject excel.application;
    $excel2.visible=$false;
    Start-Sleep -Seconds 1;
    $wb= $excel2.Workbooks.Open("C:\Temp\Report.xls");
    $Data = $wb.ActiveSheet
    $eRow = $Data.cells.item(2,1).entireRow
    $active = $eRow.activate()
    $active = $eRow.insert($xlShiftDown)
    $eRow = $Data.cells.item(3,1).entireRow
    $active = $eRow.activate()
    $active = $eRow.insert($xlShiftDown)
    [void]$wb.Activesheet.Cells.EntireColumn.Autofit();
    $wb.SaveAs("C:\Temp\ReportbyDate.xls",51);
    $wb.Close(0);
    $excel2.Quit();
    Remove-Item "C:\Temp\Report.xls"
    $DestinationFile = 'C:\Temp\Report_between_{0}-{1}.xlsx' -f $Date1.ToString('yyyy-MM-dd'), $Date2.ToString('yyyy-MM-dd')
    Move-Item "C:\Temp\Reportbydate.xls" -Destination $DestinationFile

    No matter what I try, I even tested out 2019-11-04 and 2019-11-20 when I enter the dates and the file does not generate on the Temp folder. What am I doing wrong in this script?


    Tuesday, November 5, 2019 3:17 PM

All replies

  • It is likely that you have no records that match the criteria.  Try simplifying and check the output.

    [DateTime]$date1 = Read-Host "Enter the first date(yyyy-mm-dd)" 
    [DateTime]$date2 = Read-host "Enter the second date(yyyy-mm-dd)"
    Import-CSV C:\Temp\Report.csv |  
        Where-Object{
            [DateTime]$_.'Date 1' -ge $date1 -and
            [DateTime]$_.'Date 2' -le $date2 -and
            $_.'Group Tasks' -in $GroupArray 
        }



    \_(ツ)_/


    • Edited by jrv Tuesday, November 5, 2019 3:36 PM
    Tuesday, November 5, 2019 3:29 PM
  • Thats not it. When I tested it out without using 

    $DestinationFile = 'C:\Temp\Report_between_{0}-{1}.xlsx' -f $Date1.ToString('yyyy-MM-dd'), $Date2.ToString('yyyy-MM-dd')
    Move-Item "C:\Temp\Reportbydate.xls" -Destination $DestinationFile
    It works because it gave me the output. But the problem is when I want to save it as a filename that involves dates, I cant see  that 
    'C:\Temp\Report_between_{0}-{1}.xlsx

    being created in my temp folder.

    Tuesday, November 5, 2019 3:49 PM
  • This is not a file name it is a template.

    'C:\Temp\Report_between_{0}-{1}.xlsx

    $destinationFile = 'C:\Temp\Report_between_{0:yyyy-MM-dd}-{1:yyyy-MM-dd}.xlsx' -f $date1, $date2
    Rename-Item C:\Temp\Reportbydate.xls -NewName $destinationFile -verbose
    Test-Path $destinationFile
    The PowerShell convention says that variables begin with lowercase and should be in lower camelCase.


    \_(ツ)_/


    Tuesday, November 5, 2019 4:02 PM
  • This is the correct way to do this:

    $reporttemp = 'C:\Temp\Report.xlsx'
    
    [datetime]$date1 = Read-Host "Enter the first date(yyyy-mm-dd)" 
    [datetime]$date2 = Read-host "Enter the second date(yyyy-mm-dd)"
    Import-CSV C:\Temp\Report.csv |  
        Where-Object{
            [datetime]$_.'Date 1' -ge $date1 -and
            [datetime]$_.'Date 2' -le $date2 -and
            $_.'Group Tasks' -in $GroupArray 
        } |
        Export-Excel -Path C:\Temp\Report.xlsx -WorksheetName ReportByDate
    
    $xl = New-Object -ComObject excel.application
    $wb = $xl.Workbooks.Open($reporttemp)
    $wb.WorkSheets['ReportByDate'].Cells(2,1).EntireRow.Insert([Microsoft.Office.Interop.Excel.XlInsertShiftDirection]::xlShiftDown)
    $wb.WorkSheets['ReportByDate'].Cells(2,1).EntireRow.Insert([Microsoft.Office.Interop.Excel.XlInsertShiftDirection]::xlShiftDown)
    [void]$wb.Activesheet.Cells.EntireColumn.Autofit()
    $reportFile = 'C:\Temp\Report_between_{0:yyyy-MM-dd}-{1:yyyyMMdd}.xlsx' -f $date1, $date2
    $wb.SaveAs($reportFile)
    $wb.Close(0)
    $xl.Quit()
    
    Remove-Item $reporttemp

    Your approach is old and copied from old VBS and earlier versions of Excel.  It is prone to many unwanted side effects.

    There is no need to add 51 as the "SaveAs" will default to the current Excel default which is XLSX.

    Why try to move or rename the file when you can just save it as the name you want.


    \_(ツ)_/



    Tuesday, November 5, 2019 4:23 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, December 6, 2019 9:46 AM