locked
How to incorporate Powershell with Excel? RRS feed

  • Question

  • Hi all,

    i am new to powershell, I have a report in excel that was analyze manually all this while, i would like to try automate this report by using a powershell as i can see that the report is repetitive and it can be automate but i am not sure how to start honestly, i tried to google.. it shows the query that i can use etc. i tried to play around and it's working, but i still couldn't find a way to start to incorporate this powershell with Excel, does anyone can help me with this?

    Wednesday, November 21, 2018 2:35 AM

All replies

  • Hi,

    Thanks for your question.

    You can worked with Excel through COM interface.

    https://lazywinadmin.com/2014/03/powershell-read-excel-file-using-com.html

    Also Doug Finke's powershell excel module can help you.

    https://blogs.technet.microsoft.com/heyscriptingguy/2015/11/25/introducing-the-powershell-excel-module-2/

    http://ramblingcookiemonster.github.io/PSExcel-Intro/

    Best Regards,

    Lee


    Just do it.

    Wednesday, November 21, 2018 6:30 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    LEE


    Just do it.

    Tuesday, November 27, 2018 2:52 AM
  • CSV File:

    Firstname,LastName,Address
    Scott,Head,123 Street
    Sam,Head,246 Street
    Suzie,Head,189 Street

     Function ExcelWork(){
                   [CmdletBinding()]        
                    Param(
                    # \\ Parameters Defined Which Are Colum Headers in Excel CSV File \\
                        [Parameter(Mandatory=$True,ValueFromPipelineByPropertyName=$true)]
                        [string]$FirstName,
            
                        [Parameter(Mandatory=$True,ValueFromPipelineByPropertyName=$true)]
                        [string]$LastName,
    
                        [Parameter(Mandatory=$True,ValueFromPipelineByPropertyName=$true)]
                        [string]$Address
                   )       
                Process {
            
                #\\ Remove White Space from data \\
                $FirstName=$_.Firstname.Trim()                           
                $LastName=$_.LastName.Trim()        
                $Address=$_.Address.Trim()
                   
    
                #---------Do You Magic Here-------
                    Foreach-Object{
                    
                        Write-Output "This is : $FirstName"
                        Write-Output "This is : $LastName"
                        Write-Output "This is : $Address"
    
                    }            
                #---------------------------------
    
                    }                               
            }
              
                    #\\ Import CSV and Call Function\\
                    Import-CSV C:\Temp\Book1.csv |  ExcelWork
                  

    • Proposed as answer by ComputerScott Wednesday, November 28, 2018 10:44 PM
    • Unproposed as answer by jrv Wednesday, November 28, 2018 11:49 PM
    Wednesday, November 28, 2018 10:43 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Tuesday, December 4, 2018 6:46 AM