Hi,
I have used power shell script to create sheets in Excel. Actually its creating the sheets and its working almost 160 sheets were created but after that i am getting error, its displaying popup like "save as" and the name of the file will be showing
like "Copy of filename". I am not able to find out the root cause.
I am using below power shell script
try {if (Test-Path "$vExcelDir$\completed.txt"){ Remove-Item "$vExcelDir$\completed.txt" }
if (Test-Path "$vExcelDir$\Error.txt"){ Remove-Item "$vExcelDir$\Error.txt" }
$file1 = "$vSourceFile$" # source's fullpath
$file2 = "$vDestinationFile$" # destination's fullpath
$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user
$wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
$wb1 = $xl.workbooks.open($file2) # open target
$sh1_wb1 = $wb1.sheets.item(2) # second sheet in destination workbook
$sheetToCopy = $wb2.sheets.item('$vSheetName$') # source sheet to copy
$sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook
$newSheet = $wb1.Worksheets.Item(2) # selecting second worksheet from the excel to move to end
$lastSheet = $wb1.WorkSheets.Item($wb1.WorkSheets.Count) #taking the count of worksheets
$newSheet.Move([System.Reflection.Missing]::Value, $lastSheet) #moving the copied sheet to the end
$wb2.close($false) # close source workbook w/o saving
$wb1.close($true) # close and save destination workbook
$xl.quit()
Add-Content "$vExcelDir$\completed.txt" "Done"
Write-Host "Job completed"
}
catch {Add-Content "$vExcelDir$\Error.txt" "NotDone"}
could anyone help me regarding this?
