How can we help?

  1. JAMS Scheduler
  2. PowerShell

How to Automate the opening of an Excel Spreadsheet in Powershell

Follow
—

  

# Specify the path to the Excel file and the WorkSheet Name
$FilePath = "C:\TestSheet.xlsx"


# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application

# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false

# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)

Note: This will only open the worksheet, additional items can be automated, but this is a great example for when the spreadsheet contains macros, and the rest of the operations are already taken care of.

We recommend not using Excel whenever possible, and stick to Powershell, VB, or other non-interactive programs to automate tasks.  However if an existing task has already been defined, it certainly is possible.

  


Have more questions? Submit a request

Comments

  • Avatar
    Clifton Dunaway

    When I tried this, I get this error:
    PS C:\WINDOWS\system32> $WorkBook = $objExcel.Workbooks.Open($FilePath)
    You cannot call a method on a null-valued expression.
    At line:1 char:1
    + $WorkBook = $objExcel.Workbooks.Open($FilePath)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull