{"id":68763,"date":"2005-10-12T17:55:00","date_gmt":"2005-10-12T17:55:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/10\/12\/how-can-i-build-an-array-from-a-column-of-data-in-excel\/"},"modified":"2005-10-12T17:55:00","modified_gmt":"2005-10-12T17:55:00","slug":"how-can-i-build-an-array-from-a-column-of-data-in-excel","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-build-an-array-from-a-column-of-data-in-excel\/","title":{"rendered":"How Can I Build an Array from a Column of Data in Excel?"},"content":{"rendered":"

\"Hey, \n

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

— AH<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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\u2019s 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.<\/P>\n

So what is<\/I> the tiny bites strategy in this case? Well, we don\u2019t 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\u2019s OK, because it\u2019s 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<\/I> 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: <\/P>

Dim arrExcelValues()<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nobjExcel.Visible = True<\/p>\n
i = 1\nx = 0<\/p>\n
Do Until objExcel.Cells(i, 1).Value = “”\n    ReDim Preserve arrExcelValues(x)\n    arrExcelValues(x) = objExcel.Cells(i, 1).Value\n    i = i + 1\n    x = x + 1\nLoop<\/p>\n
objExcel.Quit<\/p>\n
For Each strItem in arrExcelValues\n    Wscript.Echo strItem\nNext\n<\/PRE>\n
Before walking through the code, we should note that we\u2019re assuming you have a spreadsheet in which the data to be retrieved is in column A; in addition, we\u2019re 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:<\/P>\"Microsoft \n

If your spreadsheet doesn\u2019t look like this (for example, if it has a header row or if it includes blank cells), then you\u2019ll have to adjust the code accordingly.<\/P>\n
Now, what about<\/I> that code. Well, we begin by defining a dynamic array named arrExcelValues; as you might expect, this is the array that we\u2019ll populate using the values grabbed from the spreadsheet:<\/P>
Dim arrExcelValues()\n<\/PRE>\n
We then create an instance of the Excel.Application<\/B> object, use the Open<\/B> method to open the file C:\\Scripts\\Test.xls, and then set the Visible<\/B> property to True (enabling us to see our spreadsheet on screen).<\/P>\n
Next we assign values to a pair of counter variables, i<\/I> and x<\/I>:<\/P>
i = 1\nx = 0\n<\/PRE>\n
We\u2019re 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\u2019re 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.<\/P>\n
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:<\/P>
Do Until objExcel.Cells(i, 1).Value = “”\n<\/PRE>\n
Inside that loop, we use the ReDim Preserve<\/B> 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:<\/P>
ReDim Preserve arrExcelValues(x)\narrExcelValues(x) = objExcel.Cells(i, 1).Value\n<\/PRE>\n
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:<\/P>
i = i + 1\nx = x + 1\n<\/PRE>\n
Why? Well, the second time through the loop i will be equal to 2; that means we\u2019ll be grabbing the value from row 2<\/I>, column 1. Likewise, the value of x will be 1, which means we\u2019ll 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.<\/P>\n
Once we encounter a blank cell we call the Quit<\/B> 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<\/I> build an array using the values found in the spreadsheet:<\/P>
For Each strItem in arrExcelValues\n    Wscript.Echo strItem\nNext\n<\/PRE>\n
Quick, easy, and guaranteed non-fattening. What more could you ask for?<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[18,48,49,3,4,5],"class_list":["post-68763","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-arrays-hash-tables-and-dictionary-objects","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-scripting-techniques","tag-vbscript"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68763","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=68763"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68763\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=68763"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=68763"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=68763"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}