CODEWITHSUNDEEP
powershell
Sooraj K
Sooraj K

Reputation: 11

Save an open Excel sheet using Powershell

I am completely newbie to Powershell. Need your help in saving an opened excel sheet using Powershell.

Script goes something like this

$xlPasteValues = -4163
$xlCellTypeLastCell = 11
$xl = new-object -comobject excel.application
$xl.Visible = $True
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Add()
$i = 1
$collection = Get-ChildItem C:\Test\* -include *.csv  # Change the location of your CSV files here.
$length = 4
foreach ($item in $collection) {
    $wb1 = $xl.Workbooks.Open("$item")
    $array = $item.ToString()
    $delim = "\"
    $SheetName = $array.split($delim)
    $s = $SheetName[2]
    $sn = $s.split(".")
    $nsn = $sn[0]
    $ws1 = $wb1.worksheets | where {$_.name -eq $nsn}
Write-Host $item $nsn
$used = $ws1.usedRange
$used.Select()
$used.copy()
$wb.Activate()
$ws = $wb.Sheets.Add()
$ws2 = $wb.worksheets | where {$_.name -eq "sheet$i"}
[void]$ws2.Range("A1").PasteSpecial(-4163)
$ws2.name = $nsn
$i++ 
$wb1.Close()
}
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat =[Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$Excel = New-Object -comobject Excel.Application
$Excel.Visible = $true

Upvotes: 0

Views: 10895

Answers (2)

johnnymatthews
johnnymatthews

Reputation: 1041

Your question was rather vague so I'm assuming that you want to know how to open and save an Excel document through Powershell.

Open your Excel Document using New-Object

$a = New-Object -COM "Excel.Application"
$a.Visible = $true
$b = $a.Workbooks.Open("C:\PATH\TO\YOUR\EXCEL\sheet.xlsx")

Save and close your document

$b.Save()
$b.Close()

Upvotes: 3

Doug Finke
Doug Finke

Reputation: 6823

Check out my PowerShell Excel Module on Github. You can also grab it from the PowerShell Gallery.

Then try:

$xlFileName="c:\temp\test.xlsx"    

dir *.csv | 
    ForEach {
        $sheetName=$_.Name.Split('.')[0]

        Import-Csv $_.FullName | 
            Export-Excel $xlFileName -WorkSheetname $sheetName
    }

Invoke-Item $xlFileName

enter image description here

Upvotes: 1

Related Questions