How can I export data from an excel sheet to create a text file of cell data arranged into a specific format?

Question

I'm not overly familiar with Excel. I am wondering if there is a way that I could take a spreadsheet and generate a new text file from its data with a specific text formatting. What I have is a sheet with 200+ country names in the rows. For each row, there are 5 columns each containing a hyperlink. What I'm looking to create is a grouping for each row which is formatted like this:

rowName (Country 1)
column2Title|row1Column2Link
column3Title|row1Column3Link
column4Title|row1Column4Link
column5Title|row1Column5Link

rowName (Country 2)
column2Title|row2Column2Link
column3Title|row2Column3Link
column4Title|row2Column4Link
column5Title|row2Column5Link

and etc, about 200 of these total. As shown, on the left of every vertical bar is the same exact text in each grouping, just the respective column titles. On the right is what exists in each column of the current row. I'm loosely familiar with VBA but I would be able to program this myself without some explanation.

xificurC xificurC · Accepted Answer · 2014-05-27T06:36:08

Assuming your data starts at A1, the first row is the headers and the first column with your countries has no blanks this should work (or at least give you the gist):

Sub write_stuff()
    Dim iFile As Integer
    Dim sFile As String
    Dim rLoop As Range, r As Range

    sFile = "C:\mytxtfile.txt"
    iFile = FreeFile
    Open sFile For Output As iFile
    For Each rLoop In Range("A2", Range("A1").End(xlDown))
        Print #iFile, rLoop
        For Each r In Range("B1", Range("A1").End(xlToRight))
            Print #iFile, r & "|" & Cells(rLoop.Row, r.Column)
        Next r
        Print #iFile, ""
    Next rLoop

    Close #iFile
    Set rLoop = Nothing
    Set r = Nothing
End Sub

How can I export data from an excel sheet to create a text file of cell data arranged into a specific format?

1 Answers