Interacting with Word

[hewstone999]

I have this code (Access VBA) at the moment that uses Bookmarks to insert information into a word doc.



Code:

Private Sub Command0_Click()
Dim Wrd As Object
Set Wrd = CreateObject("Word.Application")
Dim Mergedoc As String
Mergedoc = Application.CurrentProject.Path
Mergedoc = "C:\Documents and Settings\rhewston\Desktop\Testing2.doc"
Wrd.Documents.Add Mergedoc
Wrd.Visible = True



With Wrd.activedocument.bookmarks
.Item("Title").Range.Text = "Lorenzo Initial Validation Export Resource"

End With

End Sub



Is there a way I could use SQL queries to popular the word doc i.e. Insert * From Table1 - either as text or in a table.

Any ideas or help?

 

[chergh]

Yes you can

You need to do it something like this:

set rs = currentdb.querydefs(queryNameAsString).openrecordset

set wrdApp = new word.application

set doc = wrdapp.activedocument.add

set sel = wrdApp.selection

if rs.recordcount <> 0 then

   doc.tables.add sel.range, rs.recordcount + 1, rs.fields.count

for i = 0 to rs.fields.count -1
   sel.typetext text:=rs.fields(i).name
   
   if i <> rs.fields.count then
      sel.moveright wdcell, 1
   end if
next i

sel.moveleft wdcell, rs.fields.count - 1
sel.movedown wdline

rs.movefirst

for i = 1 to rs.recordcount
   for j = 0 to rs.fields.count - 1
      sel.typetext text:=rs.fields(j).value
      if j <> rs.fields.count - 1 then
         sel.moveright wdcell, 1
      end if   
    next j      
    sel.moveleft wdcell, rs.fields.count
    sel.movedown wdline, 1
next i

Thats a starting point that should give you the general principle of how to do it, been a while since I've done this so you will have to correct the code as you go along.

 

[GaryPanic]

not sure if i am about to muddy the water up here , but have you tried the super easy word merge option ,
in essense - its a mail merge from (1 table) but uses all of words functionality

check the samples -
you can create template docs - to your hearts content, and merge each record into x.doc

regards

 

[Mike375]

Gary,

I would use merge if I could (anyone know how)

Click.....and it opens without msg box about merge, prints letter, does select all/copy, opens a new record with memo field in Access table and pastes the writing in, closes the word.doc

 

[GaryPanic]

Mike - check the super easy word in the samples

this has a table called contacts
and it basically opens up word (from Access) and uses the fields on the form (based on the table "Contacts" and then you create templates, save and close then it gives you the option to merge 1 record or all

-downside is - it has to be 1 table (no links-looks up etc)

my way round this was to create a table and on the open form button click it appends all the fields i require into the table , and when i exit the form it deletes them (keeping the d/b smaller)

you have to import a couple of forms/modules etc
then done ..(none of it my work - )

 

[Mike375]

Gary,

Thanks for that. I will have a look a bit later. I have seen you mention merge a couple of times now on these Access/Word threads.

 

[GaryPanic]

it really works - - but remember the 1 table bit - it does lose formating of numbers - so you have to use words function for formatting numbers ie 1,000 it will do 1000 - so use word formating on the merge field - if you get stuck on this ping me -i spent a couple of hours figuring it out ..(word problem not an access one).

 

[Mike375]

Gary,

I have Samples/NthWind and Sample/Access Project. However, the latter wants to bring in from SQL and "your CD". These machines have no CD. Access 2003 and XP Home

 

[GaryPanic]

check the link

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

all instructions there...