saving each sheet as a separate text file

Topic

New Reply

I have this macro that I used when I had Excel 2003 and now it is failing under 2007. I have a workbook that has multiple sheets. I need to save each sheet as a separate .txt.file.

Can someone tell me why this is failing? The error is a runitome ‘1004’ Method ‘Copy’ of object ‘_Worksheet’ failed.

[codebox]Sub createTxt()
Dim oWS As Worksheet, oWB As Workbook
Dim strPath As String, strFName As String
strPath = “\Bl-recs-goalieprofessional_staffBudgetsAdministration9-10BudgetBudget_Upload”
For Each oWS In Worksheets
oWS.Copy
ActiveWorkbook.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlText
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Next oWS
End Sub[/codebox]

Thanks in advance!

Reply | Quote

Replies

Does it fail with the first sheet, or does it create one or more files, and fail then?

Reply | Quote

Hans,
I tried copying the sheets over to a new workbook and it worked. Thanks anyway.

Reply | Quote

Can someone tell me why this is failing? The error is a runtime ‘1004’ Method ‘Copy’ of object ‘_Worksheet’ failed.

This more concise version works when run in Excel 2003 on Excel 2007 format files:

Sub createTxt()
Dim oWS As Worksheet
Dim strPath As String, strFName As String

strPath = “C:” ‘ changed from your version
Application.DisplayAlerts = False
For Each oWS In ThisWorkbook.Worksheets
ActiveWorkbook.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlText
Next oWS
Application.DisplayAlerts = True
End Sub

Why does your posted version need the Copy line?

Reply | Quote

John,
I tried using your suggested code, and while I do get a text file for each of my sheets, the txt files are being saved as sheet1.txt & sheet2.txt rather than the name of the sheet. This is an important part of the process. I also noticed that the name of one of the sheets remained as I named it, and the other became sheet2 and the name of the workbook became sheet2.txt.

I have attached my test version of the file that has 2 sheets in it to see if you have any luck figuring out why.
Thanks!

Reply | Quote

I tried using your suggested code, and while I do get a text file for each of my sheets, the txt files are being saved as sheet1.txt & sheet2.txt rather than the name of the sheet.

I get “23463112768.txt” and “2346311.txt”; so I’m not sure what’s going on. Since I’m using Excel 2003 with the 2007 converter, perhaps someone with Excel 2007 can figure it out.

Reply | Quote

I tried John’s code in 2007, and I get two txt files with the proper sheet tab numbers as file names, but both files have the info from sheet 2346311

Reply | Quote

I tried John’s code in 2007, and I get two txt files with the proper sheet tab numbers as file names, but both files have the info from sheet 2346311

Whoah, good catch, and bad diligence on my part. The active sheet gets printed every time, so we have to activate each sheet in the loop, so:
Sub createTxt()
Dim oWS As Worksheet
Dim strPath As String, strFName As String

strPath = “C:” ‘ changed from your version
Application.DisplayAlerts = False
For Each oWS In ThisWorkbook.Worksheets
oWS.Activate
ActiveWorkbook.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlText
Next oWS
Application.DisplayAlerts = True
ActiveWorkbook.Saved = True ‘ let’s not save the whole thing as text
End Sub

Reply | Quote

How about:

For Each oWS In ThisWorkbook.Worksheets
oWS.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlText
Next oWS

And not activating each sheet at all?

Steve

Reply | Quote

How about:

For Each oWS In ThisWorkbook.Worksheets
oWS.SaveAs Filename:=strPath & oWS.Name, FileFormat:=xlText
Next oWS

And not activating each sheet at all?

I think you have the winner, except that that the FileFormat argument seems to require … FileFormat:=xlTextMSDOS for worksheet saves.

Reply | Quote