[AnnieB]

Apologies in advance for the long-winded explanation!
I have created an automated Word template. As part of a report, itemised
test results are entered into tables and the results for each table summed
and averaged. I have managed all this with userform, bookmarks and calculated
formulae and all fields update on Cmd_click OK.
It works so well that instead of the current generic text for each item, the
end users would now like specific descriptor text entered based on each item
score. The text is stored in an Excel workbook. The columns are 'ItemNo",
"ItemScore", "ItemText".
If I create a bookmark in my Word table - say "Item1Text" based on "Item1"
and "Item1Score" (this a bookmark filled from the userform), how do I get the
Item1Text data from Excel? Using Access as the data store is not an option,
as some end users do not have it installed. Through looking around I found
how to open a link to the spreadsheet through DDEInitiate and DDERequest to
get a specific cell, but how can I force a LkUp situation?

I'd appreciate any help I can get!


---
AnnieB
Basic Babe in the Woods

[Jay Freedman]

I think you can adapt the code in
http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm.
Unfortunately I'm pretty much illiterate in SQL, but I believe you can
modify the SELECT statement to get just the row you want.

--
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 Fri, 7 Sep 2007 19:00:02 -0700, AnnieB
wrote:

Apologies in advance for the long-winded explanation!
I have created an automated Word template. As part of a report, itemised
test results are entered into tables and the results for each table summed
and averaged. I have managed all this with userform, bookmarks and calculated
formulae and all fields update on Cmd_click OK.
It works so well that instead of the current generic text for each item, the
end users would now like specific descriptor text entered based on each item
score. The text is stored in an Excel workbook. The columns are 'ItemNo",
"ItemScore", "ItemText".
If I create a bookmark in my Word table - say "Item1Text" based on "Item1"
and "Item1Score" (this a bookmark filled from the userform), how do I get the
Item1Text data from Excel? Using Access as the data store is not an option,
as some end users do not have it installed. Through looking around I found
how to open a link to the spreadsheet through DDEInitiate and DDERequest to
get a specific cell, but how can I force a LkUp situation?

I'd appreciate any help I can get!


---
AnnieB
Basic Babe in the Woods

[AnnieB]

Hi Jay,
Thanks for your response. I had seen this reference and I can try that, but
will end users also need to have that DAO reference?
--
AnnieB
Basic Babe in the Woods



"Jay Freedman" wrote:

I think you can adapt the code in
http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm.
Unfortunately I'm pretty much illiterate in SQL, but I believe you can
modify the SELECT statement to get just the row you want.

--
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 Fri, 7 Sep 2007 19:00:02 -0700, AnnieB
wrote:

Apologies in advance for the long-winded explanation!
I have created an automated Word template. As part of a report, itemised
test results are entered into tables and the results for each table summed
and averaged. I have managed all this with userform, bookmarks and calculated
formulae and all fields update on Cmd_click OK.
It works so well that instead of the current generic text for each item, the
end users would now like specific descriptor text entered based on each item
score. The text is stored in an Excel workbook. The columns are 'ItemNo",
"ItemScore", "ItemText".
If I create a bookmark in my Word table - say "Item1Text" based on "Item1"
and "Item1Score" (this a bookmark filled from the userform), how do I get the
Item1Text data from Excel? Using Access as the data store is not an option,
as some end users do not have it installed. Through looking around I found
how to open a link to the spreadsheet through DDEInitiate and DDERequest to
get a specific cell, but how can I force a LkUp situation?

I'd appreciate any help I can get!


---
AnnieB
Basic Babe in the Woods

[Jay Freedman]

I'm pretty sure the reference is saved in the template with the rest
of the macro code.

On Fri, 7 Sep 2007 19:44:01 -0700, AnnieB
wrote:

Hi Jay,
Thanks for your response. I had seen this reference and I can try that, but
will end users also need to have that DAO reference?
--
AnnieB
Basic Babe in the Woods



"Jay Freedman" wrote:

I think you can adapt the code in
http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm.
Unfortunately I'm pretty much illiterate in SQL, but I believe you can
modify the SELECT statement to get just the row you want.

--
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 Fri, 7 Sep 2007 19:00:02 -0700, AnnieB
wrote:

Apologies in advance for the long-winded explanation!
I have created an automated Word template. As part of a report, itemised
test results are entered into tables and the results for each table summed
and averaged. I have managed all this with userform, bookmarks and calculated
formulae and all fields update on Cmd_click OK.
It works so well that instead of the current generic text for each item, the
end users would now like specific descriptor text entered based on each item
score. The text is stored in an Excel workbook. The columns are 'ItemNo",
"ItemScore", "ItemText".
If I create a bookmark in my Word table - say "Item1Text" based on "Item1"
and "Item1Score" (this a bookmark filled from the userform), how do I get the
Item1Text data from Excel? Using Access as the data store is not an option,
as some end users do not have it installed. Through looking around I found
how to open a link to the spreadsheet through DDEInitiate and DDERequest to
get a specific cell, but how can I force a LkUp situation?

I'd appreciate any help I can get!


---
AnnieB
Basic Babe in the Woods


