setting delimiters
 

Have the following code that creates a small text file. I need to set the
delimiters to tab for field and enter for record. The reason is so it can be
reconized by word for mail merge.
Thanks

Dim FF As Long
Dim RowCount As Long
Dim ColCount As Long
Dim TotalFile As String
Dim MyFile As Variable
FF = FreeFile
Open "C:\Parade\ZZZ.txt" For Output As #FF
For RowCount = 1 To Cells(Rows.Count, "A").End(xlUp).row
If WorksheetFunction.CountBlank(range("a" & RowCount & ":e" &
RowCount)) = 0 Then
If RowCount 1 Then TotalFile = TotalFile & vbCrLf
For ColCount = 1 To Cells(RowCount, Columns.Count).End(xlToLeft).Column
If ColCount 1 Then TotalFile = TotalFile & ","
TotalFile = TotalFile & Cells(RowCount, ColCount).Value
Next
End If
Next

Dim Blanks As Long
Dim LastRow As Integer
Blanks = msgbox(Prompt:="Do you have blanks to complete?,If not sure
Click on Yes", Buttons:=vbYesNo)
If Blanks = vbYes Then
LastRow = Cells(Rows.Count, "A").End(xlUp).row
range("D5:M5").Resize(LastRow - 4).Select
Userform1_Main_form.Hide
Selection.specialcells(xlCellTypeBlanks).Select
End If
If Blanks = vbNo Then
End If
Print #FF, TotalFile
Close #FF

setting delimiters
 

A tab is chr(9). so make the following replacement

from
If ColCount 1 Then TotalFile = TotalFile & ","
to
If ColCount 1 Then TotalFile = TotalFile & chr(9)

"Curt" wrote:

Have the following code that creates a small text file. I need to set the
delimiters to tab for field and enter for record. The reason is so it can be
reconized by word for mail merge.
Thanks

Dim FF As Long
Dim RowCount As Long
Dim ColCount As Long
Dim TotalFile As String
Dim MyFile As Variable
FF = FreeFile
Open "C:\Parade\ZZZ.txt" For Output As #FF
For RowCount = 1 To Cells(Rows.Count, "A").End(xlUp).row
If WorksheetFunction.CountBlank(range("a" & RowCount & ":e" &
RowCount)) = 0 Then
If RowCount 1 Then TotalFile = TotalFile & vbCrLf
For ColCount = 1 To Cells(RowCount, Columns.Count).End(xlToLeft).Column
If ColCount 1 Then TotalFile = TotalFile & ","
TotalFile = TotalFile & Cells(RowCount, ColCount).Value
Next
End If
Next

Dim Blanks As Long
Dim LastRow As Integer
Blanks = msgbox(Prompt:="Do you have blanks to complete?,If not sure
Click on Yes", Buttons:=vbYesNo)
If Blanks = vbYes Then
LastRow = Cells(Rows.Count, "A").End(xlUp).row
range("D5:M5").Resize(LastRow - 4).Select
Userform1_Main_form.Hide
Selection.specialcells(xlCellTypeBlanks).Select
End If
If Blanks = vbNo Then
End If
Print #FF, TotalFile
Close #FF

setting delimiters
 

Thank You

"Joel" wrote:

A tab is chr(9). so make the following replacement

from
If ColCount 1 Then TotalFile = TotalFile & ","
to
If ColCount 1 Then TotalFile = TotalFile & chr(9)

"Curt" wrote:

Have the following code that creates a small text file. I need to set the
delimiters to tab for field and enter for record. The reason is so it can be
reconized by word for mail merge.
Thanks

Dim FF As Long
Dim RowCount As Long
Dim ColCount As Long
Dim TotalFile As String
Dim MyFile As Variable
FF = FreeFile
Open "C:\Parade\ZZZ.txt" For Output As #FF
For RowCount = 1 To Cells(Rows.Count, "A").End(xlUp).row
If WorksheetFunction.CountBlank(range("a" & RowCount & ":e" &
RowCount)) = 0 Then
If RowCount 1 Then TotalFile = TotalFile & vbCrLf
For ColCount = 1 To Cells(RowCount, Columns.Count).End(xlToLeft).Column
If ColCount 1 Then TotalFile = TotalFile & ","
TotalFile = TotalFile & Cells(RowCount, ColCount).Value
Next
End If
Next

Dim Blanks As Long
Dim LastRow As Integer
Blanks = msgbox(Prompt:="Do you have blanks to complete?,If not sure
Click on Yes", Buttons:=vbYesNo)
If Blanks = vbYes Then
LastRow = Cells(Rows.Count, "A").End(xlUp).row
range("D5:M5").Resize(LastRow - 4).Select
Userform1_Main_form.Hide
Selection.specialcells(xlCellTypeBlanks).Select
End If
If Blanks = vbNo Then
End If
Print #FF, TotalFile
Close #FF