how to export multiple sheets into txt files without the header line

[ankitaugust]

Hi Folks-

I am trying to figure out a simple way to export multiple sheets in my excel workbook without the header line. I was able to export the sheets to txt files but wasnt able to remove the header line. Here is the code that I am using. I do want to retain the header line in my sheets but not in the exported txt files.

Sub ExportSheetsToText()
Dim xWs As Worksheet
Dim xTextFile As String
Dim NewPath As String
NewPath = ThisWorkbook.Path & "\New"

MkDir NewPath

ErrTrap:
On Error Resume Next

For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Copy
xTextFile = NewPath & "\" & xWs.Name & ".txt"
Application.ActiveWorkbook.SaveAs Filename:=xTextFile, FileFormat:=xlText
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next

End Sub

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ankitaugust]

Hi Fluff--

Just to let you know I added the following lines to the code and it resolved the issue
Dim xList as ListObjects
.
.
.
Added below lines after the original For loop to convert the table into normal range
For Each xList In xWs.ListObjects
xList.Unlist
Next

 

[Fluff]

Thanks for letting us know.