--
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.

[AnnieB]

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.
--
AnnieB
Basic Babe in the Woods



"Jay Freedman" wrote:

I'm pretty sure the reference is saved in the template with the rest
of the macro code.

On Fri, 7 Sep 2007 19:44:01 -0700, AnnieB
wrote:

Hi Jay,
Thanks for your response. I had seen this reference and I can try that, but
will end users also need to have that DAO reference?
--
AnnieB
Basic Babe in the Woods



"Jay Freedman" wrote:

I think you can adapt the code in
http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm.
Unfortunately I'm pretty much illiterate in SQL, but I believe you can
modify the SELECT statement to get just the row you want.

--
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 Fri, 7 Sep 2007 19:00:02 -0700, AnnieB
wrote:

Apologies in advance for the long-winded explanation!
I have created an automated Word template. As part of a report, itemised
test results are entered into tables and the results for each table summed
and averaged. I have managed all this with userform, bookmarks and calculated
formulae and all fields update on Cmd_click OK.
It works so well that instead of the current generic text for each item, the
end users would now like specific descriptor text entered based on each item
score. The text is stored in an Excel workbook. The columns are 'ItemNo",
"ItemScore", "ItemText".
If I create a bookmark in my Word table - say "Item1Text" based on "Item1"
and "Item1Score" (this a bookmark filled from the userform), how do I get the
Item1Text data from Excel? Using Access as the data store is not an option,
as some end users do not have it installed. Through looking around I found
how to open a link to the spreadsheet through DDEInitiate and DDERequest to
get a specific cell, but how can I force a LkUp situation?

I'd appreciate any help I can get!


---
AnnieB
Basic Babe in the Woods


--
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.

[Jay Freedman]

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.

[AnnieB]

Hi Jay,

Thanks so much - you certainly have helped me along the way! You visualised
my spreadsheet very well indeed. I was able to get the selected text back
into the right place in my Word doc using your sample code.
I just need to play around a bit more so I can have the value of a named
reference as my strScore instead of specifying a number.
If I put your ameded code into my userform as part of the cmd_click OK
event, can I use the formfield txtScaleScore.value that the user input as the
parameter for the strScore value? These formfield values will also populate
bookmarks in the Word table on the click_OK event.
Sorry, I know it's very difficult when you don't have a working example of
the table in front of you and the person describing it can't articulate
clearly!!


---
AnnieB
Basic Babe in the Woods



"Jay Freedman" wrote:

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.

[Jay Freedman]

Yes, you can replace my line

strScore = "7"

with

strScore = txtScaleScore.Text

