There are several ways to import data into Word from Excel. The web site
below is good:
http://www.exceltip.com/st/Control_Excel_from_Word_using_VBA_in_Microsoft_Excel/463.html
Also, this resource is very good:
http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm
I use this code (currently) for several projects.
I would recommend using a small subroutine to open the listbox:
Sub AutoNew()
UserForm1.Show
End Sub
Under Forms, create a UserForm, call it UserForm1, and create a Listbox and
ControlButton on that UserForm. Double-click the ControlButton adn place the
code between the *** in the window that opens:
'********************************************
Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.Variables("name of first value").Value = ListBox1.Value
ListBox1.BoundColumn = 2
ActiveDocument.Variables("name of second value").Value = ListBox1.Value
' etc.
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub
Private Sub UserForm_Initialize()
'Dim qd As DAO.QueryDef
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("J:Historical View.xls", False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")
' 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
'********************************************
Regards,
Ryan---
--
RyGuy
macropod said:
Hi Tom,
Depending on what you're trying to achieve, you may not need vba at all.
If, for example, you want to send a lot of letters and you have names, addresses & other data in Excel, a mailmerge may be what you
need. Alternatively, if you have a report that you want to update regularly with a value that is always in the same location or
named range in an Excel workbook, then setting up a link between the two might be more appropriate.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------
Hi,
I am fairly new to word vba and would like to import some a cell from
Excel into a Word document.
For example, I would like to import a cell from an excel file (say C:\
\LinkToWord.xls Sheet!1 cell B7, which holds the value 3.55).
Then in Word, I would like to insert the value from excel into a
sentence, like :
My cat is //Insert cell B7// years old.
Any ideas how to do this ?
Regards,
Tom
