Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comma Delimited File (XP)

    I can open a CSV file using the following code. What I would like to do is obtain the data from a field position using variables.

    Code example:
    Open File For Input as #1
    Do While Not EOF(1)
    Line Input #1, LineofText
    Range("A1") = LineofText
    Loop
    Close #1
    End Sub

    LineofText example:
    Cell A1 = 'abcd,efgh,1,2,3,4,5,6,7,8,9,10,11,12


    Is there a function similar to an array that for example would return field 6's data: 4?

    Thanks,
    John

  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: Comma Delimited File (XP)

    You could use a formula like:

    =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),5)) +1,FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),6))-FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),5))-1)

    The 6 is the field positions, the 2 5s are the field positions -1. YOu could store them in an intermediate cell if desired.

    You could also create a custom function if desired.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comma Delimited File (XP)

    Steve,

    How would it look in a custom function? Suppose I need to change the field postion, would it not make sense to write something like this:

    i=6
    Msgbox LineofText(i)

    I know one can reference the field positons from an array as mentioned in my crude example above. I'm not sure how to do it via code since Cell A1 is just a text line with fields seperated by commas.

    Thanks,
    John

  4. #4
    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: Comma Delimited File (XP)

    You can have something like this:

    <pre>Option Explicit
    Function GetPosition(str As String, iPos As Integer)
    Dim sTemp As String
    Dim iStart As Integer
    Dim iEnd As Integer

    sTemp = "," & str & ","
    sTemp = Application.WorksheetFunction. _
    Substitute(sTemp, ",", Chr(1), iPos)
    iStart = InStr(sTemp, Chr(1)) + 1
    iEnd = InStr(iStart, sTemp, ",")
    GetPosition = Mid(sTemp, iStart, iEnd - iStart)
    End Function</pre>


    You can use it in Excel:

    =Getposition(A1,6)

    Or on VB:

    sVar = GetPosition(sInput,6)

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comma Delimited File (XP)

    Steve,

    Great solution.

    Thanks, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    John

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

    Re: Comma Delimited File (XP)

    Here is a somewhat shorter version:
    <code>
    Function GetPosition(str As String, iPos As Integer)
    GetPosition = Split(str, ",")(iPos - 1)
    End Function</code>

  7. #7
    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: Comma Delimited File (XP)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>Much Better than mine...

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>One caveat to your function is that SPLIT is not available in XL97 so it can not be used on machines running XL97.

    Steve

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

    Re: Comma Delimited File (XP)

    True, Split was introduced in Office 2000 VBA.

  9. #9
    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: Comma Delimited File (XP)

    I am still "sensitive" to these issues, since I still run XL97 on my home computer. I tend to work on solutions that will work at home since I still develop many "answers" on my home computer...

    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
  •