and the SELECT statement will work as before, _if_ the user entered a
value in the txtScaleScore field that does occur in the Score column
of the spreadsheet. If they entered letters, negative numbers, 0, or
11 or greater, then the SELECT will return rs.EOF = True (the "not
found" condition).

There are two ways to handle this. The poor way is to accept whatever
the user entered, try to use it in the SELECT, and complain to the
user when it fails, forcing them to try again. And an even poorer
strategy is to complain but not tell the user what was wrong with
their entry. :-(

The better way is to have your code validate the user's entry to be
sure it's within the expected range. If the test fails, return the
cursor to the appropriate field of the userform and exit from the OK
event procedure.

Private Sub cmdOK_Click()
Dim strScore As String
Dim lngScore As Long
' and other declarations

strScore = Trim(txtScaleScore.Text)
lngScore = Val(strScore) ' 0 if not a number

'validate the score
If ((lngScore 1) Or (lngScore 10)) Then
MsgBox Prompt:=strScore & " is not a valid score", _
Title:="Score Error"
txtScaleScore.SetFocus
Exit Sub
End If

' if we get here, the score is OK,
' so proceed with the rest of your code...
End Sub

Look up the Val and Trim functions and the .SetFocus method in the VBA
Help to see what they do (put your cursor on the word in the code
window and press F1).

--
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 Sun, 9 Sep 2007 05:52:00 -0700, AnnieB
wrote:

Hi Jay,

Thanks so much - you certainly have helped me along the way! You visualised
my spreadsheet very well indeed. I was able to get the selected text back
into the right place in my Word doc using your sample code.
I just need to play around a bit more so I can have the value of a named
reference as my strScore instead of specifying a number.
If I put your ameded code into my userform as part of the cmd_click OK
event, can I use the formfield txtScaleScore.value that the user input as the
parameter for the strScore value? These formfield values will also populate
bookmarks in the Word table on the click_OK event.
Sorry, I know it's very difficult when you don't have a working example of
the table in front of you and the person describing it can't articulate
clearly!!


---
AnnieB
Basic Babe in the Woods



"Jay Freedman" wrote:

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.

[AnnieB]

Dear, wonderful Jay - Bless you!
The whole household heard me whoop for joy when the code ran and all my
tables have scores and descriptors right where they should be.

Thank you for all your patience and your clear instructions. This has been a
pretty steep learning curve for me, but thanks to you I now have a great
little automated report writer. I have learnt so much during this process and
I'm looking forward to playing around and streamlining the process even more.
Thanks again!

Kind regards,
--
AnnieB
Basic Babe in the Woods



"Jay Freedman" wrote:

Yes, you can replace my line

strScore = "7"

with

strScore = txtScaleScore.Text

and the SELECT statement will work as before, _if_ the user entered a
value in the txtScaleScore field that does occur in the Score column
of the spreadsheet. If they entered letters, negative numbers, 0, or
11 or greater, then the SELECT will return rs.EOF = True (the "not
found" condition).

There are two ways to handle this. The poor way is to accept whatever
the user entered, try to use it in the SELECT, and complain to the
user when it fails, forcing them to try again. And an even poorer
strategy is to complain but not tell the user what was wrong with
their entry. :-(

The better way is to have your code validate the user's entry to be
sure it's within the expected range. If the test fails, return the
cursor to the appropriate field of the userform and exit from the OK
event procedure.

Private Sub cmdOK_Click()
Dim strScore As String
Dim lngScore As Long
' and other declarations

strScore = Trim(txtScaleScore.Text)
lngScore = Val(strScore) ' 0 if not a number

'validate the score
If ((lngScore 1) Or (lngScore 10)) Then
MsgBox Prompt:=strScore & " is not a valid score", _
Title:="Score Error"
txtScaleScore.SetFocus
Exit Sub
End If

' if we get here, the score is OK,
' so proceed with the rest of your code...
End Sub

Look up the Val and Trim functions and the .SetFocus method in the VBA
Help to see what they do (put your cursor on the word in the code
window and press F1).

--
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 Sun, 9 Sep 2007 05:52:00 -0700, AnnieB
wrote:

Hi Jay,

Thanks so much - you certainly have helped me along the way! You visualised
my spreadsheet very well indeed. I was able to get the selected text back
into the right place in my Word doc using your sample code.
I just need to play around a bit more so I can have the value of a named
reference as my strScore instead of specifying a number.
If I put your ameded code into my userform as part of the cmd_click OK
event, can I use the formfield txtScaleScore.value that the user input as the
parameter for the strScore value? These formfield values will also populate
bookmarks in the Word table on the click_OK event.
Sorry, I know it's very difficult when you don't have a working example of
the table in front of you and the person describing it can't articulate
clearly!!


---
AnnieB
Basic Babe in the Woods



"Jay Freedman" wrote:

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.

[Jay Freedman]

Wonderful! Soon you're going to have to change your tag line...

--
Jay

AnnieB wrote:
Dear, wonderful Jay - Bless you!
The whole household heard me whoop for joy when the code ran and all
my tables have scores and descriptors right where they should be.

Thank you for all your patience and your clear instructions. This has
been a pretty steep learning curve for me, but thanks to you I now
have a great little automated report writer. I have learnt so much
during this process and I'm looking forward to playing around and
streamlining the process even more. Thanks again!

Kind regards,

--
AnnieB
Basic Babe in the Woods

Yes, you can replace my line

strScore = "7"

with

strScore = txtScaleScore.Text

and the SELECT statement will work as before, _if_ the user entered a
value in the txtScaleScore field that does occur in the Score column
of the spreadsheet. If they entered letters, negative numbers, 0, or
11 or greater, then the SELECT will return rs.EOF = True (the "not
found" condition).

There are two ways to handle this. The poor way is to accept whatever
the user entered, try to use it in the SELECT, and complain to the
user when it fails, forcing them to try again. And an even poorer
strategy is to complain but not tell the user what was wrong with
their entry. :-(

The better way is to have your code validate the user's entry to be
sure it's within the expected range. If the test fails, return the
cursor to the appropriate field of the userform and exit from the OK
event procedure.

Private Sub cmdOK_Click()
Dim strScore As String
Dim lngScore As Long
' and other declarations

strScore = Trim(txtScaleScore.Text)
lngScore = Val(strScore) ' 0 if not a number

'validate the score
If ((lngScore 1) Or (lngScore 10)) Then
MsgBox Prompt:=strScore & " is not a valid score", _
Title:="Score Error"
txtScaleScore.SetFocus
Exit Sub
End If

' if we get here, the score is OK,
' so proceed with the rest of your code...
End Sub

Look up the Val and Trim functions and the .SetFocus method in the
VBA Help to see what they do (put your cursor on the word in the code
window and press F1).

--
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 Sun, 9 Sep 2007 05:52:00 -0700, AnnieB
wrote:

Hi Jay,

Thanks so much - you certainly have helped me along the way! You
visualised my spreadsheet very well indeed. I was able to get the
selected text back into the right place in my Word doc using your
sample code.
I just need to play around a bit more so I can have the value of a
named reference as my strScore instead of specifying a number.
If I put your ameded code into my userform as part of the cmd_click
OK event, can I use the formfield txtScaleScore.value that the user
input as the parameter for the strScore value? These formfield
values will also populate bookmarks in the Word table on the
click_OK event.
Sorry, I know it's very difficult when you don't have a working
example of the table in front of you and the person describing it
can't articulate clearly!!


---
AnnieB
Basic Babe in the Woods



"Jay Freedman" wrote:

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.