Lookup function in VB6

Question

Hi, I have a table in an excel file called " Employee_States_File".This table contains two columns Name and States. Both columns are filled with data. The province table contains abbreviation of the States such as " NE, WA" and so. I have another excel file called " States_File" which contains a table that has two columns: Abbreviation and FullStateName. This table is considered to be a lookup table to look for the full state names based on the abbreviation. Now, I want to write a code in VB6 so that when the user click a button, All the abbreviation names in the table of the excel file " Employee_States" are changes into the full state names based on the lookup table of the excel sheet " States_File". does it make sense? Please help,

Answer 1

If you are allowed to convert them to csv files (comma delimited text files) you can use the Jet Database engine and do normal SQL joins.

I use this to setup the connection.

Public Function OpenTextConnection(ByVal FileName As String) As Connection
    Dim FSO As FileSystemObject
    Dim DBFolder As String
    Dim TS As TextStream

    Set FSO = New FileSystemObject
    DBFolder = FSO.GetParentFolderName(FileName)

    If FSO.FileExists(FSO.BuildPath(DBFolder, "Schema.ini")) Then
        FSO.DeleteFile (FSO.BuildPath(DBFolder, "Schema.ini"))
    End If

    Set TS = FSO.CreateTextFile(FSO.BuildPath(DBFolder, "Schema.ini"))
    TS.WriteLine "[" & FSO.GetFileName(FileName) & "]"
    TS.WriteLine "Format=CSVDelimited"
    TS.WriteLine "ColNameHeader = True"
    TS.WriteLine "MaxScanRows = 0"
    TS.Close

    Set OpenTextConnection = New Connection


    If FSO.FolderExists(DBFolder) Then
        OpenTextConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFolder & ";Extended Properties=""text;HDR=Yes;FMT=Delimited;"";"
    Else
        MsgBox DBFolder & " Does not exists.", vbExclamation
    End If

End Function

Each file is a table in the connection and you can do SQL joins. Note the example is just a simply open up a table. You can use any valid SQL Syntax.

Dim DB1 As Connection
Dim TB As Recordset
Dim FSO As FileSystemObject
Dim tImport As New DBImportList
Set FSO = New FileSystemObject
Set tImport = New DBImportList
If FSO.FileExists(FileName) Then
    Set DB1 = OpenTextConnection(FileName)
    Set TB = New Recordset

    TB.Open "SELECT * FROM [" & FSO.GetFile(FileName).Name & "]", DB1, adOpenKeyset, adLockOptimistic, adCmdText

End If