CODEWITHSUNDEEP
vbaexcel
Heresh
Heresh

Reputation: 305

export text file excel

I am trying to make a commandbutton that exports my table to a tab separated text file and add some dynamic headers to it. I want the headers to say: 1/x 2/x 3/x 4/x and so forth where x being the total amount of columns

This is my code so far that I got from a website:

Private Sub CommandButton1_Click()

    Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As   Integer

    myFile = Application.DefaultFilePath & "\projekt.txt"

    Set rng = Selection

    Open myFile For Output As #1

    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count

            cellValue = rng.Cells(i, j).Value

            If j = rng.Columns.Count Then
                Write #1, cellValue
            Else
                Write #1, cellValue,
            End If

        Next j
    Next i

    Close #1
End Sub

The problem is this depends on me marking the filled cells for it to work, also it only produces comma separated text files, I want tab separated files without "". Also it doesnt produce headers.

1/5    2/5    3/5    4/5    5/5
x(total) rows
Data    Data    Data    Data    Data
Data    Data    Data    Data    Data
Data    Data    Data    Data    Data
Data    Data    Data    Data    Data

Upvotes: 1

Views: 940

Answers (1)

Rosetta
Rosetta

Reputation: 2725

It only outputs selected cells because in your code you have at line 4

Set rng = Selection

If you want to export always a specific range, example A1:E100, you can change this line to, 

Set rng = Range("A1:E100")

If this range include the headers, then your export will also have headers.

Second problem,

It is comma separated because you have 

Write #1, cellValue,

To make it tab demilited, change this line to

Write #1, cellValue & vbTab

this should be the complete code for the write section, vbNewline implies the "new line" character (or vbCrLf also can), vbTab is the tab character

If j = rng.Columns.Count Then
    Write #1, cellValue & vbNewLine
Else
    Write #1, cellValue & vbTab
End If

Upvotes: 2

Related Questions