appending to .txt file format problems

[pikapika13]

I currently have the following code. It basically appends a chunk of
data at the end of a .txt file. However, the way its formatting is not
what I want. Currently, it takes each cell in Range(A1:J?) and makes
it's own row in the .txt file. I want a tab delimited file that
maintains the column-like format A:J.
Example:
What I have and don't want in the .txt file:

Data1
Data2
Data3
etc.

What I need:

Data1 Data2 Data3 ....Data8
Data9 Data10 ........Data16 etc.





Sub XfertoNotepad()

Dim FileNum As Integer
Sheets("SQL").Select
Range("A2").Select
With ActiveSheet
Set Lend = .Cells(.Rows.Count, "J").End(xlUp)
End With


FileNum = FreeFile


Open "C:\4x\" & docsol For Append As #FileNum


Print #FileNum, [a2]
For Each cl In Range("A3:" & Lend)

Print #FileNum, myStr
myStr = "": myStr = myStr & cl
Next
'appends the input to an existing file write to the textfile
Print #FileNum, myStr
Close #FileNum ' close the file
End Sub

 

[Guest]

Hi

I changed your code as below. I have hard coded the filename to test my
code. Give this a try.

Dim Lend As Long
Dim FileNum As Integer
Sheets("Sheet1").Select
Range("A2").Select
With ActiveSheet
Lend = .Cells(2, 1).End(xlDown).Row

FileNum = FreeFile
Open "C:\data\t1\docsol.TXT" For Append As #FileNum
Print #FileNum, [a2]
For x = 3 To Lend
mystr = ""
For y = 1 To 9
mystr = mystr & .Cells(x, y).Value & Chr$(9)
Next
mystr = mystr & .Cells(x, 10).Value
Print #FileNum, mystr

Next
End With
Close #FileNum ' close the file

 

[NickHK]

Here's one approach:

Private Sub CommandButton1_Click()
Dim FileNum As Integer
Dim DObj As MSForms.DataObject

On Error GoTo Handler

FileNum = FreeFile
Open "C:\TestData.csv" For Append As #FileNum

Set DObj = New MSForms.DataObject
Range("A4:" & Cells(Rows.Count, "D").End(xlUp).Address).Copy

With DObj
.GetFromClipboard
Print #FileNum, .GetText
End With

Close #FileNum ' close the file

Exit Sub

Handler:
Close #FileNum

End Sub

NickHK

 

[pikapika13]

ADG & Nick,

Thanks a lot! I'm new to VBA but this forum sure does have many
helpful people. I swear that once I'm good enough, I'll put in my fair
share.

ADG, I used yours since yours was 1st response. It works great.
Thanks!