I'm having a little trouble visualizing the layout of your data and
how you're using it, but I think I can point you a little further
along (although this is really the blind leading the blind g). This
is my understanding; please correct me if I'm wrong.
The spreadsheet looks something like this (best viewed with fixed
font):
ScaleName Score ScoreText
ScaleA 1 AA
ScaleA 2 AB
etc. down to
ScaleA 10 AJ
ScaleB 1 BA
ScaleB 2 BB
etc. down to
ScaleB 10 BJ
etc.
In order to pick out a particular ScoreText value to send back to the
Word table, you have to supply values of both ScaleName and Score. You
mentioned only the Score being in the Word table, so how do you know
which ScaleName to choose?
Anyway, assuming your macro or userform code does know the correct
values of ScaleName and Score, it can ask the Excel spreadsheet for
the corresponding ScoreText value.
When I mentioned SQL, I was referring to the expression in quotes in
the OpenRecordset statement. The SQL expression from the example,
which you adapted in your code, says to get all the rows from the
named range in the spreadsheet. What you want to do is extract just
the one row that matches the values of ScaleName and Score selected by
your userform.
To do that, you need to use the optional WHERE clause in the SQL
expression (see
http://www.w3schools.com/sql/sql_where.asp for a brief
explanation of this clause). For example, if the ScaleName is "ScaleA"
and the Score is 2 for the sample data above, then the SQL to select
the corresponding ScoreText value of AB from the spreadsheet would be
"SELECT ScoreText FROM ScaleDescriptors WHERE
ScaleName = 'ScaleA' and Score = 2"
Now, you want to use variables in the expression instead of fixed
values of ScaleName and Score. You also have to remember that the
whole SQL expression is a string, so you build it up by using the &
operator to put substrings together. It gets to looking a little
messy:
Set rs = db.OpenRecordset( _
"SELECT ScoreText FROM ScaleDescriptors WHERE ScaleName = '" _
& strScaleName & "' and Score = " & strScore)
The space-and-underscore at the ends of the first two lines are
"continuation characters" that tell VBA that the whole thing is one
statement. There are single-quote characters just before the
double-quote at the end of the second line, and just after the first
double-quote on the third line -- these are needed to surround the
string value of strScaleName. Because strScore represents a number, it
doesn't need single-quotes.
The other thing in your code that needs to change is that (assuming
every row in the spreadsheet is unique) you should get back exactly
one row in the recordset rs, so you don't need the While...Wend loop.
However, if there is no spreadsheet row containing the selected values
of ScaleName and Score, then rs.EOF will be true (EOF stands for "end
of file"), so you need to check for that before trying to use the
value to avoid getting an error.
Here's a complete working demo macro:
Sub demo()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strScaleName As String
Dim strScore As String
' you would get these values from the user
' through the userform -- these assignments
' are just to get a working example
strScaleName = "ScaleB"
strScore = "7"
Set db = OpenDatabase("C:\Test\LkUpScaleDescriptors.xls", _
False, False, "Excel 8.0")
Set rs = db.OpenRecordset( _
"SELECT ScoreText FROM ScaleDescriptors WHERE ScaleName = '" _
& strScaleName & "' and Score = " & strScore)
If Not rs.EOF Then
' replace this MsgBox with code to
' put rs.Fields(0).Value into the
' Word table as the ScoreText
MsgBox rs.Fields(0).Value
' Else
' MsgBox "No such value"
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
--
Regards,
Jay Freedman
Microsoft Word MVP FAQ:
http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
On Sat, 8 Sep 2007 11:10:14 -0700, AnnieB
wrote:
Thanks Jay,
I used the example at
http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm and established
the link to the Excel workbook and the named range okay, but I just don't
know where to go now...
So far I have:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\Test\LkUpScaleDescriptors.xls", False, False,
"Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `ScaleDescriptors`")
While Not rs.EOF
MsgBox rs.Fields(0).Value
rs.MoveNext
Wend
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
This all worked when I ran the example code and correctly got a message box
with the values of the first column in the spreadsheet. However, I can't work
out how to amend that code as suggested to achieve what I need.
My word table has multiple rows -
Column1 has Score1, Score2 etc. (bookmark(bkm) filled by Userform1)
Column 2 has bkmScaleName1, bkmScaleName2 etc.
My Spreadsheet has 3 columns headed ScaleName, Score, ScoreText.
Each ScaleName has a score between 1 and 10, so there are 10 rows for each
ScaleName as each Score has different ScoreText.
Based on the Score back in my Word table, I need to grab the correct
ScoreText from Excel and insert back in the bkmScaleName.
I don't know how - you mentioned SQL Jay, but I'm floundering...
Any assistance greatly appreciated.