Your opinion on searching a CSV file

  • There are so many experts on this board that I am hopeful one of you will take the time to give me some direction on how to proceed.


    I have a CSV file of the streets in the city of Ajax, Ontario, AJAX.CSV, with three fields, the first which is null (x), the second which contains a street name, and the third which contains a route number.


    Here is a sample of what the AJAX.CSV file looks like:




    I would like to add data to another of a dozen ROUTE files, using the AJAX.CSV file as the input file. The user would type in the STREET NAME and the AJAX.CSV file is accessed, then the correct ROUTE number (column 3) is applied to the record and added to that route.


    Here's my code, no laughter please ...



    Here's my problem ... people will sometimes type in the wrong suffix, for example instead of typing in "BIRD CR" exactly they will type in "BIRD CRES", or "BIRD CRS" and the program simply exits at NOTFOUND .


    Is there any way I can somehow make it so if someone mispells the suffix, or does not follow the convention, the program will still find the correct STREET and ROUTE?


    As always, thank you in advance for your time.

  • Re: Your opinion on searching a CSV file


    William,


    What version of VBA are you using? This code looks old.....


    Regards
    Weasel

  • Re: Your opinion on searching a CSV file


    Weasel, You probably mean that my code looks like crap, rather than old.


    I am laboring under earlier versions of GW Basic and applying the same to VB.


    My version of Excel is 2002.

  • Re: Your opinion on searching a CSV file


    No, not at all. I am a relative new comer to VBA (or any type of code) and have never come across the syntax before.


    If most of the confusion occurs with street suffixs you could make the suffix a seperate entry using a list of all official abbreviations ie. CR, ST, PDE, CCT, RD and then concatenate the two entries as your search string.


    Cant help you with the code as I dont understand it :O)


    Cheers
    Weasel

  • Re: Your opinion on searching a CSV file


    Hi William


    Try the GetOpenFile Name Method. For example

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!