VBA code to Save Rows from Excel to Notepad

Question

Hey everyone,

So I have seen some threads discussing similar items, but I am not experienced with VBA or macros so editing them to fit my needs is near impossible.

I frequently receive files of thousands of lines in Excel that need to be saved to their own individual text file (Notepad) to be converted into another program, not compatible with Excel. Each row has 8 columns with unique identifiers in column A and F that the file is name. Currently, I am copying and pasting each line manually and need to find a macro or something that will eliminate this manual process.

Also, each cell is formatted specifically so that the spacing should remain the same in the text file for its conversion in the another program.

Any ideas for a better way to do this.

Answer 1

Re: saving one row as a text file (notepad)

Try a test to see if you get what you want...

  Open a new workbook and paste one row of your data into the top row.

  In another workbook, run this code - Note the new workbook must be active (displayed).

'---

Sub SaveSheetAsTextFileTest()

  Dim strPath As String

  strPath = "C:\Excel Files\General\BookTest1"  '<<< REPLACE WITH CORRECT FILE PATH AND FILE NAME

  ActiveSheet.SaveAs Filename:=strPath, FileFormat:=xlTextMSDOS

  ActiveWorkbook.Close SaveChanges:=True

End Sub

'---

Open the saved file - is it what you need?

'---

Jim Cone

Answer 2

Jim,

Thanks for looking at this, but unfortunately I am receiving a "Run-time error '1004'". Any ideas?

Answer 3

The file is one sheet. So I am looking at least a couple hundred lines per file, or one worksheet. So if I have 500 lines I need 500 text files created, one per line keeping the same format on the excel file.

Answer 4

Re:  saving as text file

Are you saving each row as a text file or each sheet as a text file?

From excel you can save each sheet as a separate text file.

  4 flavors are offered: text, unicode, mac, dos

'---

Jim Cone

Portland, Oregon USA