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’re like most people, any time you hear the words “Scripting Guys” 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 if any of us had the guts to actually eat 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.

Why do we mention that? Simply because it seems 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’t admit that there’s something about scripting we don’t 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’s just that, instead of brute force, we’re calling it “rustic and hand-crafted.” With any luck, no one will be the wiser.

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:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

i = 1
Do While True
    strValue = objWorksheet.Cells(i,3)
    If strValue = “” Then
        Exit Do
    End If
    strText = strText & strValue & vbCrLf
    i = i + 1
Loop
objExcel.Quit
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.CreateTextFile(“C:\Scripts\ExcelData.txt”)
objFile.Write strText
objFile.Close

When you run this script two things happen: you’ll grab all the data found in column C of the spreadsheet Test.xls, and then you’ll save that data into a text file named ExcelData.txt. It’s not quite copying and pasting, but the net result is the same, and no one will ever be the wiser here, either.

Well, unless they read this column. So just don’t let anyone else read this column and you’re home free.

If anyone is 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:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

Once we get through with that we’re ready to get down to business.

For starters, we assign the value 1 to a counter variable named i. We’re 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’s no header row). What if your data starts in, say, row 3? No problem: just assign i the value 3.

In turn, that brings us to the following Do While loop:

Do While True
    strValue = objWorksheet.Cells(i,3)
    If strValue = “” Then
        Exit Do
    End If
    strText = strText & strValue & vbCrLf
    i = i + 1
Loop

What we’re 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:

strValue = objWorksheet.Cells(i,3)

If strValue is equal to an empty string (“”) we assume we’ve reached the end of the data; with that in mind, we use the Exit Do command to exit the loop. That’s what these three lines of code are for:

If strValue = “” Then
    Exit Do
End If

But wait, you say: what if the value of the cell is not equal to an empty string? Well, in that case we add the value plus a carriage return-linefeed (vbCrLf) to a string named strText:

strText = strText & strValue & vbCrLf

All we’re 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 the new value and a carriage return-linefeed.)

After we’ve run through all the cells in column C we use the Quit method to close Excel. That brings us to the halfway point: we’ve retrieved all the data from the designated column in Excel. Now we just have to get that data into a text file.

And while there are some crazy and somewhat-convoluted ways that we might be able to paste the data into a text file it seemed much easier to just save the data as a text file. That’s what this code is for:

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.CreateTextFile(“C:\Scripts\ExcelData.txt”)

objFile.Write strText
objFile.Close

Here we’re creating a new file named C:\Scripts\ExcelData.txt and then using the Write 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’d use code like this:

Const ForAppending = 8

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objTextFile = objFSO.OpenTextFile(“C:\Scripts\ExcelData.txt”, ForAppending, True)
objFile.Write strText
objFile.Close

And there you have it: a brand-new script carefully hand-crafted by the Scripting Guys, the people who don’t believe in the cold, impersonal style of mass-produced scripts.

Well, unless you mass produce them using the Scriptomatic, of course. But that’s different.