Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Save a single sheet in Tab or Text Format

  Asked By: Craig    Date: Sep 12    Category: MS Office    Views: 3009
  

I should have asked this then the thread I hijacked was still warm,
but...

RE:
> ActiveWorkbook.SaveAs Filename:=strFN, FileFormat:=xlNormal

Is there a text/Tab File Format? I've searched MSDN, the object
browser and the web, but found nothing for a text or Tab delimited
File Formats.

Detail:
I would like to save (a sheet as) a text file with standard Tab
delimited fields in the Records (lines). I want to have my Excel
sheet read and write another application's files. I found references
to many standard Microsoft file formats, but not Tab or text.

It appears there is a File Format of xlCSV - at least there is a
constant by that name. If so, I *could* simply pack my lines in a
single cell and stuff my own Tab chatacters, but having Excel do the
Tabs would be R E A L L Y nice.

Share: 

 

9 Answers Found

 
Answer #1    Answered By: Khadeeja Malik     Answered On: Sep 12

I don't think there is a way of doing that.

I list below the formats  you can use.
There does not seem to be a way of exporting to tab  delimited direct from
Excel either.

Looks like you will have to save  as CSV and then use word to find and replace
comma with tab.

You could do this in a macro in excel.



XlFileFormat can be one of these XlFileFormat constants.
xlCSV
xlCSVMSDOS
xlCurrentPlatformText
xlDBF3
xlDIF
xlExcel2FarEast
xlExcel4
xlAddIn
xlCSVMac
xlCSVWindows
xlDBF2
xlDBF4
xlExcel2
xlExcel3
xlExcel4Workbook
xlExcel5
xlExcel7
xlExcel9795
xlHtml
xlIntlAddIn
xlIntlMacro
xlSYLK
xlTemplate
xlTextMac
xlTextMSDOS
xlTextPrinter
xlTextWindows
xlUnicodeText
xlWebArchive
xlWJ2WD1
xlWJ3
xlWJ3FJ3
xlWK1
xlWK1ALL
xlWK1FMT
xlWK3
xlWK3FM3
xlWK4
xlWKS
xlWorkbookNormal
xlWorks2FarEast
xlWQ1
xlXMLSpreadsheet

 
Answer #2    Answered By: Shayan Anderson     Answered On: Sep 12

Unless I've missed the point,
File|Save As...
then in the 'Save as type:' field one of the options is:
Text(Tab delimited)(*.txt)
Pressing OK then results in a 'The selected filetype does not support
workbooks that contain multiple workbooks. To save  only the active
sheet click OK', which is fine for you. Pressing OK here results in
another warning about losing excel  features not compatible with tab
delimited text  files; this is what Help had to say about the format:

"The Text (*.txt) file format  saves only the text and values as they
are displayed in cells of the active worksheet. All rows and all
characters in each cell  are saved. Columns of data are separated by
tab characters, and each row of data ends in a carriage return. If a
cell contains a comma, the cell contents are enclosed in double
quotation marks. All formatting, graphics, objects, and other
worksheet contents are lost."

Since you've used 'Save As', then you can go back to your original
file, so nothing to worry aboout. Clicking 'Yes' here results in the
file being created. Opening this file  in NotePad confirmed that it was
indeed tab  delimited.

I recorded a macro of my doing this and got:

ActiveWorkbook.SaveAs Filename:="C:\Book3.txt", _
FileFormat:=xlText, CreateBackup:=False

 
Answer #3    Answered By: Frances Parker     Answered On: Sep 12

This is somewhat of a d'oh! I'd forgotten:
1- In spreadsheet-eese, text  *IS* Tab delimited.
2- When in doubt, record a macro.

Looked good. The String ""'s don't appear in the test.txt file  I
just did. Almost a winner, but... Excel always saves a rectangular
area in ASCII formats. Shorter lines  are padded with trailing Tabs.
I suspect that'll gag the other application I'm trying to emulate. At
least I can still build my own strings w/Tabs without quotes.

After I get that, the converse - reading the file back in.

If I could just use something like the old file I/O commands
[Input,Write,Print], it'd be just fine. I have seen something like
that in VB context somewhere... Perhaps the same technique I use for
serial I/O will work for files, just need to figure out the way to get
to a file vs. port..

 
Answer #4    Answered By: Adalgisa Fischer     Answered On: Sep 12

This example from the help for "OpenTextFile method" may help you

Sub OpenTextFileTest
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("c:\testfile.txt", ForAppending,TristateFalse)
f.Write "Hello world!"
f.Close
End Sub

 
Answer #5    Answered By: Meenakshi Khochar     Answered On: Sep 12

To save  other's time, while trying this out I got an error, solved (I
think) by changing
'ForAppending = 3' to 'ForAppending = 8'
(This is microsoft's own example which it seems they've got wrong)

 
Answer #6    Answered By: Roxanne Dixon     Answered On: Sep 12

This doesn't seem to be referenced in any of the VBA Help files  or
at MSDN.

I didn't have one of the VBA Help files ("can't't find
veenui3.hlp"), but I Googled and finally found  it as well as some
others with much more from a Yale site. I must open them manually,
but whatever works...

I now have references for many file  methods (Open...Close)I info.

 
Answer #7    Answered By: Elisa Larson     Answered On: Sep 12

To address the trailing tabs problem, here are a couple of macros, one
which adds a tab  character to the exported file  for every cell  that is
blank within a row, the other just ignores empty cells. Both have no
trailing tabs. They both use the usedrange of a sheet.
This is my first foray into using the filesystemobject, so there may
be some gaffes!

Opening the resultant files  seems fine if opened directly in Excel
(right-click, Open with...).

 
Answer #8    Answered By: Alessio Smith     Answered On: Sep 12

I'll add these to my File ops document. The .txt format  does
insert tabs for blank cells which is desired ... except for cells
before the CR. I'll study them

 
Answer #9    Answered By: Sammy Anderson     Answered On: Sep 12

This worked for me. I just recorded myself saving a workbook in the
text (tab delimited) format.

ActiveWorkbook.SaveAs Filename:=strFN FileFormat:=xlText

 
Didn't find what you were looking for? Find more on Save a single sheet in Tab or Text Format Or get search suggestion and latest updates.




Tagged: