Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Appending Data to an Excel Spreadsheet

As you doubtless know people are very good at giving you advice without necessarily offering suggestions on how you can act on that advice:

“You need to start saving for retirement.”

“You need to find a better job.”

“You need to do something besides watch Baseball Tonight three times a night.”

If truth be told, the Scripting Guys are no better than anyone else. (And, sadly, that is true: we’ve looked all over the place and we have yet to find a single person we’re better than.) For example, we keep telling you over and over again that you ought to use Microsoft Excel (instead of text files) as a reporting tool. And that’s great as long as you want to create a new spreadsheet each and every time you run a script. “But,” you ask, “what if I want to use the same spreadsheet day after day, simply appending new data to the existing data? What if I want to do that, Scripting Guys? Uh, Scripting Guys? Hello; is anyone there?”

OK, so maybe we’ve been a bit remiss in our duties from time-to-time. But no more: in today’s column we’ll show you how you can append data to an existing spreadsheet. And if there’s enough time, we’ll tell you how to start saving for retirement, too.

Of course, we’ll be explaining how you can save for our retirement, but….

Before you can append data to a spreadsheet you need to know where the existing data leaves off; otherwise you run the very real risk of overwriting some of that existing data. We’re going to show you two different ways to determine where the existing data ends and – by extension – where the new data should begin.

Let’s begin with the simplest possible case, a spreadsheet that looks something like this:

As you can see, we have data in columns A, B, and C. We’re going to assume that column A represents mandatory data that must be present in every row; in other words, you can’t write a new row without putting a value into column A. We’re also going to assume that no blank rows are allowed; in the preceding example, the next line of data will be written in row 6. This makes it very easy to determine where to start writing new data: all we have to do is identify the bottom-most row that contains a value in column A. Because all cells in column A must have a value we know that the very next row is the row where new data should be appended.

Make sense? If not, take a look at the picture. Row 5 is the very last row that contains a value in column A; because blank rows are not allowed and because column A must contain a value, we have no choice but to write the next bit of data in row 6.

Of course, that’s easy to figure out when we’re actually looking at the spreadsheet. But how can we do this programmatically??

Here’s one way. As you might know, it’s easy to get to the bottom of a column of data when you’re in Excel: just position the cursor in a cell in the column (for example, cell A1), hold down the Ctrl key, and then press the Down Arrow key. In our preceding example, that jumps the cursor down to cell A5. We can then press the Down Arrow key by itself and move the cursor to the first blank row. Assuming you know where the Ctrl and Down Arrows keys are, you can locate the first available blank row blindfolded.

And, yes, that would be a very cool magic trick to show all your friends.

We’re going to do the same thing in our script. Here’s a script that moves the cursor to the first available blank row; we’ll explain how it works in a minute:

Const xlDown = -4121

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = 1
objWorksheet.Cells(2,1) = 2
objWorksheet.Cells(3,1) = 3
objWorksheet.Cells(4,1) = 4
objWorksheet.Cells(5,1) = 5

Set objRange = objExcel.Range("A1")
objRange.End(xlDown).Activate

intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" &  intNewRow
objExcel.Range(strNewCell).Activate

OK. We begin by defining a constant named xlDown and setting the value to -4121; we’ll use this constant later on to represent Ctrl+Down Arrow. We then run through a bunch of code that simply creates a visible instance of Excel, adds a new workbook and worksheet, and then writes some data in cells A1 through A5. That’s what this is all about:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = 1
objWorksheet.Cells(2,1) = 2
objWorksheet.Cells(3,1) = 3
objWorksheet.Cells(4,1) = 4
objWorksheet.Cells(5,1) = 5

Now we’re ready for some real scripting. First we create a range consisting solely of cell A1:

Set objRange = objExcel.Range("A1")

Why do we do that? Well, in just a second we’re going to mimic the behavior of Ctrl+Down Arrow; for that to work, however, we need to start in cell A1. And so we create a little miniature range consisting of that one cell.

Next we actually do mimic the behavior of Ctrl+Down Arrow, something that requires just one line of code:

objRange.End(xlDown).Activate

