Below we will look at a program in Excel VBA that writes an Excel range to a CSV (comma-separated-value) text file.
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.
Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer
2. We need to specify the full path and the filename of the file.
myFile = Application.DefaultFilePath & “sales.csv”
Note: the DefaultFilePath property returns the default file path. The path to the folder you see when you open or save a file.
3. We initialize the range object rng with the selected range.
Set rng = Selection
4. Add the following code line:
Open myFile For Output As #1
Note: this statement allows the file to be written to. 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.
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
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.
cellValue = rng.Cells(i, j).Value
7. Add the following code lines to write the value of cellValue to the text file.
If j = rng.Columns.Count Then
Write #1, cellValue
Write #1, cellValue,
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.