Save as txt-file macro

[Beginner81]

Hi everyone,

I tried to save my xls-sheets to txt-files with a following macro:

Sub SaveFiles()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
' copy the sheet to its own workbook
sh.Copy
ActiveWorkbook.SaveAs "C:\Temp\" & sh.Name & ".txt", _
FileFormat:=xlTextWindows
ActiveWorkbook.Close SaveChanges:=False
Next
End Sub

Everything is fine except one thing. There comes some extra tabs to th
end of some lines in my txt-files. What is the best way to avoid thos
empty spaces?
Is it possible to make some kind of loop to my macro? Or is there a
existing "command" (member) in some class?

I really appreciate any of your tips!

-Beginner81

 

[Jim Rech]

This isn't a problem related to your macro specifically. It's how Excel
saves tab delimited text files. If you enter this data in a sheet:

aaa aaa aaa
aaa
aaa aaa aaa
aaa aaa aaa

and save it via File, save As in that format you'll get the same number of
tabs on each line in the text file. So for the second line you'd get "aaa"
<tab><tab>. That's just how Excel works and you cannot change it, as far as
I know.

When you don't like how Excel creates text files, and people frequently
don't, you have to use a macro to create the file. chip Pearson has an
example of exporting a text file at the bottom of this page. This isn't
exactly what you need but you can modify it to use tabs:

http://www.cpearson.com/excel/imptext.htm

--
Jim
in message |
| Hi everyone,
|
| I tried to save my xls-sheets to txt-files with a following macro:
|
| Sub SaveFiles()
| Dim sh As Worksheet
| For Each sh In ActiveWorkbook.Worksheets
| ' copy the sheet to its own workbook
| sh.Copy
| ActiveWorkbook.SaveAs "C:\Temp\" & sh.Name & ".txt", _
| FileFormat:=xlTextWindows
| ActiveWorkbook.Close SaveChanges:=False
| Next
| End Sub
|
| Everything is fine except one thing. There comes some extra tabs to the
| end of some lines in my txt-files. What is the best way to avoid those
| empty spaces?
| Is it possible to make some kind of loop to my macro? Or is there an
| existing "command" (member) in some class?
|
| I really appreciate any of your tips!
|
| -Beginner81-
|
|
| --
| Beginner81
| ------------------------------------------------------------------------
| Beginner81's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25389
| View this thread: http://www.excelforum.com/showthread.php?threadid=388629
|

 

[Beginner81]

Thanks Jim,

it seems to be quite difficult to modify the existing macro (at least
for me).

Is it possible to get any further tips? If I have (.xls)

aaa aaa aaa
aaa
aaa aaa
aaa aaa aaa aaa
(and so on)

How should I modify the macro that you mentioned or my own macro to get
txt file like:

aaa aaa aaa (<-no tab)
aaa (<-no tabs)
aaa aaa (<-no tabs)
aaa aaa aaa aaa

Thanks a lot in advance!

 

[Jim Rech]

Rather than modify Chip's macro here is one I did:

''Outputs the selection if more than one cell is selected, else entire sheet
Sub OutputActiveSheetAsTabDelim()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
Dim ColCount As Integer
Dim CurrCol As Integer
FName = Application.GetSaveAsFilename("", "Tab Delimited File (*.txt),
*.txt")
If FName <> False Then
ListSep = vbTab ''Chg to comma, etc for a different separator
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
ColCount = SrcRg.Columns.Count
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrCol = 0
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrCol = CurrCol + 1
CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub


--
Jim
in message |
| Thanks Jim,
|
| it seems to be quite difficult to modify the existing macro (at least
| for me).
|
| Is it possible to get any further tips? If I have (.xls)
|
| aaa aaa aaa
| aaa
| aaa aaa
| aaa aaa aaa aaa
| (and so on)
|
| How should I modify the macro that you mentioned or my own macro to get
| txt file like:
|
| aaa aaa aaa (<-no tab)
| aaa (<-no tabs)
| aaa aaa (<-no tabs)
| aaa aaa aaa aaa
|
| Thanks a lot in advance!
|
|
| --
| Beginner81
| ------------------------------------------------------------------------
| Beginner81's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25389
| View this thread: http://www.excelforum.com/showthread.php?threadid=388629
|