Tab delimited text file issue

[captbluefin]

Here is a simple example of the sheet layout. Three rows and at most 5 columns with data.

A B C
D E F G
H I J K L


We save the sheet into a tab delimited text file.
The text file ends up as 5 columns by 3.
The problem is when we go to upload the tab delimited text file to the receiving authority.
We get an error pertaining to the number of columns in rows 1 and 2.
The first row has data in 3 columns. However the tab delimited text file has tabs for the "empty" 4th and 5th column of row 1.
The second row has data in 4 columns and an extra tab for the "empty" 5th column.
All the rows after row 3 have data in all 5 columns.
We can manually delete the tabs from the text file, and all is good with the upload.
Obviously, we would like to avoid the need to manually edit the text file.

The only way we have been able to think of to accomplish what we want is to split the information onto 3 sheets. One sheet with row 1 that has only 3 columns, sheet2 with row 2 that has 4 columns, and a 3rd sheet that has all the rest of the rows that have 5 columns. Then save 3 text files. Then copy the 3 files into one file. Obviously a kludge at best. Never mind we are clueless at how to run a dos command prompt using variables from within Excel VBA.

Here's an example of something that on the surface should be simple, but in practice is an issue.

As always, any ideas are greatly appreciated.

[Paul_Hossler]

Maybe this

It uses the WB path and name to create a TDL with a .txt extension

The macro (as it is currently written) must be in the same workbook as the data

Option Explicit
Sub GenerateTDL()
    Dim rData As Range, rRow As Range
    Dim fileNum As Long
    Dim filePath As String
    Dim fileLine As String
    Dim i As Long
    Dim vTemp As Variant
    
    
    'get TDL file name = WB path + name + TXT
    i = InStrRev(ThisWorkbook.FullName, ".")
    filePath = Left(ThisWorkbook.FullName, i) & "txt"
    
    'get data to use
    Set rData = ActiveSheet.Cells(1, 1).CurrentRegion
    
    'delete if already exists
    Application.DisplayAlerts = False
    On Error Resume Next
    Kill filePath
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    'create TDL file
    fileNum = FreeFile
    Open filePath For Output As #fileNum
    
    'get a row, combine with tabs, and remove trailing
    For Each rRow In rData.Rows
        Application.StatusBar = "Processing " & Format(rRow.Row, "#,##0")
        vTemp = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(rRow))
        fileLine = Join(vTemp, vbTab)
        Do While Right(fileLine, 1) = vbTab
            fileLine = Left(fileLine, Len(fileLine) - 1)
        Loop
        
        Print #fileNum, fileLine
    Next
    
    Close #fileNum
    
    MsgBox "Wrote " & rData.Address & vbCrLf & vbCrLf & " as tab deliminated " & vbCrLf & vbCrLf & filePath
End Sub

[captbluefin]

THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!