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.Trans pose(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!!!!!!!!!!!!!!!!!!!!!!!!