As you can see, we use the End property and the Activate method. To move to the correct cell we set the value of End to the constant xlDown; that simply tells Excel that we want to go to the “end” of the column. (Technically we’re just moving down the column until we encounter a blank cell.) After making the move we then call the Activate method to make that cell the active cell.

This positions the cursor in cell A5; based on our spreadsheet design we know that the next cell – cell A6 – is the first available blank cell. So how do we move the cursor to cell A6?

To tell you the truth there are a couple different ways we could do this. For better or worse we chose this method, which we think makes it a little bit easier for you to keep track of where the cursor is:

intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" &  intNewRow
objExcel.Range(strNewCell).Activate

What we do here is assign a value to a variable named intNewRow; the value we assign will be the row number of the active cell (row 5) plus 1. That’s because we don’t want to be in the current row, we want to be down in the next row.

We then assign a value to another new variable, strNewCell; this variable gets assigned the letter A plus the value of intNewRow (6). In other words, strNewCell will be assigned the value A6, which just happens to be the next available blank cell. To move the cursor to this new cell we create a range consisting of this one cell and call the Activate method. Our cursor is now in a blank cell, we know the address of this cell (A6), and we can now start adding new data to the spreadsheet.

If you’re looking for a simple example of adding new data here’s a script you can try. This one just adds some numbers to the end of our original spreadsheet. Note that in this sample script we don’t even bother changing the active cell each time we add a new row; instead we simply add the data to the spreadsheet:

Const xlDown = -4121

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = 1
objWorksheet.Cells(2,1) = 2
objWorksheet.Cells(3,1) = 3
objWorksheet.Cells(4,1) = 4
objWorksheet.Cells(5,1) = 5

Set objRange = objExcel.Range("A1")
objRange.End(xlDown).Activate

intNewRow = objExcel.ActiveCell.Row + 1

For i = 1 to 10
    objWorksheet.Cells(intNewRow, 1) = i * 100
    intNewRow = intNewRow + 1
Next

The preceding approach works great with spreadsheets that don’t have blank rows. But what if you have a spreadsheet where blank rows are allowed? For example, what if you have a spreadsheet that looks like this:

Now we have a problem. We can’t do the little down arrow trick here. Why? Well, suppose we position the cursor in cell A1 and try the previous script; in that case the cursor would jump only to cell A4; that’s because the first blank cell in column A occurs in row 4. That’s not the end of the data, but our script doesn’t know that. After all, it’s looking for the first blank cell, which – as this spreadsheet demonstrates – doesn’t always mark the end of the data. If we use our first script with this spreadsheet we’ll start appending data in cell A4, and eventually overwrite the existing data in rows 5 and 6.

Yes, that definitely is a problem. But don’t worry; there’s a way to deal with this issue. Excel’s Range object includes a property named UsedRange that encompasses all the cells that have a value. In our sample spreadsheet, the UsedRange looks like this:

This is also the answer to our problem. All we have to do is determine the bottom row in the used range; the next available row for data entry will then be the row immediately following the used range. That’s exactly how this script operates:

Const xlCellTypeLastCell = 11

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = 1
objWorksheet.Cells(2,1) = 2
objWorksheet.Cells(3,1) = 3
objWorksheet.Cells(5,1) = 4
objWorksheet.Cells(6,1) = 5

Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate

intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" &  intNewRow
objExcel.Range(strNewCell).Activate

The script begins by defining a constant named xlCellTypeLastCell and setting the value to 11; we’ll use this in conjunction with the SpecialCells method to move the cursor to the last cell in our range. We then create a sample spreadsheet, one that looks like this (notice that cell A4 is blank):

Next we create a range (based on the UsedRange property) and use the SpecialCells method to jump to the very last cell in that range (in this case, cell A6):

Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate

We then move the cursor to the next available cell (cell A7) and, again, we’re ready to start adding data.

So there you have it: now you can append data to a spreadsheet. In return, you have to stop bugging us about watching Baseball Tonight so often. Deal? Deal.

Special bonus code. After making a cell the active cell you can retrieve the cell address, row number, and column number using code similar to this:

Wscript.Echo objExcel.ActiveCell.Address
Wscript.Echo objExcel.ActiveCell.Row
Wscript.Echo objExcel.ActiveCell.Column