Update Fields From .txt File

    Hi,


    I need to update some fields in my excel sheet from a .txt file (comma or space separated), based on matching values of a key field in both files. I do not want to import the file into excel and do a vlookup.


    I am thinking of a macro that prompts the user for the file location ( txt file) and then scans the txt file and updates the fields.


    Would anyone know how to go about doing this or have any code I can use?


    Thanks!

    :angrypc:

    Re: Update Fields From .txt File


    if you know at what position in a line of the text file your key word is to be found and also the the position of the value to be written in your xl file then you can use something like this:


    Code
    FName = path_and_filename_of_txt_file
        Open FName For Input Access Read As #1
            While Not EOF(1)
                Line Input #1, WholeLine
                If Mid(WholeLine, 2, 6) = SEARCHKEY Then
                    Cells(RowNdx, ColNdx).Value = Mid(WholeLine, 22, 7)
                    RowNdx = RowNdx + 1
                 End If
            Wend


    The above writes every found value onto a new line. That might have to be altered for you of course.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: Update Fields From .txt File


    i have been able to use this code. however, need help to solve 2 more problems:-


    1) what is the code to close a text file?
    2) I need to simulatneously open 2 text files and read data from them. is this possible?


    Thanks!

    :angrypc:

Participate now!

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

Register Yourself Login