Lookup to external file

[Kevlarhead]

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.

 

[BrianB]

Once a .csv is in an Excel worksheet can use VLOOKUP as usual.

Coincidentally I have just uploaded a VBA version

http://www.mrexcel.com/board2/viewtopic.php?t=284842

 

[Kevlarhead]

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]

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]

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]

Not a .csv file then ? If it is, change "|" with "," . Try this ...........

'==========================================================================
'- 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
'--------------------------------------------------------------------------