Find Used Range in Excel Worksheet with Powershell.

Today's challenge was to open an excel file and then find the last used row and start adding information to it.
Here is how I chose to approach this. Please keep in mind this is building off the last posts and the variables set there. In this example I have not gone back and recreated each variable like I would if I were creating a complete script. This will not work unless you set these variables first.

First we need to open an existing file. We can do this by creating a path variable or we can just declare it directly. It might be handy to set a variable if you are going to make this a function or if you are going to call various excel files within one script.

$Excel.Workbooks.open("C:\Filepath\Filename.xlsx")

Next we are going to need to make the worksheet that we want to work in active
Again note this will not work unless you create the $worksheet1 variable. This has been shown in the previous post.

$WorkSheet1.Activate()

So far so good but now to see what we came here for. We have to get the last row in this worksheet

$LastRowUsed = $WorkSheet1.UsedRange.Rows.Count

However, we don't really want the last used row. If we choose to make this row active then we overwrite the existing data. I think we better add a row to that. Let's try something like this.

$LastRow = $WorkSheet1.UsedRange.Rows.Count + 1

Now that we have the correct row lets make it active

$Excel.Range("A" + $LastRow).Activate()

Now if you recall in our last script we were entering in data this way.

$WorkSheet1.Range("A1:A1").cells= "Cell a1)

However, I feel there is a better and cleaner way to write this that will make it easier for us to follow what we are doing when entering in variables for the row count. Lets try it this way.

$Worksheet1.Cells.Item($LastRow, 1).Value2 = "Look at this!"

I would like to point out that we can do the same with columns. If we run

$worksheet1.UsedRange | Get-Member
we can see that there is an option to use columns just like we did for rows. Pretty much the same thing really only we will now have to place the $LastColumn variable in the next position when entering in data. It would look something like this.

$Worksheet1.Cells.Item($LastRow, $LastColumn).Value2 = "Look at this!"

Today's sources are
http://stackoverflow.com/questions/8452408/using-powershell-to-append-a-table-to-the-end-of-an-excel-file-the-last-row
and to
http://social.msdn.microsoft.com/Forums/en-US/22b11766-6b33-451d-b43c-7596472bcd73/excel-read-using-powershell

Next time I will try and put something together on how to read a cell. Once I get done with all of this I will look into putting a series together on connecting with SQL. Oh the fun we can have with that!