none
Overwrite excel file, Powershell

    Question

  • Hi

    Does anyone know the powershell code to overwrite an excel file?

    Thanks
    Tuesday, December 08, 2009 2:11 AM

Answers

  • There's no built-in snazzy way to export to Excel, per-say... (at least not that I know of)


    So, then you should look into the "Excel.Application" object -> http://msdn.microsoft.com/en-us/library/wss56bz7%28VS.80%29.aspx

    PS> $excelApp = New-Object -ComObject Excel.Application
    PS> $excelApp | Get-Member -Type Method



    Let's say you have an array of tab-delimited strings in $myData you want to write to Excel...

    ... you could dump this data to a standard text file temporarily with a simply loop, we'll call this file "myTempText.txt"


    # Create an Excel object
    $excelApp = New-Object -ComObject Excel.Application
    $excelApp.Visible = $True
    $excelApp.DisplayAlerts = $True

    # Open your tab-delimited text into a new Workbook
    $file = (Get-ChildItem "myTempText.txt").FullName
    $objWorkbook = $excelApp.Workbooks.Open($file)

    # Do some formatting or whatever...
    [void]$excelApp.Range("A1:E1").Select()
    $excelApp.Selection.Interior.ColorIndex = 15
    [void]$excelApp.Selection.AutoFilter()
    [void]$excelApp.Cells.Item(2,1).Select()
    $excelApp.ActiveWindow.FreezePanes = $True
    [void]$excelApp.Cells.Item(1,1).Select()

    # Save yo' file...
    $file = $file.Substring(0, $file.Length - 3) + "xls"
    $objWorkbook.SaveAs($file, 1)

    # Close up...
    $objWorkbook.Close ; [void]$excelApp.Quit()




    ==> The SaveAs method will overwrite your old file automatically...


    Check out this article too:
    http://blogs.technet.com/heyscriptingguy/archive/2006/09/08/how-can-i-use-windows-powershell-to-automate-microsoft-excel.aspx


    Hope this helps...



    • Marked as answer by TonyFlora63 Tuesday, December 08, 2009 4:22 PM
    Tuesday, December 08, 2009 3:30 PM

All replies

  • Umm... I think you're gonna' have to be more specific with your question... What are you looking to do?


    You have data in a variable and you want to write it out to an Excel file? Then you can leverage the "Excel.Application" object -> http://msdn.microsoft.com/en-us/library/wss56bz7%28VS.80%29.aspx

    PS> $excelApp = New-Object -ComObject Excel.Application
    PS> $excelApp | Get-Member -Type Method


    Or, to be even simpler, you can leverage the built-in "Export-Csv" cmdlet of Powershell...

    Assumption, your data is in comma-separated or tab-separated array of strings contained in [array]$myData

    PS> $myData | Export-Csv -NoTypeInformation -Path .\myFile.csv


    By default, "Export-Csv" will overwrite your file. If you DON'T want this behavior then run the command as follows:

    PS> $myData | Export-Csv -NoTypeInformation -Path .\myFile.csv -NoClobber


    You could then open the CSV in Excel, format it, and save it out to an XLS file...


    More info. here:

    PS> Get-Help Export-Csv


    Tuesday, December 08, 2009 9:56 AM
  • Hi Izblah

    I usually use the Export-CSV cmdlet but I figured there must be a way in powershell to send output to an excel file to eliminate the necessity of saving the csv file as an excel ss and formating.

    What I'm actually trying to accomplish is overwriting the excel file everytime I run my script. If I can't find the code to do this I will consider just using the Test-Path and Remove-Item cmdlets to just delete the file before saving the new one.

    Thanks

    Tuesday, December 08, 2009 2:16 PM
  • There's no built-in snazzy way to export to Excel, per-say... (at least not that I know of)


    So, then you should look into the "Excel.Application" object -> http://msdn.microsoft.com/en-us/library/wss56bz7%28VS.80%29.aspx

    PS> $excelApp = New-Object -ComObject Excel.Application
    PS> $excelApp | Get-Member -Type Method



    Let's say you have an array of tab-delimited strings in $myData you want to write to Excel...

    ... you could dump this data to a standard text file temporarily with a simply loop, we'll call this file "myTempText.txt"


    # Create an Excel object
    $excelApp = New-Object -ComObject Excel.Application
    $excelApp.Visible = $True
    $excelApp.DisplayAlerts = $True

    # Open your tab-delimited text into a new Workbook
    $file = (Get-ChildItem "myTempText.txt").FullName
    $objWorkbook = $excelApp.Workbooks.Open($file)

    # Do some formatting or whatever...
    [void]$excelApp.Range("A1:E1").Select()
    $excelApp.Selection.Interior.ColorIndex = 15
    [void]$excelApp.Selection.AutoFilter()
    [void]$excelApp.Cells.Item(2,1).Select()
    $excelApp.ActiveWindow.FreezePanes = $True
    [void]$excelApp.Cells.Item(1,1).Select()

    # Save yo' file...
    $file = $file.Substring(0, $file.Length - 3) + "xls"
    $objWorkbook.SaveAs($file, 1)

    # Close up...
    $objWorkbook.Close ; [void]$excelApp.Quit()




    ==> The SaveAs method will overwrite your old file automatically...


    Check out this article too:
    http://blogs.technet.com/heyscriptingguy/archive/2006/09/08/how-can-i-use-windows-powershell-to-automate-microsoft-excel.aspx


    Hope this helps...



    • Marked as answer by TonyFlora63 Tuesday, December 08, 2009 4:22 PM
    Tuesday, December 08, 2009 3:30 PM
  • The following worked for me:

    $excelApp.DisplayAlerts = $False


    $objWorkbook.SaveAs($file, 1)

    Thanks!
    Tuesday, December 08, 2009 4:24 PM