Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Save Excel VBA as tab delimited text file without quotes

Today I use this code to save my sheet as a tab delimited text file:

ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filnamn, FileFormat:=xlText

It works for 90% of the data but some cell values get surrounded with quote marks? Why is this and how can I fix it?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default Save Excel VBA as tab delimited text file without quotes

kakisami14 wrote:

Today I use this code to save my sheet as a tab delimited text file:

ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filnamn,
FileFormat:=xlText

It works for 90% of the data but some cell values get surrounded with
quote marks? Why is this and how can I fix it?


That generally happens when the cell contains a comma. Excel does that
automagically, no workaround possible that I'm aware of. Instead, after
saving the file, read it into a string and remove the quotes in code:

ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & filnamn, _
FileFormat:=xlText
Open ActiveWorkbook.Path & "\" & filnamn For Binary As 1
contents = Space$(LOF(1))
Get #1, 1, contents
Close 1
'note that this removes *all* quotes;
'if you want to keep any you'll need to deal with that yourself
contents = Replace(contents, """", "")
Open ActiveWorkbook.Path & "\" & filnamn For Output As 2
Print #2, contents;
Close 2

--
If they were servants by nature,
you wouldn't have to beat them to get them to obey.
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Save Excel VBA as tab delimited text file without quotes

Today I use this code to save my sheet as a tab delimited text file:

ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filnamn,
FileFormat:=xlText

It works for 90% of the data but some cell values get surrounded with
quote marks? Why is this and how can I fix it?


Here's how I do it...


Sub WksToTabFile(Optional Wks As Worksheet)
' Loads worksheet data into a tab-delimited text file
' Requires WriteTextFile() to create the file

Dim vData, n&, sFile$

sFile = Application.GetSaveAsFilename
If sFile = False Then Exit Sub

If Wks Is Nothing Then Set Wks = ActiveSheet
vData = Wks.UsedRange
ReDim vTmp(1 To UBound(vData))

'Load data from 2D array to 1D array
For n = LBound(vData) To UBound(vData)
vTmp(n) = Join(Application.Index(vData, n, 0), vbTab)
Next 'n

'Write data to text file
WriteTextFile Join(vTmp, vbCrLf), sFile
End Sub

Sub WriteTextFile(TextOut$, Filename$, _
Optional AppendMode As Boolean = False)
' Reusable procedure that Writes/Overwrites or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**
Dim iNum%
On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then
Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut;
Else
Open Filename For Output As #iNum: Print #iNum, TextOut;
End If

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFile()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Save delimited format, specify delimiter, remove quotes from text HotPepper Excel Discussion (Misc queries) 2 October 21st 09 08:53 PM
Japanese text lost when save as tab delimited text file Greg Lovern Excel Programming 0 October 24th 07 08:39 PM
Output space-delimited file with quotes around text? Sinc Excel Discussion (Misc queries) 1 December 6th 06 12:02 AM
How can I save a file as a comma-delimited text file in Excel? LAM Excel Discussion (Misc queries) 1 May 3rd 05 10:24 PM
Save file as text pipe delimited Ashley[_3_] Excel Programming 0 May 5th 04 06:38 PM


All times are GMT +1. The time now is 11:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"