export excel columns into multiple text files

Question

Input

Output

I need to split data [1...M] gathered in columns [A...N] into separate text files named as first cell of each column.

Data arranged in excel:

FileName_A  |FileName_B |FileName_C |…      |FileName_N
Data_A1     |Data_B1        |Data_C1        |…      |Data_N1
Data_A2     |Data_B2        |Data_C2        |…      |Data_N2
Data_A3     |Data_B3        |Data_C3        |…      |Data_N3
…       …       …       …       …
DataA_AM    DataA_BM    DataA_CM    DataA_AM    DataA_NM
____________________________________________________________________________

shall be written into FileNames

FileName_A.tex should look like:

_____________
Data_A1
Data_A2
Data_A3
…
DataA_AM
____________

I´ve tried, but...

Altough it seems an easy task for an expert, it is quite a huge obsticle for me becouse I am not familiar with coding.

Thank You very much for support in advance.

You've tried what exactly? Can you please edit your question to let us know? — cybernetic.nomad
– cybernetic.nomad, Commented Jan 11, 2019 at 15:36
What is "M" ? The number of rows ? How many is that ? Do you want to manually enter them ? — Thryn
– Thryn, Commented Jan 11, 2019 at 15:47
I have measureing points in different columns [from 1 to N]. Labels ar written in first row. Below I have measuring data [row 2 to M] of each measuring point. I have to put measurements of each measuring point into single file. I want to label text file from measuring point A [cell A1] with FileName_A.tex, which includes data from [cell A2] to [cell A34] and [cell B1] FileName_B.tex with data from [cell B2] to [cell B34] ... — Yogi
– Yogi, Commented Jan 11, 2019 at 16:02
M is the number of rows between 10 to 50. They are entered manually, but i get files 3 times a day for additional editing. — Yogi
– Yogi, Commented Jan 11, 2019 at 16:07
You need to rewrite the question to ask the following: 1. Is it possible to export columns of data into text files? 2. If it is possible what method should I use, whether it be excel functionality or special coding functionality. I am certain you can do this based on excel functionality... no coding needed. — Christian Matthew
– Christian Matthew, Commented Jan 11, 2019 at 16:50

Thryn · Accepted Answer · 2019-01-11 16:06:21Z

1

So, I assumed that M is an Integer Variable that you already defined and N just the Column Name (So column number 14). The code would then be

Dim i As Integer
Dim LastRow As Integer

For i = 1 To 14

Range(Cells("2", i), Cells(M, i)).ExportAsFixedFormat _
    Type:=xlTypeXPS, _
    Filename:=Sheets(1).Cells("1", i), _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Next

You might want to replace Filename:=Sheets(1).Cells("1", i), _ with Filename:="C:/Your_Path/ & Sheets(1).Cells("1", i), _

answered Jan 11, 2019 at 16:06

Thryn

4553 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yogi Over a year ago

Thank You Thryn. It is my /if I can say so/ first VBA code. I will do my best. Have a nice day.

Thryn Over a year ago

We've all been there, don't worry :) If that answered your problem, don't forget to check the tick to mark the question as answered. If not, I might help you solve why.

Yogi Over a year ago

It does not work. Don´t know why. All data are strings, not numbers. I have 14 columns. First cell in row is FileName. Other cells are string data.

JUGAL SHAH · Accepted Answer · 2019-01-11 16:27:02Z

0

Try Code below.

    Sub export_data()
    Dim row, column, i, j As Integer
    Dim fullPath, myFile As String

    fullPath = "C:\Workspace"
    row = 21
    column = 5

    For i = 1 To column
        myFile = Cells(1, i).Value + ".txt"
        myFile = fullPath + "/" + myFile
        Open myFile For Output As #1
        For j = 2 To row
            Print #1, Cells(j, i).Value
        Next j
        Close #1
    Next i

    End Sub

You can change Row number and Column number. Your First row is always Header. See Image below for Excel

Excel Image

answered Jan 11, 2019 at 16:27

JUGAL SHAH

381 silver badge7 bronze badges

1 Comment

Yogi Over a year ago

JUGAL SHAH, You are genius. The code is clear and simple for illiterate. Thank You whereever you are.

Collectives™ on Stack Overflow

export excel columns into multiple text files

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related