export information from excel to word doc template

[Aaron Hodson \(Coversure\)]

I have an excel doc/table A3-M3 downwards.

in N3 etc, I would like a button that, when pressed, will open a word.doc
template and export data in A3 into a particulr section in that document.
It will also take info from B3 to another part, B4 to another etc etc.

Is this possible?

Thank you in anticipation.

Kind regards

Aaron

 

[Arvi Laanemets]

Word Mail Merge with Excel table as source (it works opposite you asked - in
word you open a MM template, determine filter conditions, and document(s)
based on data in source table is/are generated/printed/mailed (you can
select different outputs there).

But you must have all info for document in single row of excel table. And
the table MUST have a single header row. And am best keep things with table
as as possible - keep the source table as first in workbook, and start the
table a top of sheet (headers start from A1).

 

[Aaron Hodson \(Coversure\)]

Thanks Arvi,

I was hoping it would be possible to export rather than import,

At present I am inputting information into excel as a of log of information,
once completed, I then open my MSWord template and have to retype all
information.

My excel spreadsheet is normally about 90 rows long per month and I thought
that it may have been possible to click a button to export the info from
that particular row into a word document.

Though I appreciate the prompt reply, it wouldn't be helpful to mail merge
from word.

Thanks

Aaron

 

[Arvi Laanemets]

And why isn't MM helpful then?

You type information into excel, and close the file;
You open Word, and open our MM template (it isn't your document template,
probably you have to create it jet, to link your excel table with it, and
save it);
You start MM with template open, and determine which rows from your table
are processed;
You determine output media;
You process MM - document or documents are created or printed or set as
e-mails.

Next time you enter new info into excel table, save it or close the file,
open MM template you created earlier, determine which rows to process this
time, and create new outputs, etc.

 

[ryguy7272]

It is not only possible, it is quite easy too. Check out these resources:

http://word.mvps.org/faqs/interdev/ControlXLFromWord.htm

http://word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm


This is what I use personally:
http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

If you use this technique, you will have to create a UserForm (called
'UserForm1') and a ListBox (called 'ListBox1') and a CommandButton (called
'CommandButton1').

When you double-click on the CommandButton, you need to have code such as:
Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.Variables("Tracking_Number").Value = ListBox1.Value
ListBox1.BoundColumn = 2
ActiveDocument.Variables("First_Name").Value = ListBox1.Value
ListBox1.BoundColumn = 3
ActiveDocument.Variables("Last_Name").Value = ListBox1.Value
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub

Also, you need to have code such as:
Private Sub UserForm_Initialize()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `myDatabase`")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub

Finally, in your Word document, or Word Template, you need to go to: Insert

Field > DocumentVariable...then name the variable...such as

Tracking_Number, or First_Name, or Last_Name...

Hope that helps!

Regards,
Ryan--

 

[Aaron Hodson \(Coversure\)]

Thank you very much,

Will work on this over the next few days.

Thanks again for the replies,

Aaron