I wish people would make up their minds!! (Sorry, my rant!)
Let me explain:
I've created a multi-lingual template (English, French, Spanish) for the user to choose their selected language. When doing so, the template queries an Access database and populates all the Ribbon Bar labels, tips and supertips through the XML.
This is using DAO - and it works like a dream. Below is the existing code for just the Ribbon labels. The other two follow the same pattern.
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
Dim inifileloc2 As String
'control.id comes from my Ribbon XML shared callback
rxMyLabel = control.id
inifileloc2 = “FullPath” + “\” + “RibbonData.accdb”
Set dbRibbonData = OpenDatabase(Name:=inifileloc2)
'searches the Registry for the set language
myLanguage = GetSetting("GA", "Template Language", "Language")
‘For this example myLanguage is set at “English”
'search the database in the 'Template_Labels' table in the 'language' 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
Now comes the crunch: the client has - to cut a long story short - said "We want you to use an .xlsx file and access it through ADO! Why, when it's working so well? But that's another thing...
I've now got to convert the above code to use the client's requirements.
Thanks to Greg Maxey, who had previously made a post dealing with (sort of) just this subject, I have been trying to make the changes but have come to a grinding halt as I've run out of ideas on what to do next.
So far I've got this far:
Sub rxshared_getLabel(control As IRibbonControl, ByRef returnedVal)
'Requires reference to the "Microsoft ActiveX Data Object 2.8 Library."
Dim oConn As New ADODB.Connection
Dim oRecSet As New ADODB.Recordset
Dim strConnection As String
Dim strRange As String
Dim lngCount As Long
'control.id comes from my Ribbon XML shared callback
rxMyLabel = control.id
p_strPath = “FullPath”
strRange = "Template_Labels & “$]"
Set oConn = CreateObject("ADODB.Connection")
'Suppress first row.
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & p_strPath & "RibbonData.xlsx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
oConn.Open ConnectionString:=strConnection
Set oRecSet = CreateObject("ADODB.Recordset")
'Read the data from the worksheet.
oRecSet.Open "SELECT * FROM [" & strRange, oConn, 2, 1
With oRecSet
.MoveLast
'Get count.
lngCount = .RecordCount
.MoveFirst
End With
.....
Where I'm struggling is in finishing it off: How can I read the label for the Ribbon (column 0) and then read the correct label text from column 2 (English) and pass this back to rxMyLabel at the top pf the procedure?
Could someone point me in the right direction, please?
Thanks.
Roderick