Using DAO search of an Excel database file

[Roderick]

I'm using Word 2013 and Windows 7 (64-bit)

As you can see, I'm using an Access database table to sequence through the table and pick out the relevant field result based on the label name in the first column. The field name it could search for might be in the column marked French or Spanish or English, and so on across the table.

As an explanation, using this method illustrated below works perfectly. However...my client's techies have come back to me and said "Our users could be using any type of machine and systems - but not earlier than Windows 7 - and we want you to use DAO and an .xls file to get the same information".

Here is the present code:

Sub rxshared_getLabel(control As IRibbonControl, ByRef returnedVal)
    Dim dbRibbonData As DAO.Database
    Dim rdShippers As Recordset
    Dim intRecords As Integer
    Dim myString As String
    Dim strSQL As String
    Dim myLanguage As String
    Dim myFieldCode As String
    Dim rxMyLabel As String

    'control.id comes from my Ribbon XML shared callback
    rxMyLabel = control.id
    
    VarPathLocal = Options.DefaultFilePath(wdUserTemplatesPath)
    VarPathNetwork = Options.DefaultFilePath(wdWorkgroupTemplatesPath)
    
    FullPath = VarPathLocal + "RibbonData.accdb"
    FullPathNet = VarPathNetwork + "RibbonData.accdb"

    If Dir(FullPath) <> "" Then
        inifileloc2 = FullPath

    ElseIf Dir(FullPathNet) <> "" Then
        inifileloc2 = FullPathNet
    Else
    End If

    Set dbRibbonData = OpenDatabase(Name:=inifileloc2)
        
    myLanguage = GetSetting("GA", "Template Language", "Language")
    
    'search the database in the 'Template_Labels' table in the 'French' field for the 'control.id' record
    strSQL = "Select " & myLanguage & " FROM [Template_Labels] where [Field_Code] = '" & rxMyLabel & "'"
    
    Set rdShippers = dbRibbonData.OpenRecordset(strSQL, dbOpenDynaset)
    
    'return the value back to the Ribbon XML
    On Error Resume Next
    returnedVal = rdShippers.Fields(0)

    rdShippers.Close
    dbRibbonData.Close

End Sub

I've copied the Access table data to an Excel spreadsheet and these are the details:

File Name: 'Templates_Database.xls'
Sheet name: 'Template_Labels'
Column A has the title of 'Field_Code' (Column 0)
The range for the data is named: 'Ribbon_Labels'
Column B (Column 1) is named 'English
Column C (Column 2) is named 'French'
and...
Column D (Column 3) is named 'Spanish'

It's no problem for me to get it to search in the appropriate column (1,2 or 3)

I've searched Google and came up with a few ideas but they all diverged away from the challenge I'm trying to resolve explaining irrelevant details which thoroughly confused me.

What References will I have to set in my VBE if using DAO?

Could someone point me in the right direction, please, to change it to what the clients want?

Thanks.

[gmayor]

The following function will read an xls worksheet to an array using DAO. Make of it what you will

Private Function XLS_DAO_FillArray(strDatabase As String, _
                                 strTableName As String) As Variant
Dim dbEng As Object
Dim db As Object
Dim RS As Object
Dim numrecs As Long
    strTableName = strTableName & "$"
    Set dbEng = CreateObject("DAO.DBEngine.120")
    Set db = dbEng.Workspaces(0).OpenDatabase(strDatabase, False, True, "Excel 8.0; HDR=YES;")
    Set RS = db.openrecordset(strTableName)

    With RS
        If .EOF Then GoTo lbl_Exit
        .MoveLast
        numrecs = .RecordCount
        .MoveFirst
    End With
    XLS_DAO_FillArray = RS.GetRows(numrecs)
lbl_Exit:
    RS.Close
    db.Close
    Set RS = Nothing
    Set db = Nothing
    Set dbEng = Nothing
    Exit Function
End Function