[b]Saving multiple worksheets as delimited files[b/] 1

[Robinstwitcher]

Am trying to save multiple worksheets within a workbook as a tab delimited file. However with the following code it saves multiple files with the correct sheet name, but only the data from Sheet1 (i.e. doesn't save the data from all the seperate worksheets)

Private Sub CommandButton2_Click()
Dim ws As Worksheet

ThisWorkbook.Save
For Each ws In ActiveWorkbook.Worksheets
MName = ws.Name & ".txt"
MDir = ActiveWorkbook.Path
ActiveWorkbook.SaveAs Filename:=MDir & "\" & MName, FileFormat:= _
xlText, CreateBackup:=False
Next ws
End Sub

 

[rmikesmith]

Try this instead:

Private Sub CommandButton2_Click()
Dim ws As Worksheet

ThisWorkbook.Save
For Each ws In ActiveWorkbook.Worksheets
    MName = ws.Name & ".txt"
    MDir = ActiveWorkbook.Path
    [COLOR=red]ws[/color].SaveAs Filename:=MDir & "\" & MName, FileFormat:= _
        xlText, CreateBackup:=False
Next ws
End Sub

Regards,
Mike

 

[Robinstwitcher]

Mike,

returns: Run-time error '1004': Method 'SaveAs' of object '_Worksheet' failed

Any ideas?

 

[rmikesmith]

Try single-stepping through your code to if this error is occurring first time through the loop or otherwise. Or, select Debug when the error occurs and determine which worksheet is being referenced. Either way, inspect your variable values to make sure they are valid. I just ran a modified version doing a SaveAs on one worksheet with no problems.


Regards,
Mike

 

[Robinstwitcher]

Mike,

it falls over on the first sheet - I have checked the variables and they seem to be populated as I would expect

 

[rmikesmith]

Is the worksheet name a valid filename?

Are there write-access restrictions on the destination drive/folder?


Regards,
Mike

 

[Bong]

It seems to me that if you're saving as text (like saving as .csv), Excel only knows from the first sheet. Why not just delete the first sheet until there are no more?

for each ws in activeworkbook.worksheets
    ActiveWorkbook.SaveAs Filename:=MDir & "\" & MName, FileFormat:= _
       xlText, CreateBackup:=False
    ws.delete
next

_________________
Bob Rashkin

 

[Robinstwitcher]

Mike,

The worksheet names are currently, sheet1, sheet2 etc to a drive with appropriate permissions. (Proved that with the original bit of code)

Bob,

I can see where you are coming from - but doesn't it save the whole document as a .txt file rather than a worksheet - therefore deleting worksheets isn't resolving the issue?

Not sure if proves that I can't do what I want to do??!!

 

[mscallisto]

This seems to work for me

[b]Sub XL2txt()[/b]
Dim ws As Worksheet

ThisWorkbook.Save
For Each ws In ActiveWorkbook.Worksheets
    MName = ws.Name & ".txt"
    MDir = ActiveWorkbook.Path
[b] Sheets(ws.Name).Select[/b]
    ActiveWorkbook.SaveAs Filename:=MDir & "\" & MName, FileFormat:= _
        xlText, CreateBackup:=False
Next ws
End Sub

 

[Robinstwitcher]

Nice One - working as I hoped

 

[rmikesmith]

Or

Sub SaveSheetsToFile()
Dim ws As Worksheet
Dim MName As String
Dim MDir As String

   For Each ws In ActiveWorkbook.Worksheets
     MName = ws.Name & ".txt"
     MDir = ActiveWorkbook.Path
     [b]ws.Select[/b]
     ActiveWorkbook.SaveAs Filename:=MDir & "\" & MName, FileFormat:= _
          xlText, CreateBackup:=False
   Next ws

End Sub

Although the issue of why you were getting the 1004 error is unresolved. I did a complete mock-up using multiple sheets and the code I posted earlier without errors.

Regards,
Mike