Lookup to external file

Kevlarhead

Board Regular
Joined
May 23, 2006
Messages
176
I need to lookup to a .CSV in a similar way to a Vlookup. I could do this with ADO, which I'm reasonably comfortable with using, but I just wondered if anyone knew any easier or simpler ways to accomplish this.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Kevlarhead

Board Regular
Joined
May 23, 2006
Messages
176
Yeah... problem is I don't want to have to insert it into an excel file.

I'm trying to create a set of spreadsheets that require as little user input as possible, as they'll be used in a remote location. They'll be used to record work done by engineers. This system will require some information that may change from day to day (mostly delivery dates and times). I'd like this stuff to be sent out in a daily CSV, and for the end users to drop it to a specific network location and forget about it.

I was really looking to see if there was VLOOkUP equivalent that would let me avoid using ADO. Previous failures to see wood for trees made me think I'd better pick the brains of the assembled MrExcel hordes before jumping into the VBE again...
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Miight be possible using text file access methods.
How is the data laid out ?
What do you want to achieve exactly ?

A few sample lines from the file would be good.
 

Kevlarhead

Board Regular
Joined
May 23, 2006
Messages
176
It's simple grid of information.

Site name | ID | Items @ Site
Aberdeen | AB | 23
Basildon_ | BD | 2

Given a Site ID no, I'd like to lookup the number of items at the site. Eg. Enter AB, get 23. There's other information kept on each line as well (Site address Line 1, Site address Line 2 etc) that I may need to retrieve as well. Basically I want a vlookup with a variable column index, but to an external csv file and triggered by entering the Site ID on a form.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Not a .csv file then ? If it is, change "|" with "," . Try this ...........
Code:
'==========================================================================
'- LOOKUP GET RECORD FROM PIPE DELIMITED TEXT FILE
'- basic code for test purposes
'- NB. At present reads header line as a record. Saves code.
'-     Not a problem because we are doing a lookup.
'- Brian Baulsom August 2007
'==========================================================================
Dim MyTextfile As String    ' source file
Dim IDlookup As String      ' lookup value
'--------------------------------------------------------------------------
'- Fields
Dim Site As String          ' Field 1
Dim ID As String            ' Field 2
Dim Items As String         ' Field 3
'--------------------------------------------------------------------------
'- number of fields to extract (not necessarily the whole line)
Dim FieldCount As Integer
Dim Fieldx As Variant     ' field data
'--------------------------------------------------------------------------
'- line of text (1 record)
Dim TextLine As String      ' line of text
Dim Pipe As String
    
    
'==========================================================================
'- MAIN ROUTINE
'==========================================================================
Sub GET_RECORD()
    '----------------------------------------------------------------------
    '- initialise variables
    MyTextfile = "C:\TEMP\TEST.TXT"
    FieldCount = 3
    ReDim Fieldx(FieldCount)
    Pipe = "|"
    '----------------------------------------------------------------------
    '- Get lookup value
    '- No error check yet. However, will not find invalid value
    '- We do convert to upper case though
    IDlookup = "AB"     ' test value
    IDlookup = UCase(InputBox("Please enter ID", " ID LOOKUP", IDlookup))
    If IDlookup = "" Then Exit Sub
    '----------------------------------------------------------------------
    '- open file
    Open MyTextfile For Input As #1
    '- check each line
    Do Until EOF(1)
        Line Input #1, TextLine
        '-----------------------------------------------------------------
        '- run subroutine
        GetFieldData
        '-----------------------------------------------------------------
        '- return values not strictly necessary. Could use Fieldx(1) .. etc
        Site = Fieldx(1)
        ID = Fieldx(2)
        Items = Fieldx(3)
        '-----------------------------------------------------------------
        '- CHECK FOR MATCH
        If ID = IDlookup Then
            rsp = MsgBox("FOUND ID :  " & IDlookup & vbCr _
                    & "Site    : " & Site & vbCr _
                    & "Items : " & Items)
            GoTo GetOut
        End If
    Loop
    '----------------------------------------------------------------------
    '- NO MATCH MESSAGE
    MsgBox ("NO MATCH FOUND")
    '----------------------------------------------------------------------
GetOut:
    Close #1
End Sub
'============ END OF MAIN ROUTINE =========================================

'==========================================================================
'- SUBROUTINE TO PARSE THE TEXT LINE TO GET FIELDS.
'- Called from main routine
'==========================================================================
Private Sub GetFieldData()
    Dim Ln, c, c1           ' character positions
    Dim f                   ' field number
    '----------------------------------------------------------------------
    Ln = Len(TextLine)
    f = 1
    c1 = 1
    '----------------------------------------------------------------------
    '- character by character
    For c = 1 To Ln
        If Mid(TextLine, c, 1) = Pipe Then
            Fieldx(f) = Trim(Mid(TextLine, c1, c - c1 - 1))
            '- next field
            c1 = c + 1
            f = f + 1
        End If
    Next
    '- last field
    Fieldx(f) = Trim(Mid(TextLine, c1 + 1, Ln - c1))
End Sub
'--------------------------------------------------------------------------
 

Forum statistics

Threads
1,181,406
Messages
5,929,759
Members
436,687
Latest member
Glass of Gin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top