New to PS, Excel Reference

PowerShell Help
1

I’m very new to Powershell, found this script and modified it a bit to do what I want. It does the job but I’m losing my formulas and formatting. I’ve tried to find some sort of reference as far as commands go on Microsofts powershell website but I can’t find anything related to excel. Excel functionality must be included somehow because this worked with my fresh install of Windows 10. This script essentially takes a range of cells in a source workbook and pastes it into a range of cells in all workbooks placed in a folder. Can anyone point me in the right direction as far as a reference page goes? I’m looking for a PasteSpecial command I believe? Also, I’m looking to rename the files and place them in folders based on the name of a cell in each workbook. I’m using Powershell 5.1

Thanks!

$sourceFile = "C:\Users\csmit\Desktop\Chris\Synapse\SynapseExcelTemplate.xlsx"
$destinationDirectory = "C:\Users\csmit\Desktop\Chris\Synapse\Sources"
$sheetName = "Sheet1"
$rangeToCopyStart = "A1"
$rangeToCopyEnd = "D8"
$sheetName2 = "Summary"
$rangeToPasteStart = "D12"
$rangeToPasteEnd = "G19"

#----------------------------------------------
# Open Excel source file
#----------------------------------------------

$excelApplication = New-Object -comobject Excel.Application                        
$excelWorkbook = $excelApplication.Workbooks.Open($sourceFile, 2, $True)
$excelWorksheet = $excelWorkbook.Worksheets.Item($sheetName)            

#----------------------------------------------
# Copy the cell value 
#----------------------------------------------

"Value to copy:" + $excelWorksheet.Range($rangeToCopyStart, $rangeToCopyEnd).Value2;
"From:" + $sourceFile;
$excelWorksheet.Range($rangeToCopyStart, $rangeToCopyEnd).Copy() | out-null;
$excelWorkbook.Close();                                                

#----------------------------------------------
# Get all Excel files from destination directory 
#----------------------------------------------

$Files = Get-ChildItem $destinationDirectory -Filter *.xlsx

Foreach ($Item in $Files) {

    $destinationFile = $Item.FullName

    #----------------------------------------------
    # Skip the source file if it's in the same directory 
    #----------------------------------------------


    If ($sourceFile.ToLower() -eq $destinationFile.ToLower())  { continue; }  

    $destinationWorkbook = $excelApplication.Workbooks.Open($destinationFile, 2, $False)       
    $destinationWorkSheet = $destinationWorkbook.Worksheets.Item($sheetName2)                 

    #----------------------------------------------
    # Paste the value into the destination file
    #----------------------------------------------

    $destinationWorkSheet.Paste($destinationWorkSheet.Range($rangeToPasteStart, $rangeToPasteEnd)); 
    $destinationWorkbook.Close($True);  #save changes and close

    "Copied to: " + $destinationFile;
}


#----------------------------------------------
# Quit Excel and release the object
#----------------------------------------------

$excelApplication.Quit();
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelApplication) | out-null;
2

I’m not sure if it will handle what you need but PSExcel is a pretty good module to work with Excel spreadsheets. It might save you having to try and figure out how to manually code this yourself.

ImportExcel is another module that looks to be a bit more extensive in its capabilities.

3

You have a bad assumption here:
Excel functionality must be included somehow because this worked with my fresh install of Windows 10.

there isn’t any actual base excel functionality for powershell.

you are using the com objects to open and work with the excel application itself, not any actual powershell native cmdlets.

John’s suggestions are both good, I personally use importexcel for my excel needs.

4

Thanks! I’ll like into both to see what works best.