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

 

[ankitaugust]

Yes.. there is a table in one of the sheets which is leveraged by Power Query to read the source file

 

[ankitaugust]

is there a way I can avoid exporting that sheet? The name of the sheet is config and I anyway delete the config.txt file after it is created.

 

[Fluff]

Ok, how about

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
      If LCase(xWs.Name) <> "config" Then
         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
      End If
   Next
End Sub

 

[ankitaugust]

Still getting the same error. Please see screenshot below.
Thank you for taking out time for this.

 

[Fluff]

When you get the error, does the active sheet contain any tables?

 

[ankitaugust]

no. I tried changing with different active sheet but it still fails at the delete line

 

[Fluff]

In that case I'm not sure. The only way I can get an error is if
1) the activesheet is protected
2) the activesheet has merged cells in rows 1& 2
3) the activesheet has a table on row 1

 

[ankitaugust]

I just tried creating a test file with 2 sheets and ran the code there and it worked fine and removed the first line of the .txt files also.

In my actual file, I am using PowerQuery to generate the sheets . Is it possible that the powerquery is creating the sheets as tables. If yes then is there a way to change it to normal sheet?

 

[Fluff]

I've never used PQ so cannot be sure, but believe it does create tables.
As it's not something I've ever used I don't know if you can convert them to ranges.

 

[ankitaugust]

Well Thanks a lot for all your help Fluff. You did solve my original concern on how to remove the first line from the .txt file. I'll try to figure out what can be done with PowerQuery