excel strips tabs from ends of lines in text file 1

[jay9333]

Hi there,

I've written a macro that opens a tab-delimited text file in excel that looks like:
----------
PTIDS Unknowns[Dilution] Un01 1 Dilution Factor 100
PTIDS Unknowns[Dilution] Un02 1 Dilution Factor 100
PTIDS Unknowns[Dilution] Un03 1 Dilution Factor 100
PTIDS Unknowns[Dilution] Un04 1 Dilution Factor 100
----------
... and then simply replaces the UnXX with strings, such as
----------
PTIDS Unknowns[Dilution] A04100027 06 06/12/02 1 Dilution Factor 100
PTIDS Unknowns[Dilution] A04100134 06 06/12/02 1 Dilution Factor 100
PTIDS Unknowns[Dilution] A04100029 06 06/12/02 1 Dilution Factor 100
PTIDS Unknowns[Dilution] A04104562 06 06/12/02 1 Dilution Factor 100
----------
... and then saves the resulting file as another tab-delimited text file text. I'll call the macro I've written to do this the "replace UnXX macro".

The problem is that after running the macro the new text file is missing a tab character at the end of each line. So in "whitespace view" (where "tab"=tab character) the files are as such...
original:
----------
PTIDS"tab"Unknowns[Dilution]"tab"Un01"tab"1"tab""tab"Dilution Factor"tab"100"tab"
PTIDS"tab"Unknowns[Dilution]"tab"Un02"tab"1"tab""tab"Dilution Factor"tab"100"tab"
PTIDS"tab"Unknowns[Dilution]"tab"Un03"tab"1"tab""tab"Dilution Factor"tab"100"tab"
PTIDS"tab"Unknowns[Dilution]"tab"Un04"tab"1"tab""tab"Dilution Factor"tab"100"tab"
----------

new (after opening in excel and running "replace UnXX macro":
----------
PTIDS"tab"Unknowns[Dilution]"tab"A04100027 06 06/12/02"tab"1"tab""tab"Dilution Factor"tab"100
PTIDS"tab"Unknowns[Dilution]"tab"A04100134 06 06/12/02"tab"1"tab""tab"Dilution Factor"tab"100
PTIDS"tab"Unknowns[Dilution]"tab"A04100029 06 06/12/02"tab"1"tab""tab"Dilution Factor"tab"100
PTIDS"tab"Unknowns[Dilution]"tab"A04104562 06 06/12/02"tab"1"tab""tab"Dilution Factor"tab"100
----------

This for some reason is wreaking havoc on the software program (clinical data analysis software called "softmax") I'm importing the new text file in. Softmax wants a tab at the end of each line. I tried recording a macro while I manually put tabs in each ending cell (which I would then modify to do automatically), but when I hit "tab" in excel it switches to the next cell. Is there anyway to get excel to not strip tabs of the end of a file, or to put a tab at the end of each line in my text file?

Is there vba code to just do some file manipulation in the text file itself after it is saved, and put a tab at the end of each character? Or is there a way to automatically do this in excel before I save the new file as a tab-delimited text file? I don't know if excel strips the tabs upon opening the original text file or upon saving the new text file.

The code I use to open the original text file is as follows. I'm not sure if there is some modification I can make in it to keep end of line tabs or something... maybe the "FieldInfo" setting at the end (I'm not sure what that does... I just got the code of the web).

   Dim TemplateWB As Variant 'variable to refer to template exported as text from softmax
   TemplateWB = Application.GetOpenFilename("Excel-files,*.txt", _
       1, "Select Template To Use", , False)
   If TypeName(TemplateWB) = "Boolean" Then Exit Sub
   ' the user didn't select a file
   Debug.Print "Selected file: " & TemplateWB
      Workbooks.OpenText Filename:=TemplateWB, _
                StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 2)

A brief excerpt of the code I use to replace the "UnXX" strings follows:

  Dim NumOfRowsInTemplate As Long 'holds the number of rows in the template
  NumOfRowsInTemplate = Cells.Find(What:="*", _
       SearchDirection:=xlPrevious, _
       SearchOrder:=xlByRows).row

  For rowToCopy = 1 To NumOfRowsInTemplate
     If UCase(Left(Cells(rowToCopy, 3).Value, 2)) = "UN" Or UCase(Left(Cells(rowToCopy, 3).Value, 2)) = "NO" Then  'If this is an "UnXX" (unknown antigen) or a "no XX" (unknown noAg) cell, then it might have a ptid value)
        If StripNumberFromString(Cells(rowToCopy, 3).Value) <= NumberOfPtids Then ' If there is a ptid for this unknown ptid
           ' set this unknown equal to the proper PTIDs string
           Cells(rowToCopy, 3).Value = PtidsArray(StripNumberFromString(Cells(rowToCopy, 3).Value) - 1)
        End If 'end if there is a ptid for this unknown ptitd
     End If 'end if this is an "UnXX" cell
     
  Next rowToCopy

The code I use to save the modified text file is as follows.

Dim FName As Variant

FName = Application.GetSaveAsFilename(, "Excel-files,*.txt", , "Type a name and select a location for the loaded template")
If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
End If

ActiveWorkbook.SaveAs Filename:= _
    FName, _
    FileFormat:=xlText, _
    CreateBackup:=False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

Any help would be much appreciated, or a pointer to a good tutorial on the web concerning file input/output functions for vba.

thank you,

jay

 

[SkipVought]

Why not put a SPACE or ZERO in the trailing cells that you need to have a tab in

???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jay9333]

Thanks Skip,

That worked wonderfully... and was much easier I imagine then manipulating the text file after the fact.