Read Excel sheet in Powershell

by

This assumes that the content is in column B on each sheet (since it’s not clear how you determine the column on each sheet.) and the last row of that column is also the last row of the sheet.

$xlCellTypeLastCell = 11 
$startRow = 5 
$col = 2 

$excel = New-Object -Com Excel.Application
$wb = $excel.Workbooks.Open("C:\Users\Administrator\my_test.xls")

for ($i = 1; $i -le $wb.Sheets.Count; $i++)
{
    $sh = $wb.Sheets.Item($i)
    $endRow = $sh.UsedRange.SpecialCells($xlCellTypeLastCell).Row
    $city = $sh.Cells.Item($startRow, $col).Value2
    $rangeAddress = $sh.Cells.Item($startRow + 1, $col).Address() + ":" + $sh.Cells.Item($endRow, $col).Address()
    $sh.Range($rangeAddress).Value2 | foreach 
    {
        New-Object PSObject -Property @{ City = $city; Area = $_ }
    }
}

$excel.Workbooks.Close()

More Related Contents:

  1. Why does Range.BorderAround emit “True” to the console?
  2. How to export a CSV to Excel using Powershell
  3. Export hashtable to CSV with the key as the column heading
  4. Can’t get all excel processes to stop when closing through Powershell
  5. Excel, save and close after run
  6. Save excel file multiple times with file names in another workbook.
  7. Is it possible to force Excel recognize UTF-8 CSV files automatically?
  8. Read/Parse text file line by line in VBA
  9. How do I find the last column with data?
  10. Excel VBA: Answer gets “stuck”
  11. filter out multiple criteria using excel vba
  12. Excel VBA autofilter all but three
  13. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  14. Generating CSV file for Excel, how to have a newline inside a value
  15. Excel VBA code to copy a specific string to clipboard
  16. Excel VBA – Range.Copy transpose paste
  17. How do you get a Range to return its Name?
  18. What charset does Microsoft Excel use when saving files?
  19. How can I run VBA code each time a cell gets its value changed by a formula?
  20. How to convert 32 bit VBA code into 64 bit VBA code
  21. Referencing Excel Userform Control Name Value From String (VBA)
  22. Refreshing all the pivot tables in my excel workbook with a macro
  23. Creating a chart in Excel that ignores #N/A or blank cells
  24. Countif With Multiple OR Criteria
  25. Vlookup to copy color of a cell
  26. every possible combination of the contents of 2 columns in excel
  27. How to delete rows in Excel based on criteria using VBA?
  28. Macro to export MS Word tables to Excel sheets
  29. VBA WinHTTP to download file from password proteced https website
  30. How to highlight selected text within excel

Leave a Comment