none
Powershell opening and saving Excel 2013 file RRS feed

  • Question

  • I'm having an issue with Powershell 3.0 saving an Excel 2013 file. My script opens an existing Excel file, adds data to the next free row before saving the file and sending it via email as an attachment. No matter what I try the Excel process stays open, if I kill the process the script runs the next time I execute it but does not add any data.

    Based on other threads I tried the following:

    $file="c:\scripts\mbx_connections.xlsx"
    $xl=New-Object -ComObject "Excel.Application"
    $wb=$xl.Workbooks.Open($file)
    $ws=$wb.ActiveSheet
    $xl.DisplayAlerts = $FALSE
    
    $cells=$ws.Cells
    $rows = $ws.UsedRange.Rows.Count
    
    <snip>
    
    $wb.SaveAs("c:\scripts\mbx_connections.xlsx")
    
    $wb.Close()
    $xl.Quit()
    
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
    Remove-Variable wb
    Remove-Variable xl

    Are there any other ways to close the file or known issues with using Excel 2013?
    Monday, December 16, 2013 2:44 PM

Answers

  • You are creating cells.  Any time you assign any object you are creating a reference that has to be removed.

    ¯\_(ツ)_/¯

    • Marked as answer by DPFY Monday, December 16, 2013 5:18 PM
    Monday, December 16, 2013 4:47 PM

All replies

  • You created three com objects you have to release three com objects;

    while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($ws)){}
    while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)){}
    while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){}

    Note that you should release in a loop until the count is zero.

    Try to reduce the number of convenience objects you create.

    $xl=New-Object -ComObject "Excel.Application"
    $xl.DisplayAlerts = $FALSE
    
    $wb=$xl.Workbooks.Open($file)
    
    $cells=$wb.ActiveSheet.Cells
    $rows = $wb.ActiveSheet.UsedRange.Rows.Count
    
    <snip>
    
    $wb.SaveAs("c:\scripts\mbx_connections.xlsx")
    
    $wb.Close()
    $xl.Quit()
    
    # now you only need to release two.
    while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)){}
    while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){}


    ¯\_(ツ)_/¯

    Monday, December 16, 2013 3:47 PM
  • Thanks for the tips.

    Unfortunately the issue is the same, the Excel.exe process remains until I close the Powershell window. I'll try on a different computer later.

    Monday, December 16, 2013 4:26 PM
  • You did not post the whole script so it is likely you are creating other objects along the way.


    ¯\_(ツ)_/¯

    Monday, December 16, 2013 4:32 PM
  • This is the script in full after I got rid of anything else that might cause a problem. I guess I'm creating a mail object:

    $ErrorActionPreference= 'silentlycontinue'
    
    $date = (get-date -format "dd-MMM-yyyy") 
    $file="c:\scripts\mbx_connections.xlsx"
    
    $xl=New-Object -ComObject "Excel.Application"
    $xl.DisplayAlerts = $FALSE
    
    $wb=$xl.Workbooks.Open($file)
    
    $cells=$wb.ActiveSheet.Cells
    $rows = $wb.ActiveSheet.UsedRange.Rows.Count
    
    $b = "b"
    $c = "c"
    $d = "d"
    $e = "e"
    
    [int]$total = 5
    
    $rows = $rows + 1
    
    $cells.item($rows,1)=$time
    $cells.item($rows,2)=$b
    $cells.item($rows,3)=$c
    $cells.item($rows,4)=$d
    $cells.item($rows,5)=$e
    $cells.item($rows,6)=$total
    
    $wb.Save("c:\scripts\mbx_connections.xlsx")
    
    $wb.Close()
    $xl.Quit()
    
    while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)){}
    while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){}
    Remove-Variable wb
    Remove-Variable xl
    
    Function EmailResults
     {
     
    Param ($Subject, $Body)
     $message = New-Object Net.Mail.MailMessage
     $message.From = "exchangereport@x.com"
     $message.To.Add("x@x.com")
     $message.Body = $Body
     $message.IsBodyHTML = $true
     $message.Subject = $Subject
     $message.Attachments.Add($file)
    
     
            $smtpclient = New-Object Net.Mail.SmtpClient("xxx")
             $smtpclient.port=25
             $smtpclient.Send($message)
      }
    
    $Subject = "Connections Report - $date"
    $body = "<html><body>Mailbox connections report - $timestamp. See Excel attachment.</body></html>"
    
    EmailResults $Subject $Body
    

    Monday, December 16, 2013 4:40 PM
  • You are creating cells.  Any time you assign any object you are creating a reference that has to be removed.

    ¯\_(ツ)_/¯

    • Marked as answer by DPFY Monday, December 16, 2013 5:18 PM
    Monday, December 16, 2013 4:47 PM
  • That helped :) Now the Excel process terminates correctly but the file will only open as read only unless I kill the Powershell window.

    Thanks for the help!


    Update: found out it was EmailResults function that was holding on to the Excel file after the script had finished.
    • Edited by DPFY Monday, December 16, 2013 5:18 PM update
    Monday, December 16, 2013 4:57 PM
  • Use Send-MaiMessage and it will work correctly.


    ¯\_(ツ)_/¯

    Monday, December 16, 2013 5:48 PM
  • I used the following and this also seemed to work:

    mail.Attachments.Dispose()
    mail.Dispose()

    I also noticed that sometimes the Excel.exe process stays open but gets replaced when the script is run again. Not causing any problems anyway.

    Monday, December 16, 2013 7:45 PM
  • This is much more reliable:

    $params=@{
         SmtpServer='smtp.domain.com'
         Subject=$subject
         Body=$Body
         From = 'exchangereport@x.com'
         To='x@x.com'
         Body= $body
         IsBodyHTML=$true
         Attachments=$file
    }
    Send-MailMessage @params 
    


    ¯\_(ツ)_/¯

    Monday, December 16, 2013 8:01 PM