Consulting

Results 1 to 6 of 6

Thread: copy every 2 rows of data into a pipe delimited text file

  1. #1
    VBAX Newbie
    Joined
    Mar 2006
    Posts
    3
    Location

    copy every 2 rows of data into a pipe delimited text file

    I have an excel file that has 400 rows of data. I need to copy every 2 rows of data into a pipe delimited text file. I also need to copy row 1 into every file. So, every file with have the first row from the excel file and the next two rows of data. There will be 200 files.

    One more note: there is only data through column X, but need the pipes to go through columns AB

    Location: c:\user\

    Filenames: Loader1.txt, Loader2.txt, etc.

  2. #2
    VBAX Mentor
    Joined
    Jul 2012
    Posts
    398
    Location
    can you attach sample files ?

  3. #3
    VBAX Regular HaHoBe's Avatar
    Joined
    Aug 2004
    Location
    Hamburg
    Posts
    89
    Location
    Hi, rki1966,

    maybe try it like this:
    Sub WriteToTextfilfe()
    
    Dim lngCol As Long
    Dim lngCounter As Long
    Dim lngLoop As Long
    Dim intFile As Integer
    Dim strFile As String
    Dim strText As String
    
    Const cstrPath As String = "c:\user\"
    
    Do While Range("A2").Value <> ""
      lngCounter = lngCounter + 1
      strFile = cstrPath & "loader" & lngCounter & ".txt"
      intFile = FreeFile
      Open strFile For Output As intFile
      For lngLoop = 1 To 3
        For lngCol = 1 To 28
          strText = strText & Cells(lngLoop, lngCol).Value & "|"
        Next lngCol
        strText = Left(strText, Len(strText) - 1)
        Print #intFile, strText
        strText = ""
      Next lngLoop
      Close intFile
      Range("A2:A3").EntireRow.Delete xlShiftUp
    Loop
    End Sub
    Ciao,
    Holger

  4. #4
    VBAX Newbie
    Joined
    Mar 2006
    Posts
    3
    Location
    thanks, this is great.

    I just need one change which I forgot to indicate. Actually the first row is just text that does not need pipes. Would it be easier to just add the text to the code so the first row will be "~Format=transaction" and the next two rows will be the data set with pipes.

  5. #5
    VBAX Newbie
    Joined
    Mar 2006
    Posts
    3
    Location
    I figured it out, thanks

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635

Posting Permissions

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