New to PS, Excel Reference

This topic contains 3 replies, has 3 voices, and was last updated by  Chris 2 weeks, 6 days ago.

  • Author
    Posts
  • #100216

    Chris
    Participant

    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;
  • #100222

    John Steele
    Participant

    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.

  • #100240

    David Schmidtberger
    Participant

    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.

  • #100257

    Chris
    Participant

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

You must be logged in to reply to this topic.