+ Reply to Thread
Results 1 to 4 of 4

appending to .txt file format problems

  1. #1
    Forum Contributor
    Join Date
    06-21-2004
    Posts
    106

    appending to .txt file format problems

    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

  2. #2
    ADG
    Guest

    RE: appending to .txt file format problems

    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
    --
    Tony Green


    "pikapika13" wrote:

    >
    > 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
    >
    >
    > --
    > pikapika13
    > ------------------------------------------------------------------------
    > pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
    > View this thread: http://www.excelforum.com/showthread...hreadid=566906
    >
    >


  3. #3
    NickHK
    Guest

    Re: appending to .txt file format problems

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >
    >
    > --
    > pikapika13
    > ------------------------------------------------------------------------
    > pikapika13's Profile:

    http://www.excelforum.com/member.php...o&userid=10892
    > View this thread: http://www.excelforum.com/showthread...hreadid=566906
    >




  4. #4
    Forum Contributor
    Join Date
    06-21-2004
    Posts
    106
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1