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.