Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenating cells (Excel 2000)

    I have been given a scenario of an EXCEL 2000 file that has text in 1500 cells down a worksheet. I need to take those cells and put them into one long string of text in a text file, with spaces, not tabs between each word. Concatenation of 1500 cells seems like a very long formula. Is there any easier way. Maybe a short piece of code or a function to combine these I'm not thinking about?
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating cells (Excel 2000)

    Are you sure that is really the intention? Say that the cells consist of 6 characters on average. The result would be one line of about 1500 * 6 + 1499 (spaces) = 10499 characters long! Many applications can't handle lines that long!

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating cells (Excel 2000)

    I followed some code found on the side by jscher

    Option Explicit
    Sub SaveEachAsText()
    Dim sOriName As String
    Dim wks As Worksheet
    Dim sPath As String
    Dim sFilename As String
    sOriName = ActiveWorkbook.FullName
    sPath = "C:"
    For Each wks In Worksheets
    sFilename = sPath & wks.Name & ".txt"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs _
    FileName:=sFilename, _
    FileFormat:=xlTextMSDOS
    Next
    ActiveWorkbook.SaveAs _
    FileName:=sOriName, FileFormat:=xlNormal
    Application.DisplayAlerts = True
    End Sub

    And it seems to work by putting the cells into a column of text in a notepad file. Have sent that away for approval...I was under the impression they wanted it as a message, which would then be words seperated by spaces in a text file not by an Enter stroke.

    Will let you know when I learn more.
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Concatenating cells (Excel 2000)

    The following snippet of VBA will save the contents of A1:A1500 as a single (long) line of text to a text file :<pre>Sub MakeTextFile()
    Dim strText, c As Range, objFSO, objText
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objText = objFSO.CreateTextFile("Filename.txt", 0)
    For Each c In Range("A1:A1500")
    strText = strText & c.Text & " "
    Next
    With objText
    .Writeline strText
    .Close
    End With
    End Sub</pre>

    Andrew C

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating cells (Excel 2000)

    Andrew,

    This worked GREAT. The other code was doing okay enough, but this creates more of a "message look". I haven't heard back yet from the person I'm requesting it for, but it seems to do what was requested.

    As always,
    I salute you!
    Thank you so very much,

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating cells (Excel 2000)

    Andrew,

    Persons specified earlier replied. This is their comments:

    Nannette,
    This second option is a good one and I think this may work, but there is two additional things I need it to do when importing into notepad. I have enclosed an example of what we are working on. One, when the file imports into notepad it does not wrap the text were I need it. I need the text to wrap after the F column. Two, if possible I still need the columns to align. I have changed the range from a1:f1500. I've tried working this all morning and have come up short on an answer. I need it to import like you see in excel only minus the tabs. Your help is greatly appreciated in this matter.

    Andrew...
    I am enclosing their attachment...I have also given this post address to them so they can keep up with this themselves. I will also peek in to see how it gets solved...it's beyond me. You guys and gals are much better then I at this.

    Thank you,
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Concatenating cells (Excel 2000)

    Hi,
    That sounds to me like you want File-Save As, then in Save As Type, choose Formatted Text (Space delimited) (*.prn).
    Does that work?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating cells (Excel 2000)

    Rory,

    Have passed your information on to the one in need. Your result is perfect as far as I'm concerned. It did exactly what it was supposed to. Why I didn't look further down the Save As Type list is beyond me...stopped at tab delimited text file, and should have pushed further...

    So appreciative,

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Concatenating cells (Excel 2000)

    Nannette,

    Rory's suggestion of Formatted Text (Space delimited) is indeed the most appropriate method in view of the revised requirements.

    For better alignment, you might suggest that the column headers from B2 to F2 be right aligned and that a number format of 0 followed by say 2 spaces be used. ("0 ").


    Edit :

    I intended to suggest that when you select FileSaveAs Formatted Text (Space delimited) (*.prn), you can force a .txt extension by wrapping the file filename in quotation marks ( " ). The whole procedure can be achieved by some code as follows :

    Sub ExportAsText()
    With Range("B2:F2")
    .HorizontalAlignment = xlRight
    Range(.Offset(1, 0), .End(xlDown)).NumberFormat = "0 "
    End With
    ActiveWorkbook.SaveAs _
    Filename:="Filename.txt", _
    FileFormat:=xlTextPrinter
    End Sub

    Replace Filename.txt with the appropriate path and file name for the export file.

    Andrew C

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating cells (Excel 2000)

    Andrew,

    I've never noticed the *.prn option either. From previous experience though, a .prn file is one destined for the default (or selected) printer driver, and contains escape codes etc. that are peculiar to that specific printer (driver). Is this a different file type to the Excel interpretation?

    Alan

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Concatenating cells (Excel 2000)

    Alan,

    Using the .prn option from the File Save As dialog, just creates a plain text image of the document that is being saved. As far ras I know the only codes that might be included are Carriage Returns, Line Feeds, and possibly Tabs.

    When you print a document and select Print To File, then the control codes for the printer selected are included in the file output.

    Andrew

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating cells (Excel 2000)

    Thanks Andrew. It just seemed a bit odd that XL would use the .PRN extension for this purpose. I do recall there being a standard Windows printer driver (generic text only or something) that would seem to do the same sort of thing i.e. produce a pure text file when print to file was used with this driver.

    Alan

Posting Permissions

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