Home and Learn - Free Excel VBA Course

8.2 Writing to text files in Excel VBA

In the previous lesson, you saw how to open up a text file with Excel VBA code. In this lesson, you'll learn how to write data from a spreadsheet to a text file. What we'll do is take our reordered columns from last time and write them back to a CSV file.

The first job is to find a way to reference the cells on the spreadsheet that we want. We can then loop round all these cells getting the values.

Our spreadsheet is 3 columns wide by 8 rows high:

A spreadsheet showing a CSV file opened with VBA code

We could have two loops to cycle through the data above, an inner loop and outer one. Like this:

For i = 1 To 8

For j = 1 To 3

Next j

Next i

The outer loop goes from 1 to 8. This is the number of rows we have. The inner loop goes from 1 to 3, which is the number columns.

However, suppose we decided to add more rows, or more columns to the spreadsheet. It would mean our loop would not pick up the new data. A better way is to get the last row with data in it and the last column with data. We could then have this for the loops:

For i = 1 To LastRow

For j = 1 To LastColumn

Next j

Next i

The question is, how do we get these values?

There are lots of way to get the last row and last column with data. A popular way to get the last row with data in, for example, is this:

LastRow = Cells(1, "A").End(xlDown).Row

This code first goes to the very last row in Excel, no matter which version you have (older versions of Excel have fewer rows than later versions). It then goes back up to find the last cell in column A that has something in it. A similar technique is used to find the last column with data.

The technique we prefer, though, is this (all on one line, and no space after UsedRange, as there is here):

LastRow = ActiveSheet.UsedRange. SpecialCells(xlCellTypeLastCell).Row

This refers to any range in your active worksheet with data in it. The inbuilt SpecialCells is then used. In between the round brackets of special SpecialCells is the constant xlCellTypeLastCell. This gets you the last cell with data. After a dot, you can type either Row or Column, depending on which last cell you want.

We're almost ready to start writing the code. To actually open a text file for writing, though, you need this:

Open FilePath For Output As #1

This is the same as before, except for the use of the keyword Output. VBA will then try to open up your file. If a file of the one named in FilePath doesn't exist it will be created for you. If the file does exist then it will be overwritten. (If you want the new contents to be added to the end of your file then you would use Append instead of Output.)

To do the actual writing to the file you need this:

Write #1, File_Contents

After the word Write you type your file number. After a comma, you need the contents you wish to write to the file.

With that in mind, let's write some code.

Create a new Sub and call it WriteTextFile. Add the following four variables to your new Sub:

Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

To get the last row and column with data, add the following two lines (no space after UsedRange):

LastCol = ActiveSheet.UsedRange. SpecialCells(xlCellTypeLastCell).Column

LastRow = ActiveSheet.UsedRange. SpecialCells(xlCellTypeLastCell).Row

You then need a file path, as before:

FilePath = Application.DefaultFilePath & "\auth.csv"

This points to a file called auth.csv in the Documents folder. If there is no such file then VBA will create one for us.

The next line to add is the one that opens the file for Output:

Open FilePath For Output As #2

Notice that the file number at the end, #2. We've already used #1 previously, so we'll try #2 to avoid any conflicts. (NOTE: On some systems, you may get an error telling you the file is already open when you run the code. If so, close down Excel and reopen it.)

The next code to add is the double For Loop. This is quite complex so don't worry if you don't understand it at first. Keep studying it and it will make sense. Here it is:

For i = 1 To LastRow

For j = 1 To LastCol

If j = LastCol Then

CellData = CellData + Trim(ActiveCell(i, j).Value)

Else

CellData = CellData + Trim(ActiveCell(i, j).Value) + ","

End If

Next j

Write #2, CellData
CellData = ""

Next i

As we said, we're looping round cells on the spreadsheet. The outer loop takes care of the rows and the inner loop takes care of the column.

Inside the inner loop, we have this If Statement:

If j = LastCol Then

CellData = CellData + Trim(ActiveCell(i, j).Value)

Else

CellData = CellData + Trim(ActiveCell(i, j).Value) + ","

End If

The reason why we want to know if j = LastCol is because of the commas. We want each line in our text file to look like this, remember:

9780349114903, Brookmyre, Christopher

We're getting one cell at a time from the spreadsheet. Each of the items needs to be separated by a comma. However, there's no comma at the end, which is the third item. If j does equal LastCol then this gets executed:

CellData = CellData + Trim(ActiveCell(i, j).Value)

Whatever is in the ActiveCell i, j will have its Value placed into the variable called CellData.

However, if j does not equal LastRow then this gets executed instead:

CellData = CellData + Trim(ActiveCell(i, j).Value) + ", "

The only difference is the comma on the end. The first time round the inner loop, CellData will hold this value:

9780349114903,

The next time round it will hold this:

9780349114903, Brookmyre,

And the final time round it will hold this value:

9780349114903, Brookmyre, Christopher

Outside of the inner loop, but just before the Next i of the outer loop, we have these two lines:

Write #2, CellData
CellData = ""

The first line is the one that actually writes the new line to the text file. The second line resets the CellData variable to a blank string.

The final two lines of code are these, both outside of the two loops, at the end:

Close #2
MsgBox ("Done")

These lines close the file and then display a message box.

The whole of the code looks like this:

Excel VBA code to write data to a CSV file

Run your code and try it out. Now locate your new text file. You should find that it looks like this: (Don't worry about the double quotes that VBA has added to the beginning and end of each line. If you don't want the quote marks then you can use Print instead of Write in the code above.)

A CSV file with data written to in Excel VBA

 

In the next section, you'll make a start learning about User Forms.

< Open File

Next Lesson: 9.1 User Forms >