Results 1 to 9 of 9
  1. #1
    JustCallMeAl
    Guest

    Listbox Population Code

    *** GeoffW- Long line split ***

    I would like to have some assistance in making this code run faster. Given the parameters below, it takes six seconds for the userform to show. And that testing is on a PIII, 800 mHz, 192 RAM, which is significantly faster than most of the users of this code have.

    The programming parameters

    1. <LI>Reading from a table in a file

      <LI>Need data in cells in columns 1, 3, 5, and 6

      <LI>Currently, there are 106 rows of information, this can grow

      <LI>I only need data of cells in column 3 in the list box
    Given number 4 above, it may be faster to populate an array, then populate the list box from the one "column" of the array. Or would 4 arrays be even better?

    Here's the code:

    <pre>Public Sub mPopulateSignorList()

    Dim tblThis As Table
    Dim celThis As Cell
    Dim colName As Column
    Dim colInitials As Column
    Dim colPhoneNo As Column
    Dim colEmailAddress As Column
    Dim doc As Document
    Dim lngCellCounter As Long

    Set doc = Documents.Open(FileName:="G:FORMS97Info Inputattyinfo.doc", _
    ConfirmConversions:=False, _
    ReadOnly:=True, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto)

    Set tblThis = doc.Tables(1)
    Set colInitials = tblThis.Columns(1)
    Set colName = tblThis.Columns(3)
    Set colEmailAddress = tblThis.Columns(5)
    Set colPhoneNo = tblThis.Columns(6)

    For lngCellCounter = 2 To colName.Cells.Count
    lstSigner.AddItem (Left$(colInitials.Cells(lngCellCounter).Range.Tex t, _
    Len(colInitials.Cells(lngCellCounter).Range.Text) - 2))
    ' The value "2" at the end of the above line is necessary
    ' to get rid of the end of cell marker and the
    ' end of paragraph marker.
    lstSigner.List(lstSigner.ListCount - 1, 1) = gParseToFirstVBCRorComma _
    (Left$(colName.Cells(lngCellCounter).Range.Text, _
    Len(colName.Cells(lngCellCounter).Range.Text) - 2))
    lstSigner.List(lstSigner.ListCount - 1, 2) = _
    (Left$(colEmailAddress.Cells(lngCellCounter).Range .Text, _
    Len(colEmailAddress.Cells(lngCellCounter).Range.Te xt) - 2))
    lstSigner.List(lstSigner.ListCount - 1, 3) = _
    (Left$(colPhoneNo.Cells(lngCellCounter).Range.Text , _
    Len(colPhoneNo.Cells(lngCellCounter).Range.Text) - 2))
    Next ' lngCellCounter

    doc.Close wdDoNotSaveChanges

    End Sub

    Public Function gParseToFirstVBCRorComma( _
    ByVal strCellContent As String) As String

    ' This function is needed because some of the data in _
    the cells contains commas, paragraph marks (returns) _
    or manual line breaks (vbVerticalTab). The _
    information I need is before whichever comes first _
    of the three choices.

    Dim lngCommaPosition As Long
    Dim lngVBTabPosition As Long

    lngCommaPosition = InStr(strCellContent, ",")
    lngVBTabPosition = InStr(strCellContent, vbVerticalTab)

    If lngCommaPosition > 0 Then
    gParseToFirstVBCRorComma = Left$(strCellContent, lngCommaPosition - 1)
    ElseIf lngVBTabPosition > 0 Then
    gParseToFirstVBCRorComma = Left$(strCellContent, lngVBTabPosition - 1)
    Else
    gParseToFirstVBCRorComma = strCellContent
    End If ' lngCommaPosition and lngVBTabPosition

    End Function</pre>


    Thanks for your consideration and assistance.

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

    Re: Listbox Population Code

    *** GeoffW- Long line split ***
    Hi Al,

    Nice code!
    Here's a different way to do it; seems to run faster.

    Gary

    <pre>Public Sub mPopulateSignorList2()

    Dim tblThis As Table
    Dim aRow As Row

    Dim doc As Document

    Set doc = Documents.Open(FileName:="G:FORMS97Info Inputattyinfo.doc", _
    ConfirmConversions:=False, _
    ReadOnly:=True, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto)

    Set tblThis = doc.Tables(1)

    For Each aRow In tblThis.Rows
    lstSigner.AddItem (Left$(aRow.Cells(1).Range.Text, _
    Len(aRow.Cells(1).Range.Text) - 2))
    ' The value "2" at the end of the above line is necessary
    ' to get rid of the end of cell marker and the
    ' end of paragraph marker.
    lstSigner.List(lstSigner.ListCount - 1, 1) = gParseToFirstVBCRorComma _
    (Left$(aRow.Cells(3).Range.Text, _
    Len(aRow.Cells(3).Range.Text) - 2))

    lstSigner.List(lstSigner.ListCount - 1, 2) = _
    (Left$(aRow.Cells(5).Range.Text, _
    Len(aRow.Cells(5).Range.Text) - 2))

    lstSigner.List(lstSigner.ListCount - 1, 3) = _
    (Left$(aRow.Cells(6).Range.Text, _
    Len(aRow.Cells(6).Range.Text) - 2))

    Next 'aRow

    doc.Close wdDoNotSaveChanges
    Set tblThis = Nothing

    End Sub
    </pre>


  3. #3
    JustCallMeAl
    Guest

    Re: Listbox Population Code

    Thanks for the time you took.

    I was going to use the row collection, but I have a header row that I didn't really know how to deal with. Since this document is only for data purposes, I decided to use your suggestion and make the header row its own table. Hence, I will be using tables(2) instead of tables(1).

    Again, thanks.

  4. #4
    JustCallMeAl
    Guest

    Re: Listbox Population Code

    Charlotte (at least I believe you are the correct person), have you ever experimented with this type or process comparing getting data from a Word table v. an Access database?

    Thanks

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

    Re: Listbox Population Code

    The only time I use Word tables to hold data is when I do a mail merge. And even then, I generally use a text file as the data source rather than a Word table. In the newer versions of Office, there isn't quite such a high overhead using Access as a datasource for Word, but I shy away from it based on its poor performance in earlier versions.
    Charlotte

  6. #6
    JustCallMeAl
    Guest

    Re: Listbox Population Code

    WOW! Talk about blazing speed!

    I converted the Word document containing the table of information to a comma delimited text file. I then rewrote the code using Input #.

    Less than a second to load the same number of items into the list box.

    I have had to take two steps, however, because the Input #1 command didn't seem to like lstSigner.AddItem as the recipient of the field.....(Word 97, SR2)

    Therefore, I loaded the values into an array and then put them in the list box.

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

    Re: Listbox Population Code

    If you use a tab-delimited text file, you can use it directly just as if it were a Word table. That would get around the Input # problems, but I'm not sure what the effect on speed would be ... probably slow it down a bit.

    If it were me, I would definitely load the values into the array, close the text file and then add the items to the listbox . That's the cleanest way, anyhow, and it's what I usually do when I need to load a list or combobox from a file.

    edited immediately after post
    Oh, one other trick would be to create a user-defined type to hold the fields from the input file. Then dim an array of that udt. That way, you can load the array with the whole record at once and just reference the particular element you need while using a single dimension dynamic array. I'm not sure whether there would be a noticeable difference in speed loading the whole record, and I've only used this method with a fixed width file, but it might be worth playing with. It definitely makes the code easier to read, since you can see exactly which element you're referencing instead of having to use a numeric index.
    Charlotte

  8. #8
    New Lounger
    Join Date
    Apr 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Population Code

    Charlotte, I am interested in your user defined type suggestion for reading in a fixed width text file.

    Could you or someone else show a simple sample of what you mean, including how you access the field data from the udt?

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

    Re: Listbox Population Code

    A user-defined type is created like this:

    'user-defined type for household income
    Public Type HouseholdIncome
    IDCode As String * 5 'holds ID
    Income As Currency 'holds household income
    Persons As Integer 'holds number of members
    End Type 'HouseholdIncome

    Then you dim an array like this:

    'public array to hold data
    Public gudtSurvey() As HouseholdIncome

    And you read the file into the array one record at a time, after redimming the array to the appropriate size.

    <pre>Private Sub LoadArray(strDataFile As String)
    'created by Charlotte Foust
    'populate global array of HouseholdIncome
    Dim strPath As String 'holds the current path
    Dim intLoop As Integer 'controls looping through array
    Dim HIncome As HouseholdIncome 'holds each type object

    'get the current application path
    strPath = GetPath()

    'open the file and get the record count
    Open strPath & strDataFile For Random As #1 Len = Len(HIncome)
    mintCount = LOF(1) / Len(HIncome)

    'if there are records, load the array
    If mintCount > 0 Then

    'dimension the array to the current size
    ReDim gudtSurvey(1 To mintCount)

    'loop through the records in the file
    For intLoop = 1 To mintCount

    'get each record and store it
    'to the Survey object
    Get #1, intLoop, HIncome
    With HIncome
    gudtSurvey(intLoop).IDCode = HIncome.IDCode
    gudtSurvey(intLoop).Income = HIncome.Income
    gudtSurvey(intLoop).Persons = HIncome.Persons

    'update the total income aggregate value
    mcurTotalIncome = mcurTotalIncome + HIncome.Income

    'increment the counter for below poverty level
    If HIncome.Income < _
    PovertyLevel(HIncome.Persons) Then
    mintBelowCnt = mintBelowCnt + 1
    End If 'HIncome.Income < _
    PovertyLevel(HIncome.Persons)
    End With 'HIncome
    Next intLoop '= 1 To mintCount

    'close the data file
    Close #1

    End If 'mintCount > 0

    End Sub 'LoadArray()</pre>

    Charlotte

Posting Permissions

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