Tab Delimited Text File........Excel is entering unneeded qu

[Guest]

I'm using Excel to create a tab delimited flat file that will be imported into another system.

I have a macro written to save the file as a tab delimited text file. For the most part everything works file. However, there are some cases where Excel is including unneeded quotes in the text file.

Example - Cell A1 has the following contents: FCPLT,FM-WLI-16E1B

Save as Text File (tab delimited).

Open the text file and it now reads: "FCPLT,FM-WLI-16E1B"

In this case the quote is added because of the comma. If I change the comma to a dash it works perfectly. However, I don't have a choice, I need to maintain the original format. In cases where a quote exists in the original entry, Excel adds an additional quote (" -> "").

Is there are way to prevent this?

Thanks,
Brent

 

[brentframe]

I'm having a problem with this too. Can anyone help?

 

[Jim North]

Please post the code you using to write to the file.

 

[AJ]

I think the only way to do this is write the file directly. Here's some code that should do the trick...

Sub TABExport()
    
    Dim strg As String
    Dim TABFilename As String
    Dim MyRow As Long, MaxRow As Long
    Dim MyCol As Integer, MaxCol As Integer
    Dim ChkFile As String
    Dim DoubleCheck As VbMsgBoxResult

'   Get filename of output file
    TABFilename = Application.GetSaveAsFilename(filefilter:="Text (Tab Delimited) (*.txt), *.txt")

'   Check to see if the file already exists
    If TABFilename <> "" Then
        ChkFile = Dir(TABFilename, vbNormal)
        If ChkFile <> "" Then
            DoubleCheck = MsgBox("The file " & ChkFile & " already exists. Do you want to replace the existing file?", vbYesNo + vbExclamation)
        End If
    
'       Delete existing file if overwrite is confirmed
        If DoubleCheck = vbYes Then
            Kill (TABFilename)
        End If
    
'       Create the output file if we have a filename and have verified it's ok.
        If DoubleCheck <> vbNo Then

'           Determine the values for MaxRow and MaxCol
'           This returns the same values as hitting End Home / Ctrl+End
            MaxCol = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
            MaxRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

'           StartRow
            MyRow = 1

'           Output file until Print sequence reaches MaxRow
'           String to Print is based on number of columns used in sheet
            Open TABFilename For Output As #1
            Do Until MyRow = MaxRow + 1
                strg = ""
                For MyCol = 1 To MaxCol
                    If MyCol > 1 Then strg = strg & Chr(9)
                        strg = strg & Cells(MyRow, MyCol).Value
                Next
                Print #1, strg
                MyRow = MyRow + 1
            Loop
            Close #1
            MsgBox "A file " & TABFilename & " was created", vbInformation
        
            End If
    
        End If
        
End Sub