Enter search criteria into Word fields, return data from excel rowsto populate word table

[Frank A Frugone]

I have two documents: an excel spreadsheet and a word template.

I want to create a document from the word template that has fields for
a Start Date and End Date.

Each row in the excel spreadsheet corresponds to a single date.

Once I fill in the start date and the end date, I want a VBA script in
the word document to search the excel spreadsheet for the rows between
and including the Start Date and the End Date.

Then I want the VBA script to populate a word table in the word
document with the data from the rows found in the excel spreadsheet.

Finally, I want to have a field at the end of the word document that
totals the numbers in the last column of the word table.

How do I do it?

(Thanks in advance for any and all help rendered)

 

[Doug Robbins - Word MVP]

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

and the following pages of fellow MVP Greg Maxey's website :

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

You could actually load all of the data form Excel into the ListBox and then
use code in the UserForm to get each of the records that fall between the
start and end dates that the user enters into the userform.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Frank A Frugone]

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

and the following pages of fellow MVP Greg Maxey's website :

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

You could actually load all of the data form Excel into the ListBox and then
use code in the UserForm to get each of the records that fall between the
start and end dates that the user enters into the userform.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Thank You!!!!!!!!!!

I have created my userform, and considered my next step.

I read in an example of MVP Greg Maxey that I can user a DAO Recordset
to retrieve and hold data.

Now I have two new questions:

How can I use OpenRecordset (which appears to use a SQL query) to
select and retrieve my records using the two dates I collected in my
userform as query parameters?

How can I populate an existing wordtable (already in the document
template) with the data from the recordset?

 

[Peter Jamieson]

1. You may actually be able to use a DATABASE field (or the VBA
InsertDatabase method) to insert the results you want. You don't get
much control, but it does all the business of creating a table that you
can then reformat.

I read in an example of MVP Greg Maxey that I can user a DAO Recordset
to retrieve and hold data.

2. FWIW DATABASE/InsertDatabase would typically use an OLE DB connection
in Windows versions of Word from Word 2002 onwards, and you may be
better off using ADO rather than DAO. I can't argue the case though -
it's simply that I know the ADO route. The relevant OLE DB provider is
actually the Access/Jet provider, or the newer Access/ACE provider if
you need to get data from Excel 2007 .xlsx workbooks

How can I use OpenRecordset (which appears to use a SQL query) to

3. You do indeed use SQL. But because Excel, unlike Access, cannot store
queries, you may as well build your SQL statement from scratch in code
and avoid using a query and a parameter-passing mechanism.

ADO sees an Excel workbook as a "database" that contains "tables" for each
a. worksheet
b. named range

So if for example you need all the records from "Sheet1" you might use a
n ADO connection string such as

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<the pathname of the
workbook>;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:Engine Type=35;

where you substitute your workbook name for <the pathname of the workbook>

and SQL such as

SELECT * FROM [Sheet1$]

(The table names may have that extra "$" at the end)

If you need to select columns and rows, it's standard Jet SQL, e.g.
something like

SELECT mycol1, mycol2
FROM [Sheet1$]
WHERE mydate BETWEEN #2009-01-01# AND #2009-06-01#

If you are using InsertDatabase or a DATABASE field, you will probably
need to alias the table name even though the syntax does not demand it:

SELECT t.mycol1, t.mycol2
FROM [Sheet1$] [t]
WHERE t.mydate BETWEEN #2009-01-01# AND #2009-06-01#

How can I populate an existing wordtable (already in the document
template) with the data from the recordset?

4. See my Point 1. Otherwise you have to create the table yourself and
populate it cell by cell. Sorry, I don't have a sample for that

Peter Jamieson

http://tips.pjmsn.me.uk