none
Sheets Creating in Excel using Powershell RRS feed

  • Question

  • 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?

    • Moved by Bill_Stewart Monday, July 30, 2018 1:34 PM This is not "debug/fix/rewrite my script for me" forum
    Wednesday, June 6, 2018 7:39 AM

All replies

  • Can you please post your code correctly using the code posting tool provided?


    \_(ツ)_/

    Wednesday, June 6, 2018 4:18 PM
  • Variables in PowerShell should not end in a dollar sign.

    This $vExcelDir$ should be this $vExcelDir.


    \_(ツ)_/

    Wednesday, June 6, 2018 4:23 PM