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?

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.

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