Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as txt-file macro
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 -- Beginner8 ----------------------------------------------------------------------- Beginner81's Profile: http://www.excelforum.com/member.php...fo&userid=2538 View this thread: http://www.excelforum.com/showthread.php?threadid=38862 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as txt-file macro
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 "Beginner81" wrote 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...o&userid=25389 | View this thread: http://www.excelforum.com/showthread...hreadid=388629 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as txt-file macro
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...o&userid=25389 View this thread: http://www.excelforum.com/showthread...hreadid=388629 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save as txt-file macro
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 "Beginner81" wrote 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...o&userid=25389 | View this thread: http://www.excelforum.com/showthread...hreadid=388629 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Macro to Open File, Delete Contents, Save New File | Excel Discussion (Misc queries) | |||
Macro Save File (Unique file name) | Excel Worksheet Functions | |||
ASP: Open Excel File with Macro, Allow Macro to run, and then save | Excel Programming | |||
Macro to insert values from a file and save another sheet as a .txt file | Excel Programming | |||
Automate open file, update links, run macro, close and save file | Excel Programming |