1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Copy Columns from excel into new file

Discussion in 'Business Applications' started by Supermanbam, Feb 21, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Supermanbam

    Supermanbam Thread Starter

    Joined:
    Feb 21, 2013
    Messages:
    4
    I am trying to write some vba code to Copy the the contents from each row in excel and make it into a new text file. I need the 1st Row to be in each text file though. So the 1st loop would be A1 & A2 row copy next loop would be A1 & A3 copy. When i paste the values in the new sheet to be saved as the text file I need to transpose the paste so row 1 now will be in column A. The filename would be created from the value in Column A. I have uploaded some example files. Any help would be greatly appreciated. The bonus would be if i could get any empty spaces removed from the cells.

    I tried modifying some code that Jimmy the Hand wrote linked here
    http://forums.techguy.org/business-applications/951580-save-excel-each-row-html.html
    with no luck.
     

    Attached Files:

  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi

    See if this works ... replace the Directory reference to something that will apply to you ..

    Code:
     
    Sub CreateFiles()
    Dim sFile As String
    Dim sText As String
    Dim iFileNum As Integer
     
    lRow = Range("A" & Rows.Count).End(xlUp).Row
     
    'start the loop at the 2nd row to get data for each file
     
    For i = 2 To lRow
    sText = ""
        For c = 1 To 5
     
                sText = sText & Trim(Cells(1, c)) & " " & Trim(Cells(i, c)) & vbNewLine
     
        Next c
     
    sFile = "C:\Users\UserName\Documents\" & Trim(Cells(i, 1)) & ".txt"
        iFileNum = FreeFile
            Open sFile For Output As iFileNum
                Print #iFileNum, sText
                    Close #iFileNum
    Next i
    End Sub
     
     
    
     
  3. Supermanbam

    Supermanbam Thread Starter

    Joined:
    Feb 21, 2013
    Messages:
    4
    Works awesome thanks a lot. :D
     
  4. Supermanbam

    Supermanbam Thread Starter

    Joined:
    Feb 21, 2013
    Messages:
    4
    Is it possible to save the format as tab delimited?
     
  5. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    You would change this line ...

    sText = sText & Trim(Cells(1, c)) & " " & Trim(Cells(i, c)) & vbNewLine
    to
    sText = sText & Trim(Cells(1, c)) & vbTab & Trim(Cells(i, c)) & vbNewLine

    but if you are trying to align the fields in the text files this won't do it because of the variable lengths of the headers. I suspect this is why there are spaces added in the header text. If you want to align the text files then one way would be to play around with the spacing of the headers and use this instead ...

    sText = sText & Cells(1, c) & Trim(Cells(i, c)) & vbNewLine

    where the header is no longer "trimmed".

    If, on the other hand, the text files are to be used by another program you may want to consider some other delimiter e.g. "|" or "," or ";". This is easily done by altering the same line as above.
     
  6. Supermanbam

    Supermanbam Thread Starter

    Joined:
    Feb 21, 2013
    Messages:
    4
    added the vbTab and it works awesome reads into my other software great thx a ton
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090511

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice