Exporting Worksheets from a workbook and saving them as a .prn file

[Ayobami Adeloye]

i have over 200 worksheets with different names on a workbook. I would
like to move each worksheet to a separate workbook, and save the
workbook as a formatted text (space delimited) file i.e a .prn file
using the same name it had as a worksheet in the original workbook to
a any specified folder.

This way i would have 200 worksheets in the original workbook saved as
different .prn files in the specified folder all at the click of a
button.

Would really appreciate any help guys.

Thanks i anticipation

 

[Javed]

The follwing code will do.This macro will save the files in the
original workbook's folder.To change it edit the "Thisworkbook.Path"
portion with your path.

I must mention FileFormat of excel does not mention any thing like PRN
so I have used xlTextMSDOS.Hope this will fulfill your reqirement.

--------------------------------
Sub SheetSaveTextFile()

Dim sh As Worksheet, stt As String

Application.ScreenUpdating = False

For Each sh In ThisWorkbook.Worksheets
stt = sh.Name
sh.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "/" & stt &
".prn", FileFormat:=xlTextMSDOS
ActiveWorkbook.Close False
Next sh

Application.ScreenUpdating = True

End Sub