Using Powershell to Append a table to the end of a

问题:

I've written a Powershell script that copies a table from Microsoft Word and pastes them to an excel document. I want to append it after the last used row in column A.

I got this code so far:

$Excel = New-Object -ComObject Excel.Application

$Excel.Visible = $True

$ExcelWordBook = $Excel.Workbooks.Open($ExcelPath)

$ExcelWorkSheet = $Excel.WorkSheets.item("Sheet1")

$ExcelWorkSheet.activate()

This is where I get confused, I want to be able to find the last row that is used in the Excel WorkSheet.

$lastRow = $ExcelWorkSheet.UsedRange.rows("A").count

$nextRow = $lastRow + 1

$ExcelWorkSheet.Range("A$nextRow").Select

$ExcelWorkSheet.Paste()  

---

I feel like I'm so close after this. I was trying to use the UsedRange property in the Office Interlop to determine the last row in the Excel WorkSheet. (http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.usedrange(v=vs.80).aspx) Any ideas?

回答1:

Thanks I ended up going a different route that's more dynamic instead of using "A9924082480198" as the end of the range.

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$ExcelWordBook = $Excel.Workbooks.Open($ExcelPath)
$ExcelWorkSheet = $Excel.WorkSheets.item("sheet1")
$ExcelWorkSheet.activate()

$lastRow = $ExcelWorkSheet.UsedRange.rows.count + 1
$Excel.Range("A" + $lastrow).Activate()
$ExcelWorksheet.Paste()

回答2:

The object returned by $ExcelWorkSheet.UsedRange has a property rows (not a method, you can't use a parameter). This means that $ExcelWorkSheet.UsedRange.rows.count will work, but won't give you the desired result (it will give you the row number of the las trow for the longest column).

To append something after the last used cell in column A, try the following:

$xldown = -4121 # see: http://msdn.microsoft.com/en-us/library/bb241212(v=office.12).aspx
$xlup = -4162
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$ExcelWordBook = $Excel.Workbooks.Open($ExcelPath)
$ExcelWorkSheet = $Excel.WorkSheets.item("Sheet1")
$ExcelWorkSheet.activate()

# Find the last used cell
$lastRow = $ExcelWorksheet.cells.Range("A1048576").End($xlup).row

$nextRow = $lastRow + 1
$range = $ExcelWorkSheet.Range("A$nextRow")
$ExcelWorkSheet.Paste($range)