none
Removing 1st row in Excel using PowerShell

    Question

  • Hello,

    I am facing a problem when exporting from Reporting Service 2005 to Excel. It seems there's a know issue preventing SSRS to export consistently to the 1st row in Excel document.

    To remedy, after report runs, I plan on going back to a specific folder, opening up excel files, navigating to the 2nd sheet/Tab (1st is report title page) and removing only the 1st row. That row is usually blank and it seems Excel likes to start the actual report on the 2nd.

    I am using the script below but it does not work as intended. I'm pretty new at PowerShell and would love you guys input. Please take a look at my script:

    $file = C:\filepath\TestFile.xls
    # Star Excel, hide window
    $excel = new-object -com Excel.Application -Property @{Visible = $false} 
    $workbook = $excel.Workbooks.Open($file) # Open the file
    $sheet = $workbook.Sheets.Item(1) # Activate the first worksheet
    [void]$sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
    
    $workbook.Close($true) # Close workbook and save changes
    $excel.quit() # Quit Excel

    I appreciate any constructive feedback on this


    • Edited by Tshindaye Friday, November 18, 2016 8:41 PM
    Friday, November 18, 2016 8:41 PM

Answers

All replies