Submitted By: Kent Finkle

Copies a worksheet from one location to another.

PowerShell
Edit|Remove
$comments = @'
Script name: Avoid-Error1004.ps1
Created on: Sunday, August 26, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
avoid run-time error 1004 in Excel?
See http://support.microsoft.com/kb/210684/en-us
Copying worksheet programmatically causes run-time error 1004 in Excel
'@
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers() 
}
#-----------------------------------------------------
$xl = new-object -comobject excel.application
$xl.Visible = $True
$xl.DisplayAlerts = $False
$iTemp = $xl.SheetsInNewWorkbook
$xl.SheetsInNewWorkbook = 1

# Create a new blank workbook
$wb = $xl.Workbooks.Add() 
$ws = $wb.Worksheets.Item("Sheet1") 
$xl.SheetsInNewWorkbook = $iTemp
 
# Save the workbook:
$wb.SaveAs("c:\scripts\test2.xls")
 
# Copy the sheet in a loop. Eventually,
# you get error 1004: Copy Method of
# Worksheet class failed.
for ($i = 1; $i -lt 275; $i++) {
    $ws = $wb.Worksheets.Item(1).Copy($wb.Worksheets.Item(1))
    # the workaround:
    # Save, close, and reopen after every 100 iterations:
    $b = $i % 100
    If ($b -eq 0) {
        $wb.Save()
        $wb.Close()
        $wb = $xl.Workbooks.Open("c:\scripts\test2.xls")
    }
}
 
$wb.Save()
$wb.Close()
$xl.Quit()
 
$a = Release-Ref $wb
$a = Release-Ref $xl