export text file excel

Question

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

Could you post what you want the output to look like? – Sep 02 '16 at 12:59 — , Sep 02 '16 at 12:59
I have added that, thanx for the input. – Heresh Sep 02 '16 at 13:32 — Heresh, Sep 02 '16 at 13:32

Rosetta · Accepted Answer · 2016-09-02T14:04:54.243

2

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

edited Sep 02 '16 at 14:04

answered Sep 02 '16 at 13:01

Rosetta

2,665
1
13
29

Thanks for the help, but I wonder is there anyway I dont need to specify the range, I mean that it only exports the columns and rows that are used, I mean if my table has 10 rows and 15 columns that it will only export those cells. – Heresh Sep 02 '16 at 13:24
@Heresh you certainly can. Try research this **[question](http://stackoverflow.com/questions/13686801/how-to-determine-the-last-row-used-in-vba-including-blank-spaces-in-between)** on how to determine last row. it works the same for column. – Rosetta Sep 02 '16 at 13:42
after having the last row, you can then write the code like `Set rng = Range("A1:E" & LastRow)` where `LastRow` is the variable assign with the last row value. – Rosetta Sep 02 '16 at 13:44
Thanks I surely will look into that and figure that one out. – Heresh Sep 02 '16 at 13:53
But your other comment on how to make it tab separated didnt work for me, I got every column on a new row instead. – Heresh Sep 02 '16 at 13:54
ya i see that now, thanks for pointing that out, pls see my updated answer – Rosetta Sep 02 '16 at 14:05
I still get the columns on different lines, not separated by tabs. – Heresh Sep 05 '16 at 06:41
i think each `Write` creates a new line, so perhaps need to change the code such that when you `Write` a `CellValue`, the Cellvalue is already the entire line string with vbTab as delimeter. i am some how busy lately, its not difficult to change the code, try urself first... i'll come back and update the answer – Rosetta Sep 07 '16 at 02:27

export text file excel

1 Answers1