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?

• Edited by Kevin McCallion Tuesday, March 29, 2011 8:16 PM

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

 

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

$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 QiaoModerator Monday, April 04, 2011 3:26 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) 

Brilliant!