Results 1 to 7 of 7
  1. #1
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Populating List Box using CSV file (Word 97/2000 VBA)

    I have a problem trying to populate a list box in a user form from a comma separated file. On the simple test file it works fine. Once the records mount up then errors come from all directions. I am guessing that the problem is array length (or string length) limits. Does anyone know what the limits actually are? The array is going to be huge as the text file is an address book exported from Lotus Notes with 16 fields and potentially around 2,000 records.
    The code I am using is
    <pre> Open sPeopleFile For Input As #1
    'count the number of lines in the file
    Do While Not EOF(1)
    Line Input #1, stemp
    iCounter = iCounter + 1
    Loop
    Close #1 ' Close file.

    'now dimension the array with the number of rows and columns
    'note that the row count start at 0, column count at 1
    ReDim sArray(iCounter - 1, 16)
    'now reset the counter to zero for reuse
    iCounter = 0
    'read the data contained in the file
    Open sPeopleFile For Input As #1
    'On Error GoTo InputFileError
    Do While Not EOF(1)
    Input #1, sArray(iCounter, 0), _
    sArray(iCounter, 1), _
    sArray(iCounter, 2), _
    sArray(iCounter, 3), _
    sArray(iCounter, 4), _
    sArray(iCounter, 5), _
    sArray(iCounter, 6), _
    sArray(iCounter, 7), _
    sArray(iCounter, 8), _
    sArray(iCounter, 9), _
    sArray(iCounter, 10), _
    sArray(iCounter, 11), _
    sArray(iCounter, 12), _
    sArray(iCounter, 13), _
    sArray(iCounter, 14), _
    sArray(iCounter, 15) ' Read full line of data.
    iCounter = iCounter + 1
    Loop
    Close #1 'Close file.</pre>

    Andrew Lockton, Chrysalis Design, Melbourne Australia

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Populating List Box using CSV file (Word 97/2000 VBA)

    You didn't explain what kind of errors or what kind of help you need. Are you asking for help in populating the array, or asking about limits on its size, or are you asking how to populate the listbox from the array?
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating List Box using CSV file (Word 97/2000 VBA)

    Andrew,

    I've been playing around a bit with the fso. It's pretty interesting. From the fsoClass I'm putting together:

    Property Get LineCount() As Long
    Set ts = fso.OpenTextFile(pFullName, 1) '1 = ForReading
    ts.ReadAll
    LineCount = ts.Line
    End Property

    As a standalone function:

    Function LineCount(strFileName) as long
    dim fso as object
    dim ts as object
    Set fso = CreateObject("Scripting.FileSystemObject")
    set ts = fso.opentextfile(strFileName,1) ' 1 = ForReading
    ts.readall
    LineCount = ts.line
    end function

    As always, if you want the object model at your fingertips, add a ref to MS Scripting Runtime.

    Now, from there you can use ts.read and ts.skip to parse
    your line and put the pieces into the array. The fso makes
    sense 'cause it can handle just about anything you throw at
    it. Here's a <A target="_blank" HREF=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsproline.asp>reference</A>. Drill down and have fun.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Populating List Box using CSV file (Word 97/2000 VBA)

    Hi Charlotte

    Populating the list box from the array isn't the problem.

    I would like to know what the limits in array/string size are. I have seen <A target="_blank" HREF=http://link>your post </A> replying to Kevin's limit question but I haven't worked out whether this is applies to the input line length in total or to a single data string in the line. I am also interested to know if there is a faster, more robust way of inputting the data.

    The error I get with larger files is a "9" Subscript out of range. I am not convinced that it is a size limit issue.

    Another error I am getting today is that it populates the list box with lots of numbers instead of the text that was in the file.

    Should I be using an excel file as my source instead of a CSV file? I assume this would be a lot slower to run.

    -------------- Edited later ------------------
    I have solved the errors now. Some of the records didn't have as many fields as others. The numbers were coming in when I used the WordBasic.SortArray command which scrambled larger data sets. The file is not causing me anymore problems now. Although I am still intrigued as to where the string limit exists. Is the string limit 63K per field, record or total array (input file) length?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Populating List Box using CSV file (Word 97/2000 VBA)

    Were you referring to <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=vb&Number=94147&page=&v iew=&sb=&o=&vc=1#Post94147>this post</A>? Your link is empty so I'm not sure if we're talking about the same thread. If it is, I think the answer to your question is "yes". <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    The subscript out of range error happens when you try to reference an element of the array that is beyond the ubound. Since you've already counted the records, why not just use a For loop instead of your Do While Not EOF (i.e., For intLoop = 1 to intCounter), since that can't go any higher than the ubound of the array?

    I think the Excel file would be much slower than a text file for this purpose because you would have the additional overhead of the Excel process in order to read it. The only other thing I could think of would be to define a user-defined type to hold the data coming in and then use that to populate the array elements. That's what I do with fixed width records, but I'm not sure it would even work with a comma-delimited string.
    Charlotte

  6. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Populating List Box using CSV file (Word 97/2000 VBA)

    Kevin,

    (A bit off topic) question about CreateObject:

    Set fso = CreateObject("Scripting.FileSystemObject")
    vs.
    (set reference to MS scripting runtime and then)
    Set fso = New FileSystemObject

    The VBScript code samples on the MSDN site use CreateObject because (if I understand it right) there's no early binding when using scripting.

    Meanwhile, every reference I've read about VB/VBA/COM goes on about early binding vs. late binding, and how early binding is always better because it runs so much faster (not to mention the benefit of having the objects in the development environment).

    But, I do see a lot of VB/VBA code around that uses CreateObject rather than early binding.

    Question: are there any benefits to not using early binding (that I haven't learned about yet) that counterbalance the speed disadvantage?
    Is CreateObject more portable (i.e. you set a reference to scrrun.dll which is located on D: on your machine; a laptop user runs your code and has that .dll on C: - does the reference work anyway?).

    Gary

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating List Box using CSV file (Word 97/2000 VBA)

    Gary,

    I, too, would prefer the early binding; however, the reference to the Scripting Runtime is not necessary if you use late. I don't like releasing app updates with references that work on some and not on other PCs. Don't ask me why, but all the PCs work fine if I don't include the reference.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Posting Permissions

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