SAHU
SAHU

Reputation: 15

Export specific columns of data from range to text file

I export Excel data to a text file. It works when I export all range data (ex. "B2:E" & p).

For some cases, I need only B & E column data.

Sub make_textfile()

'Variable declarations
Dim myFileName As String, rng As Range, cellVal As Variant, row As Integer, col As Integer
Dim inputNumber As Long
inputNumber = InputBox("Please enter number : " & vbCrLf & vbCrLf & "It may be 30 or 50 or your custom number! ", "Insert Data")
'Full path of the text file
myFileName = "C:\Users\Desktop\Text_1.txt"
'Data range need to write on text file
p = inputNumber + 1
Set rng = ActiveSheet.Range("B2:E" & p)
'Open text file
Open myFileName For Output As #1
'Number of Rows
For row = 1 To rng.Rows.Count
    'Number of Columns
    For col = 1 To rng.Columns.Count
   
    cellVal = rng.Cells(row, col).Value
    'write cellVal on text file
    If col = rng.Columns.Count Then
        Print #1, cellVal & vbNewLine & vbNewLine
    Else
        Print #1, cellVal & vbNewLine & vbNewLine
    End If
    Next col
Next row
Close #1
End Sub

Upvotes: 0

Views: 180

Answers (1)

Алексей Р
Алексей Р

Reputation: 7627

You can change the loop parameters by setting the columns to be processed:

For Each col In Array(1, rng.Columns.Count)

instead of For col = 1 To rng.Columns.Count

Since the variable in the for each loop must be a Variant for arrays, change the col As Integer declaration to col As Variant

Upvotes: 1

Related Questions