Write Data to Text File using Excel VBA

Below we will look at a program in Excel VBA that writes an Excel range to a CSV (comma-separated-values) text file.

Situation:

Place a command button on your worksheet and add the following code lines:

1. First, we declare a variable called myFile of type String, an object called rng of type Range, a variable called cellValue of type Variant, a variable called i of type Integer, and a variable called j of type Integer. We use a Variant variable here because a Variant variable can hold any type of value.

2. We need to specify the full path and the filename of the file.

Note: the DefaultFilePath property returns the default file path. To change the default file location, on the File tab, click Options, Save.

3. We initialize the range object rng with the selected range.

4. Add the following code line:

Note: this statement enables writing to the file. We can refer to the file as #1 during the rest of our code. If the file already exists, it will be deleted and a new file with the same name will be created.

5. Start a Double Loop.

Note: rng.Rows.Count returns the number of rows (17 in this example) and rng.Columns.Count returns the number of columns (4 in this example).

6. Excel VBA writes the value of a cell to the variable cellValue.

7. Add the following code lines to write the value of cellValue to the text file.

Explanation: due to the If Then Else statement, Excel VBA only starts a new line (Write #1, cellValue) when j equals the number of columns (last value in a row). To separate the values with a comma, use Write #1, cellValue, (with a comma).

8. Don't forget to close both loops.

9. Close the file.

10. Select the data and click the command button on the sheet.

Result:

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Data Analysis in Excel and Excel VBA.