Results 1 to 2 of 2
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Specify length of Input Row (XP/SR-1)

    My customer has a text file that has to be converted before the rest of programming can commence. I tried using
    <pre> Workbooks.OpenText Filename:= _
    "Cocuments and SettingscontractMy DocumentsBCKS0104", Origin:=xlWindows _
    , StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array( _
    18, 1), Array(49, 1), Array(51, 1), Array(66, 1), Array(81, 1), Array(88, 1), _
    Array(106, 1), _
    Array(121, 1), Array(137, 1), Array(153, 1), Array(168, 1), Array(183, 1), _
    Array(199, 2), _
    Array(210, 1), Array(222, 1), Array(238, 1))</pre>

    but it puts those stupid squre things in the place of spaces.

    I tried writing a Do...While to extract the row but found that many lines are of different lengths. Is there a way to force the length of a input row so that it does not wrap around? Or do I need to do my For...Next a different way?

    <pre>Sub GetMaxwellData()
    Dim InputRow As String, DataArray, LengArry
    Dim NextRow As Long, X As Long, ActBook
    NextRow = 1
    Close #1
    DataArray = Array(1, 18, 50, 52, 67, 82, 92, 107, 122, _
    133, 154, 169, 184, 200, 210, 223)
    LengArry = Array(18, 30, 2, 14, 14, 8, 14, _
    14, 14, 20, 14, 14, 15, 9, 12, 15)
    Workbooks.Add
    ActBook = ActiveWorkbook.Name
    Application.ScreenUpdating = False
    Open "Cocuments and SettingscontractMy " _
    & "DocumentsBCKS0104" For Input As #1
    Do While Not EOF(1)
    Workbooks(ActBook).Activate
    Input #1, InputRow
    <font color=red>Debug.Print Len(InputRow)</font color=red>
    For X = 0 To 15
    Cells(NextRow, X + 1) = Mid(InputRow, DataArray(X), LengArry(X))
    <font color=red>Debug.Print Cells(NextRow, X + 1).Address & vbTab _
    & InputRow & vbTab & _
    DataArray(X) & vbTab & LengArry(X)</font color=red>
    Next X
    NextRow = NextRow + 1
    LoopY:
    Loop
    Application.ScreenUpdating = True
    Close #1
    MsgBox "Completed extracting Maxwell text file to Excel", vbOKOnly, "Success"

    End Sub
    </pre>

    Alan

  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: Specify length of Input Row (XP/SR-1)

    Those "stupid square things" are probably part of the source file and not added by excel. Excel indicates some of the "non-printable characters". They are most likely tabs (ASCII 9), LineFeeds(ASCII 10) or carriage returns (ASCII 13), though there are others.

    You could use find/replace to get rid of them, use the CLEAN function (which removes all non-prinatble characters) to get rid of them line-by-line if you are reading the entire line in VB or use it after importing. You could even use find/replace after importing to remove them enmass. It could even be done as part of the macro by looping thru the list of ascii codes to remove and doing a find/replace on each [FYI CLEAN eliminates the Ascii chars 1-31 and also 129, 141, 143, 144, and 157]

    Steve

Posting Permissions

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