How Can I Build an Array from a Column of Data in Excel?

ScriptingGuy1

Hey, Scripting Guy! How can I build an array from a column of data in Excel?

— AH

Hey, AH. Obviously none of the Scripting Guys has any need to go on a diet (perish the thought!), but we do know that one time-honored dieting strategy is the theory of tiny bites: instead of gulping down your food in a few huge swallows, you nibble at it with scores of tiny little bites, the idea being that the more bites you take the more your body will assume that it’s full. To tell you the truth, we have no idea whether the tiny bites strategy works for dieting; however, it works quite well for building an array from a column of data in Excel.

So what is the tiny bites strategy in this case? Well, we don’t know of any way to grab all the data in a column and, with a single line of code, magically turn it into an array. But that’s OK, because it’s just as easy to grab a value from a cell, store that value in an array, grab the value in the next cell, store that value in the array, and so on. In other words, we build our array by taking tiny bites of data from the spreadsheet, like this:

Dim arrExcelValues()

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
objExcel.Visible = True
i = 1
x = 0
Do Until objExcel.Cells(i, 1).Value = “”
    ReDim Preserve arrExcelValues(x)
    arrExcelValues(x) = objExcel.Cells(i, 1).Value
    i = i + 1
    x = x + 1
Loop
objExcel.Quit
For Each strItem in arrExcelValues
    Wscript.Echo strItem
Next

Before walking through the code, we should note that we’re assuming you have a spreadsheet in which the data to be retrieved is in column A; in addition, we’re assuming that the data starts in cell A1 and continues downward, with no blanks cells until you reach the end of the data. In other words, a spreadsheet that looks like this:

If your spreadsheet doesn’t look like this (for example, if it has a header row or if it includes blank cells), then you’ll have to adjust the code accordingly.

Now, what about that code. Well, we begin by defining a dynamic array named arrExcelValues; as you might expect, this is the array that we’ll populate using the values grabbed from the spreadsheet:

Dim arrExcelValues()

We then create an instance of the Excel.Application object, use the Open method to open the file C:\Scripts\Test.xls, and then set the Visible property to True (enabling us to see our spreadsheet on screen).

Next we assign values to a pair of counter variables, i and x:

i = 1
x = 0

We’re going to use i to keep track of our position in the spreadsheet; because our data starts in row 1 we set the value to 1. Meanwhile, we’re going to use x to track our position within the array arrExcelValues. Because the first item in an array is always item 0, we set the starting value of x to 0.

Next we set up a Do Until loop that will continue looping until it encounters a blank cell in column A; that line of code looks like this:

Do Until objExcel.Cells(i, 1).Value = “”

Inside that loop, we use the ReDim Preserve method to resize the array arrExcelValues (and to preserve any data currently in that array). We pass ReDim Preserve the value x, which represents the size of the array. We then set the value of the current item in that array to the value of cell A1 (row 1, column 1) in the spreadsheet:

ReDim Preserve arrExcelValues(x)
arrExcelValues(x) = objExcel.Cells(i, 1).Value

In other words, the first time through the loop, we grab the value from cell row 1, column 1 and store it as array item 0. Make sense? We then increment the values of our two variables by one each:

i = i + 1
x = x + 1

Why? Well, the second time through the loop i will be equal to 2; that means we’ll be grabbing the value from row 2, column 1. Likewise, the value of x will be 1, which means we’ll be assigning a value to item 1 in our array (because the first item in an array is item 0, the second item in the array has to be item 1.) We then increment the variables, loop around, and repeat the process. This continues until we encounter a blank cell in column A.

Once we encounter a blank cell we call the Quit method to dismiss our instance of Excel, and then use a simple For Each loop to echo back the items in arrExcelValues, something we do just to show you that we really did build an array using the values found in the spreadsheet:

For Each strItem in arrExcelValues
    Wscript.Echo strItem
Next

Quick, easy, and guaranteed non-fattening. What more could you ask for?

ScriptingGuy1

Follow