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