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

ankitaugust

New Member
Joined
Jul 4, 2020
Messages
12
Office Version
  1. 365
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi & welcome to MrExcel.
How about
VBA Code:
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"
ActiveSheet.Rows(1).Delete
Application.ActiveWorkbook.SaveAs FileName:=xTextFile, FileFormat:=xlText
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next

End Sub
 
Upvote 0
Thanks for the response Fluff. I am getting the "runtime error '1004'. Delete Method of range class failed".
Tried with Application.ActiveWorkbook.Rows(1).Delete too but getting an error with that also
 
Upvote 0
Did you get that error with the code exactly as I posted, or did you make any changes to it?
 
Upvote 0
Hi-- I added a few lines underlined below to remove the new directory if it already exists . Also I just tried executing the code by removing those lines and this time it ran fine but didnt remove the first line from the .txt files

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

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error GoTo ErrTrap
Kill NewPath & "\" & "*.*"
On Error GoTo ErrTrap
RmDir NewPath & "\"

MkDir NewPath

ErrTrap:
On Error Resume Next

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

End Sub
 
Upvote 0
Ok, how about
VBA Code:
Sub ExportSheetsToText()
   Dim xWs As Worksheet
   Dim xTextFile As String
   Dim NewPath As String
   NewPath = ThisWorkbook.Path & "\New"
   
   If Dir(NewPath, vbDirectory) <> "" Then
      On Error Resume Next
      Kill NewPath & "\" & "*.*"
      On Error GoTo 0
      RmDir NewPath
   End If
   MkDir NewPath
   
   For Each xWs In Application.ActiveWorkbook.Worksheets
      xWs.Copy
      xTextFile = NewPath & "\" & xWs.Name & ".txt"
      ActiveSheet.Rows(1).Delete
      Application.ActiveWorkbook.SaveAs FileName:=xTextFile, FileFormat:=xlText
      Application.ActiveWorkbook.Saved = True
      Application.ActiveWorkbook.Close
   Next
End Sub
 
Upvote 0
Still getting the same error. Please see screenshot below. Also the ActiveSheet.Rows(1).Delete is not removing the first line from the txt file even if I try to run it with the rmdir loop.

1594042264117.png
 
Upvote 0
Are any of the sheets protected?
Also do any of the sheets contain merged cells?
 
Upvote 0
Do any of the sheets contain structured tables?
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top