They say you learn something new every day.

Automating Excel (04/09/2012)

When I’m outputting text or data, I tend to output it into text files. They’re simple and clean. But people often want Excel files. Yeah, I don’t understand it either.

I sat down today and had a look at writing extra data to an Excel file, and found that adding data to the last line isn’t always simple.

set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(FILENAME)
 objExcel.Sheets(SHEETNAME).Select
LastRow = objExcel.ActiveSheet.UsedRange.Rows.Count
 objExcel.Cells(LastRow + 1, 1).Value = “Test value”
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

This function loads an Excel file, finds the first blank row and writes to it, then saves and closes the file. And does so, all invisibly to the user.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Tag Cloud

%d bloggers like this: