vba - Write each Excel row to new .txt file with ColumnA as file name

Question

I would be extremely grateful for any help as I have just begun to look into writing Excel macros.

I have excel documents with about 1,500 rows and variable column lengths, from 16-18. I would like to write each row of the file to a new .txt file (actually, I would REALLY like to write it as a .pdf but I don't think that's possible) where the name of the file is the corresponding first column. Additionally, I would like each row to be separated by a new line. So, ideally, the macro would 1) export each row as a new .txt file (or .pdf if possible), 2) name each file as ColumnA, 3) the content of each new .txt file would contain ColumnsB-length of total columns 4) each column is separated by a new line.

For example, if the document looks like this:

column 1//column 2// column3

a//a1//a2

b//b1//b2

I want it to output to be 2 files, named "a", "b". As an example, the contents of file "a" would be:

a1

a2

I have found 2 other stack overflow threads addressing separate pieces of my question, but I am at a loss as to how to stitch them together.

Each row to new .txt file, with a newline between each column (but file name not ColumnA): Create text Files from every row in an Excel spreadsheet

Only one column incorporated into file, but file names correspond with ColumnA: Outputting Excel rows to a series of text files

Thank you for any help!

Answer 1

To get the contents to be columns B thru the end of the file, you could do something like this.

Create a simple loop over the cells in Column B. This defines a range of columns for each row, and also sets a filename based on the value in column A.

Sub LoopOverColumnB()

Dim filePath as String
Dim fileName as String
Dim rowRange as Range
Dim cell as Range

filePath = "C:Test" '<--- Modify this for your needs.

For each cell in Range("B1",Range("B1048576").End(xlUp))
   Set rowRange = Range(cell.address,Range(cell.address).End(xlToRight))

   fileName = filePath & cell.Offset(0,-1).Value

   '
   ' Insert code to write the text file here 
   '
   ' you will be able to use the variable "fileName" when exporting the file
Next
End Sub