Author Posts

July 1, 2016 at 8:45 am

I have a spread sheet which I'm extracting data from. The spread sheet has a load of useless lines so I start at A5. Columns go through to Q and currently data ends at row 24 thus I have
$Range = $WorkSheet.Range("a5","q24")
But if some one adds a line to the spread sheet I am sung so how do I select the range from being A5 TO Q(whatever the last row with data is?

July 1, 2016 at 10:19 am

You would need to have your script inspect the spreadsheet and based on the value of the data in the cells determine where to end your range.

July 1, 2016 at 10:21 am

Im a beginner sorry. How would I do that

July 1, 2016 at 10:50 am

If the key value to determine if the row has usable data is in column A you would do something like this.

$row = 5 
do{
  If($WorkSheet.ActiveSheet.Cells.Item($row,1).Text -ne "Awesome Text"){$endofrange = $row}
  $row++
}until($endofrange)

This will start and A5 and keep checking until "Awesome Text" is not found and and give you a variable that tells you the 1st row that it was not found.

July 1, 2016 at 11:49 am

Thanks for you help but as I say I'm a beginner. I have 2 issues with that from my limited understanding, I don't know what any of the text will be so is there anything for any text? Secondly if there's a way to do that how do fit it into my script as it stands now

$Workbook = $Excel.Workbooks.Open("a.xlsx")
$WorkSheet = $WorkBook.Worksheets.Item("Live test  and training")
$WorkSheet.activate() 
$Range = $WorkSheet.Range("a5","q24")
$Range.Copy()
$Worksheet = $Workbook.Worksheets.item(2)
$Range = $Worksheet.Range("a1")
$Worksheet.Paste($range)

July 2, 2016 at 2:15 am

Will you always extract every row starting at row 5?
Will the number of columns stay the same or will it change?

I am going to give you an example assuming you will take any row with data in column A and that the data will always be in columns A-Q

$Workbook = $Excel.Workbooks.Open("a.xlsx")
$WorkSheet = $WorkBook.Worksheets.Item("Live test  and training")
$WorkSheet.activate()
$row = 5
Remove-Variable endofrange # this is here for testing you should not need this in the final script
do{
  If($Workbook.ActiveSheet.Cells.Item($row,1).Text -eq ""){$endofrange = $row}
  $row++
}until($endofrange)
$endofrange = $endofrange - 1
$Range = $WorkSheet.Range("a5","q$endofrange")
$Range.Copy()
$Worksheet = $Workbook.Worksheets.item(2)
$Range = $Worksheet.Range("a1")
$Worksheet.Paste($range)