Roderick
11-06-2017, 10:55 AM
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.
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.