{"id":67473,"date":"2006-04-24T08:03:00","date_gmt":"2006-04-24T08:03:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/04\/24\/how-can-i-select-a-column-of-data-in-excel-and-then-paste-that-data-into-a-text-file\/"},"modified":"2006-04-24T08:03:00","modified_gmt":"2006-04-24T08:03:00","slug":"how-can-i-select-a-column-of-data-in-excel-and-then-paste-that-data-into-a-text-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-select-a-column-of-data-in-excel-and-then-paste-that-data-into-a-text-file\/","title":{"rendered":"How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I select a column of data in Excel and then paste that data into a text file?

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

Hey, RR. You know, if you\u2019re like most people, any time you hear the words \u201cScripting Guys\u201d you undoubtedly think of two things: elegance and sophistication. Despite our well-deserved reputation, however, the Scripting Guys are – at heart – simple guys with simple tastes. Sure, we like caviar as much as anybody (or we probably would<\/I> if any of us had the guts to actually eat<\/I> that stuff), but the bottom-line is that we just like to find a way to make things work. If what we do turns out to be simple and elegant, well, so much the better. But our focus is on practicality, not on appearances.<\/P>\n

Why do we mention that? Simply because it seems<\/I> like there ought to be a simple and elegant solution to your problem. However, we have no idea what that simple and elegant solution could be. But – heaven forbid! – the Scripting Guys can\u2019t admit that there\u2019s something about scripting we don\u2019t know. Therefore, rather than expose our deepest and darkest secrets, we decided to give you more of a brute force method of doing what you need to do. It\u2019s just that, instead of brute force, we\u2019re calling it \u201crustic and hand-crafted.\u201d With any luck, no one will be the wiser.<\/P>\n

In other words, rather than putting on airs, we humbly offer you this rustic and hand-crafted script, custom built for you and only you, RR:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\nobjWorksheet.Activate<\/p>\n
i = 1<\/p>\n
Do While True\n    strValue = objWorksheet.Cells(i,3)\n    If strValue = “” Then\n        Exit Do\n    End If\n    strText = strText & strValue & vbCrLf\n    i = i + 1\nLoop<\/p>\n
objExcel.Quit<\/p>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.CreateTextFile(“C:\\Scripts\\ExcelData.txt”)<\/p>\n
objFile.Write strText\nobjFile.Close\n<\/PRE>\n
When you run this script two things happen: you\u2019ll grab all the data found in column C of the spreadsheet Test.xls, and then you\u2019ll save that data into a text file named ExcelData.txt. It\u2019s not quite copying and pasting, but the net result is the same, and no one will ever be the wiser here, either.<\/P>\n
Well, unless they read this column. So just don\u2019t let anyone else read this column and you\u2019re home free.<\/P>\n
If anyone is<\/I> reading this column, the script starts out with a forbidding block of code that really does nothing more than create a visible instance of Excel, open the workbook C:\\Scripts\\Test.xls, and then make Sheet 1 the active worksheet:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\nobjWorksheet.Activate\n<\/PRE>\n
Once we get through with that we\u2019re ready to get down to business.<\/P>\n
For starters, we assign the value 1 to a counter variable named i. We\u2019re going to use this variable to keep track of the current row in the spreadsheet. We gave this variable the value 1 because the data in our practice spreadsheet starts in row 1 (that is, there\u2019s no header row). What if your data starts in, say, row 3? No problem: just assign i the value 3.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. This is a good time to mention that we\u2019re assuming that all the data in your spreadsheet is contiguous, which is just a fancy way of saying that there are no blank rows in column 3. If you do<\/I> have blank rows in column 3 you might need to use the UsedRange<\/B> property to determine the last row in the spreadsheet, and then use a For Next loop to loop from row 1 to row whatever-the-number-of-the-last-row-is. For more information on the UsedRange property see this Office Space article<\/B><\/A>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
In turn, that brings us to the following Do While loop:<\/P>
Do While True\n    strValue = objWorksheet.Cells(i,3)\n    If strValue = “” Then\n        Exit Do\n    End If\n    strText = strText & strValue & vbCrLf\n    i = i + 1\nLoop\n<\/PRE>\n
What we\u2019re doing here is reading row 1, column 3 of the spreadsheet, then reading row 2, column 3 of the spreadsheet, then row 3, column 3, etc. How do we know when to stop? Well, for each cell we use this line of code to store the value of that cell in a variable named strValue:<\/P>
strValue = objWorksheet.Cells(i,3)\n<\/PRE>\n
If strValue is equal to an empty string (\u201c\u201d) we assume we\u2019ve reached the end of the data; with that in mind, we use the Exit Do<\/B> command to exit the loop. That\u2019s what these three lines of code are for:<\/P>
If strValue = “” Then\n    Exit Do\nEnd If\n<\/PRE>\n
But wait, you say: what if the value of the cell is not<\/I> equal to an empty string? Well, in that case we add the value plus a carriage return-linefeed (vbCrLf) to a string named strText:<\/P>
strText = strText & strValue & vbCrLf\n<\/PRE>\n
All we\u2019re doing with strText is keeping track of the data we find in column C of the spreadsheet: each time we access a new cell we append that data to the value of strText. (Note that strText is always assigned the current value of strText plus<\/I> the new value and a carriage return-linefeed.)<\/P>\n
After we\u2019ve run through all the cells in column C we use the Quit<\/B> method to close Excel. That brings us to the halfway point: we\u2019ve retrieved all the data from the designated column in Excel. Now we just have to get that data into a text file.<\/P>\n
And while there are some crazy and somewhat-convoluted ways that we might<\/I> be able to paste the data into a text file it seemed much easier to just save the data as<\/I> a text file. That\u2019s what this code is for:<\/P>
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.CreateTextFile(“C:\\Scripts\\ExcelData.txt”)<\/p>\n
objFile.Write strText\nobjFile.Close\n<\/PRE>\n
Here we\u2019re creating a new file named C:\\Scripts\\ExcelData.txt and then using the Write<\/B> method to write the value of strText to that file. What if we wanted to add this data to an existing text file? No problem; in that case we\u2019d use code like this:<\/P>
Const ForAppending = 8<\/p>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objTextFile = objFSO.OpenTextFile(“C:\\Scripts\\ExcelData.txt”, ForAppending, True)<\/p>\n
objFile.Write strText\nobjFile.Close\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. For more information on writing to new text files or appending data to existing text files, take a look at this section<\/B><\/A> of the Microsoft Windows 2000 Scripting Guide<\/I>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
And there you have it: a brand-new script carefully hand-crafted by the Scripting Guys, the people who don\u2019t believe in the cold, impersonal style of mass-produced scripts.<\/P>\n
Well, unless you mass produce them using the Scriptomatic<\/B><\/A>, of course. But that\u2019s different.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I select a column of data in Excel and then paste that data into a text file?— GT Hey, RR. You know, if you\u2019re like most people, any time you hear the words \u201cScripting Guys\u201d you undoubtedly think of two things: elegance and sophistication. Despite our well-deserved reputation, however, the […]<\/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":[48,49,3,4,14,5],"class_list":["post-67473","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-scripting-techniques","tag-text-files","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I select a column of data in Excel and then paste that data into a text file?— GT Hey, RR. You know, if you\u2019re like most people, any time you hear the words \u201cScripting Guys\u201d you undoubtedly think of two things: elegance and sophistication. Despite our well-deserved reputation, however, the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67473","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=67473"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67473\/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=67473"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67473"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67473"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}