Convert each row of a spreadsheet into a .txt or .xml file?

[nickf829]

I have a spreadsheet with thousands of lines of a very small code. Each row contains a complete code that needs to either be converted/pasted into a new .txt or .xml document. The name of the new doc is irrelevant, but if it's possible to control the naming convention that would help(i.e. another cell with a suggested name). This will result in thousands of small .txt/.xml files. Until now, just copying and pasting each line into a .txt file was necessary but there has to be a way to simplify this. I would love to know if it's possible to extract each row and create or add it it to a new .txt or .xml file?

Thanks!

 

[Smitty]

Welcome to the Board!

Try recording a macro copying one row to a new workbook, then saving that as a .txt file, then post back the resulting code. From there someone can help to automate the rest of it.

The new document name can be added in at that time as well, provided you give the parameters.

 

[nickf829]

Thanks for the quick response.

I recorded a macro selecting the first column/row, pasting it into a new worksheet, saving it as the default excel filename then deleting the selected row/column(a1) and shifting all cells up. Below is the code:

Sub Macro2()
'
' Macro2 Macro
'

'
Range("A1").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\Users\Nick\Documents\Book2.txt", _
FileFormat:=xlTextMSDOS, CreateBackup:=False
Windows("EXAMPLE.xlsx").Activate
Range("A1").Select
Selection.Delete Shift:=xlUp
End Sub


Is this what you needed?

 

[daverunt]

Hi,

this is based on the above posted code.

It saves the file name as the copied Row number i.e Row 1.txt, Row 2.txt etc.
You can change the 'Row' to some other name.

Sub File_save()
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each cell In Rng
Num = cell.Row
 
Range("A" & Num).EntireRow.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs FileName:="C:\Users\Nick\Documents\[COLOR=red]Row [/COLOR]" & Num & ".txt", _
FileFormat:=xlTextMSDOS, CreateBackup:=False
ActiveWorkbook.Close False
 
ThisWorkbook.Activate 
Range("A" & Num).EntireRow.Select
Selection.Delete Shift:=xlUp
Next
End Sub

 

[Cindy Ellis]

Here's another approach that creates each file directly as text. I don't know if it's faster or uses less memory...but it's a method I've used before to create text files from Excel data. As written, it uses the contents of column B in the same row as the basis for the filename, but if you just want numbered filenames, use the (currently) commented line below that.

Sub ExportTextFiles()

'
Dim i As Long
Dim LastDataRow As Long
Dim MyFile As String
Dim fnum


    LastDataRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LastDataRow
        'The next line uses the contents of column B on the same row to name it
        MyFile = "C:\Users\Nick\Documents\" & ActiveSheet.Range("B" & i).Value & ".txt"
        'Use the following line instead to just create a series of numbered files
        'MyFileName = "C:\Users\Nick\Documents\Book" & i & ".txt"
        fnum = FreeFile()
        Open MyFile For Output As fnum
        Print #fnum, Format(Range("A" & i))
        Close fnum
    Next i
   
End Sub

This leaves the original file intact...deleting the contents all at once could be a final step of the process if it's important to do that as part of the macro.
Hope this helps,

Cindy

 

[daverunt]

I read the post as code being in a row and I assumed complete row ...

'.EntireRow' needs to be removed from the code so it only copies column A content.

 

[sfazam]

this is based on the above posted code.


It saves the file name as the copied Row number i.e Row 1.txt, Row 2.txt etc.
You can change the 'Row' to some other name.

Sub File_save()
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each cell In Rng
Num = cell.Row
 
Range("A" & Num).EntireRow.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs FileName:="C:\Users\Nick\Documents\[COLOR=red]Row [/COLOR]" & Num & ".txt", _
FileFormat:=xlTextMSDOS, CreateBackup:=False
ActiveWorkbook.Close False
 
ThisWorkbook.Activate 
Range("A" & Num).EntireRow.Select
Selection.Delete Shift:=xlUp
Next
End Sub

Hi,
I am facing one problem with above mentioned code and need help from experts.

This code is generating HTML files in ODD order, means sometime only row 1, 3 & 5 are generating and also, if I put 60 rows then it is deleting around 30 rows. I am unable to understand why this is happening

I changed only one thing i.e. replaced txt by html.

 

[Rick Rothstein]

Can you clarify something for us... do you only has one cell per row with "code" in it? If the answer is no, can you show us an example of the content of Columns A thru C and then show us those three cell's content should look in the text file?

 

[sfazam]

Can you clarify something for us... do you only has one cell per row with "code" in it? If the answer is no, can you show us an example of the content of Columns A thru C and then show us those three cell's content should look in the text file?

I have around 10 cells in each row.

A1 =Name1, B1=Address1, C1= Phone Number1,,,,,,
A2 =Name2, B2=Address2, C2= Phone Number2,,,,,,

 

[Rick Rothstein]

I have around 10 cells in each row.

A1 =Name1, B1=Address1, C1= Phone Number1,,,,,,
A2 =Name2, B2=Address2, C2= Phone Number2,,,,,,

For the first one (highlighted in red)... what should it look like in the text file you want created for it? Like this maybe...

Name1
Address1
Phone Number1
etc.