locked
Open an Excel spreadsheet with powershell getting error RRS feed

  • Question

  • I am trying to open an excel file within power shell. Here is the code i am using 

    $excel

    = New-Object -com excel.application

     

     

    $excel

    .Visible = $True

     

     

     

    $excelfile

    = "C:\Users\adm_kmccallion\Desktop\AssetInventory.xlsx"

     

     

    $sheet

    ='Test'

     

     

    $ou

    = "ctnet.com/Eton"

     

     

     

    #$ExcelWorkbook = $Excel.Workbooks.Open($ExcelFile, 2, $True)

    $ExcelWorkbook

    = $Excel.workbooks.open("C:\Users\adm_kmccallion\Desktop\AssetInventory.xlsx")

     

    $ExcelWorkSheet

    = $Excel.Worksheets.Item($Sheet)

     

    but i am getting the following error

     

    Method invocation failed because [System.__ComObject] doesn't contain a method named 'open'.

    At C:\Users\adm_kmccallion\Desktop\test.ps1:11 char:39

    + $ExcelWorkbook = $Excel.workbooks.open <<<< ("C:\Users\adm_kmccallion\Desktop\AssetInventory.xlsx")

    + CategoryInfo : InvalidOperation: (open:String) [], RuntimeException

    + FullyQualifiedErrorId : MethodNotFound

     

    Any idea why?



    Tuesday, March 29, 2011 7:57 PM

Answers

  • $xl = New-Object -comobject Excel.Application
    # Show Excel
    $xl.visible = $true
    $xl.DisplayAlerts = $False
    # Create a workbook
    $wb = $xl.Workbooks.open("C:\Users\adm_kmccallion\Desktop\AssetInventory.xlsx")
    # Get sheets
    $ws = $wb.WorkSheets.item("Test")
    $ws.activate()
    Start-Sleep 1
    $Rng = $ws.UsedRange.Cells
    $row = $Rng.Rows.Count 
    • Marked as answer by Dale Qiao Monday, April 4, 2011 3:26 AM
    Wednesday, March 30, 2011 1:36 AM

All replies

  • Try this:

    $thisThread = [System.Threading.Thread]::CurrentThread
    $originalCulture = $thisThread.CurrentCulture
    $thisThread.CurrentCulture = New-Object System.Globalization.CultureInfo('en-US')
    
    YOUR CODE HERE
    
    $thisThread.CurrentCulture = $originalCulture
    

     

    Tuesday, March 29, 2011 8:14 PM
  • here is easier to read code

     

    $excelfile = "C:\Users\adm_kmccallion\Desktop\AssetInventory.xlsx"
    $sheet = 'Test'
    $ou = "ctnet.com/Eton"

    #$ExcelWorkbook = $Excel.Workbooks.Open($ExcelFile, 2, $True)
    $ExcelWorkbook = $Excel.workbooks.open("C:\Users\adm_kmccallion\Desktop\AssetInventory.xlsx")
    $ExcelWorkSheet = $Excel.Worksheets.Item($Sheet)
    $rowMax = ($excelworksheet.usedRange.rows).count

    Tuesday, March 29, 2011 8:15 PM
  • $xl = New-Object -comobject Excel.Application
    # Show Excel
    $xl.visible = $true
    $xl.DisplayAlerts = $False
    # Create a workbook
    $wb = $xl.Workbooks.open("C:\Users\adm_kmccallion\Desktop\AssetInventory.xlsx")
    # Get sheets
    $ws = $wb.WorkSheets.item("Test")
    $ws.activate()
    Start-Sleep 1
    $Rng = $ws.UsedRange.Cells
    $row = $Rng.Rows.Count 
    • Marked as answer by Dale Qiao Monday, April 4, 2011 3:26 AM
    Wednesday, March 30, 2011 1:36 AM
  • I am thankful to oldDog1 I made presentation change, your answer is best

    #declare paths
    $sqlls = "D:\scripts"
    $lxl = "D:\sqlFileList12.xls"
    #make a list
    ls $sqlls -FILTER *.sql | foreach {$_.name} | OUT-File $lxl -force 
    $xl = New-Object -comobject Excel.Application
    # Show Excel
    $xl.visible = $true
    $xl.DisplayAlerts = $False
    # Create a workbook
    $wb = $xl.Workbooks.open($lxl) 


    --Himanshu Kulkarni

    Tuesday, February 12, 2013 11:41 AM
  • Brilliant!
    Saturday, March 1, 2014 12:40 AM
  • This is working. Thanks...
    Thursday, January 4, 2018 11:26 AM