Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export To Text File (2002)

    Dear colleagues,
    Need VBA to do this: See attached spreadsheet extract. I need to create a output ACII textfile like this:

    ITEM: 1 REF: 4371 TYP: 772 ATA:21 SUB:20 CAT: Pp MMYY: 04/03
    SUBJ: Cat: Red. Significant exceedance.

    SOLN: Present solution.
    BY: PETERNIX ODATE: 07/05/03 CDATE:
    -------------------------------------------------------------------------
    ITEM: 2 REF: 4372 TYP: 772 ATA:21 SUB:60 CAT: Pp MMYY: 04/03
    SUBJ: Cat: WATCH. First ALERT.

    SOLN: Present solution.
    BY: THATCHER ODATE: 07/05/03 CDATE:
    -------------------------------------------------------------------------
    ITEM: 3 REF: 4373 TYP: 772 ATA:21 SUB:70 CAT: Pp MMYY: 04/03
    SUBJ: TSI/70/SI/03/003 : To inspect gap btw new door & post asy,& the
    engagement btw dr latch & strike asy for correct rigging due to several
    new enhanced security dr reqd high pushing force to open.

    SOLN: Due to fwd outflow vlv status msg. MRA & MRK fixed by outflow vlv
    rplmt.MRH by CB resetting. NFR.Present solution.
    BY: ALANISBS ODATE: 07/05/03 CDATE: 30/06/03
    ---------------------------------------------------------------------------------------------------

    The words preceding the colon : are column headings. The words after the colon are cell contents. The above shows 3 rows of spreadsheet data. The VBA will traverse from A2 cellcontents to L2 cellcontents THEN from A3 cellcontents to L3 cellcontents till A* is BLANK. The textfile line max width = 75 char. IF ColHeading + CellContents >75, CellContents got to word wrap. CellContents may >75 at Col I and Col J only. Font = Courier New 10. IF detailed code is laborious, rough VBA code is sufficient. Thank you for sharing your expertise. Faithfully, Selva.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Export To Text File (2002)

    This should create a worksheet with your output. Modify as needed

    Steve

    <pre>Option Explicit
    Sub SelvaText()
    Dim wksSource As Worksheet
    Dim wks As Worksheet
    Dim iCol As Integer
    Dim lRow As Long
    Dim x As Long
    Dim sSubStr As String
    Dim sStr As String
    Dim iSubStart As Integer
    Dim iLen As Integer
    Dim iMaxLen As Integer

    iMaxLen = 75
    Set wksSource = Worksheets("sheet1")
    Set wks = Worksheets.Add

    lRow = 2
    x = 0
    Do While wksSource.Cells(lRow, 1) <> ""
    x = x + 1
    wks.Cells(x, 1).Value = ""
    For iCol = 1 To 7
    wks.Cells(x, 1).Value = wks.Cells(x, 1).Value & _
    wksSource.Cells(1, iCol) & ": " & _
    wksSource.Cells(lRow, iCol) & " "
    Next iCol
    For iCol = 9 To 10
    x = x + 1
    sStr = wksSource.Cells(1, iCol) & _
    ": " & wksSource.Cells(lRow, iCol)
    'remove any extra spaces linefeeds
    sStr = Application.WorksheetFunction.Trim( _
    Application.WorksheetFunction.Substitute(sStr, Chr(10), " "))

    sSubStr = sStr
    iSubStart = 1
    iLen = iMaxLen
    Do While Len(sSubStr) > iMaxLen
    sSubStr = Mid(sStr, iSubStart, iLen)
    Do Until Right(sSubStr, 1) = " "
    iLen = iLen - 1
    sSubStr = Mid(sStr, iSubStart, iLen)
    Loop

    wks.Cells(x, 1).Value = Mid(sStr, iSubStart, iLen)
    x = x + 1
    iSubStart = iSubStart + iLen
    sSubStr = Mid(sStr, iSubStart, Len(sStr))
    iLen = iMaxLen
    Loop
    wks.Cells(x, 1).Value = Mid(sStr, iSubStart, iLen)

    x = x + 1
    Next iCol
    wks.Cells(x, 1).Value = wksSource.Cells(1, 11) & ": " & _
    wksSource.Cells(lRow, 11) & " " & _
    wksSource.Cells(1, 8) & ": " & wksSource.Cells(lRow, 8) & " " & _
    wksSource.Cells(1, 12) & ": " & wksSource.Cells(lRow, 12)

    x = x + 1
    wks.Cells(x, 1).Value = Application.WorksheetFunction.Rept("-", 73)
    lRow = lRow + 1
    Loop
    wks.Cells(x, 1).Value = wks.Cells(x, 1).Value & Application.WorksheetFunction.Rept("-", 26)
    End Sub</pre>


  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Export To Text File (2002)

    Since macropod mentions the "carriage returns" internally in the cells:
    I chose to remove them and also any "extra spaces" that were in the text before I started doing the word-wrap sequence.

    If you do NOT want to do this remove the line with the TRIM (for the spaces) and SUBSTITUTE [for the carriage return = chr(10)] functions

    Steve

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Export To Text File (2002)

    Hi Selva,

    Here's a solution that doesn't require vba. The new sheet2 has a formula in row 1 that can be copied down as far as needed (I copied it down to row 3 to match your data).

    Once you've copied the formula down for as many rows as you need, do a File|Save As with sheet2 open and save it as a text file. Ignore the warnings along the way and you'll have a text file formatted as per your request (minus the word wrap), with quote marks at the start and end of each record set. You can delete these if needed.

    Cheers

    PS: One minor change you'll need to make is to not put carriage returns in any of the source data cells - your original cell I4 had two of them.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export To Text File (2002)

    Excellent results! Thanks a million. FYI: I was exporting the sheet to an Access table and THEN create a report THEN export as a text report. The result was really bad with just too many spaces everywhere. I take this and paste into Word and use REPLACE function to cull the spaces. This is just to let you know how much time and effort you saved me. Once again, great work and my grateful appreciation. Selva. (Thanks for spotting the returns and stuff in Cells. They should not be there. Result of 'typist' work in PC!)

  6. #6
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export To Text File (2002)

    Thanks a million for such a great formula based solution. Works great. Yes, 'returns' and spaces should not be there. Thanks for spotting these. Selva.

Posting Permissions

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