Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Arrays (Word 97 sr2)

    Hi there!

    I am trying to read from a text file a list of details about different users - there are columns for username, firstname, lastname, email and phonenumber.

    I have successfully managed to read the data from the file, but I am having problems trying to compare the username (read from the registry) and the first column in the array. Ultimately I want to identify if the user exists in the text file - if not I want to prompt the user for their details to be added to the text file (I'll worry about that later) and if the user does exist to pull the rest of the user details from the array to be held in variables that can be used later on...

    Any and all ideas gratefully accepted....

    Cheers!

    Alba

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Arrays (Word 97 sr2)

    Hi Alba,

    One way to do it would be to assign the entire contents of the text file to a string, and then use InStr to check if the username exists in that string:

    <pre>If InStr(strTextFileContents, strUserName) = 0 Then 'username not found in text file contents</pre>

    If there are delimiters between the elements in the text file, it would be best to include those in strUserName - otherwise you could get a false hit (for example if there is a csmithson and you are searching for csmith).

    Gary

  4. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays (Word 97 sr2)

    Thanks for getting back to me Gary.

    That help sort the first part of the problem...but how can I then pick out the details associated with that user? Would it be possible to mark the row number in the array, then display the other entries within that row?

    Thanks again

    Alba

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

    Re: Arrays (Word 97 sr2)

    Are you talking about a VBA array in code or something else? Did you read the text file into a single dimension array?
    Charlotte

  6. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Arrays (Word 97 sr2)

    If your file is very large, but well structured, you might consider using ADO to access it. ADO was not packaged with Office 97, I don't think, but you can download it from Microsoft. ADO has a bit of a learning curve, as do all database access methods, but you can get an idea of what is involved by checking out <!post=Post #133021,133021>Post #133021<!/post>.

    On the other hand, rather than work with the file as a text file, you could use Excel's excellent import wizard (under Get External Data, I think) to give it structure and then read from that.

    Either way, viewing your text file as an ADO Recordset rather than as a huge collection of lines of text provides you with great flexibility in searching and updating.

    If you find yourself stuck on the syntax for an ADO project, the folks in the Access Lounge typically can provide the fastest answers.

  7. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays (Word 97 sr2)

    Thanks - I'll look into it!

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

    Re: Arrays (Word 97 sr2)

    Alba,

    You're right that my solution is useless beyond getting that first result!
    If you are dealing with loads of data, then it's worth trying one of the methods Jefferson has suggested.

    Otherwise, here is some code that may give you ideas on how to extract the data from your array:

    <pre>Sub PlayWithArrays()

    Dim n As Long
    Dim nFoundRow As Long
    Dim strUserName As String
    Dim fNameFound As Boolean
    Dim strDetails(0 To 2, 0 To 4) As String
    'username, first name, last name, e-mail, tel num
    Dim strFirstName As String
    Dim strLastName As String
    Dim strEmail As String
    Dim strTelNum As String

    strUserName = "UserName2"

    strDetails(0, 0) = "UserName1"
    strDetails(0, 1) = "Joe"
    strDetails(0, 2) = "Smith"
    strDetails(0, 3) = "js@something.com"
    strDetails(0, 4) = "333-2222"
    strDetails(1, 0) = "UserName2"
    strDetails(1, 1) = "Jack"
    strDetails(1, 2) = "Brown"
    strDetails(1, 3) = "jb@something.com"
    strDetails(1, 4) = "444-3333"
    strDetails(2, 0) = "UserName3"
    strDetails(2, 1) = "John"
    strDetails(2, 2) = "Jones"
    strDetails(2, 3) = "jj@something.com"
    strDetails(2, 4) = "555-4444"

    For n = 0 To UBound(strDetails, 1) - 1
    If strDetails(n, 0) = strUserName Then
    fNameFound = True
    nFoundRow = n
    Exit For
    End If
    Next 'n

    If Not fNameFound Then
    MsgBox "UserName not found!"
    Exit Sub
    Else
    strFirstName = strDetails(nFoundRow, 1)
    strLastName = strDetails(nFoundRow, 2)
    strEmail = strDetails(nFoundRow, 3)
    strTelNum = strDetails(nFoundRow, 4)

    Debug.Print strUserName
    Debug.Print strFirstName
    Debug.Print strLastName
    Debug.Print strEmail
    Debug.Print strTelNum
    End If

    End Sub
    </pre>

    Gary

  9. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays (Word 97 sr2)

    One thought that just occured to me is that instead of trying to see if the Username exists after you fill your array, do your check WHILE you are filling the array from the text file

    strUserName = The value from the registry

    Start Loop
    Fill Array record with record from text file
    If strUserName = ary(CurrentRecordNumber,ColumnNumber) then
    bolUserExists = true
    End If
    Loop

    If bolUserExists = False then
    Get the Users Info
    End If
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  10. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays (Word 97 sr2)

    You're a genius!!!!

    I'll give it a bash and let you know how I get on....

    Thanks!

  11. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays (Word 97 sr2)

    http://www.wopr.com/cgi-bin/w3t/showthread...sts&Main=185248

    You may want to use my strSplitStringAt function - a general utility function for parsing delimited strings.

  12. #11
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays (Word 97 sr2)

    Thanks to all of you who have helped!

    I've now got it working.....that was only part one of the problem though.....

    Cheers!
    Alba

Posting Permissions

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