VBA Word and Excel linkage

[sanjdhiman]

Hi there I have a list of Address in Excel. What I need is a link between word and excel, so when the user via a combo box clicks on a surname, it automatically goes to the Excel sheet and grabs the relevant line of data and puts it into the form in word.. is this possible or am I living in a fantasy world?

I know its possible within say excel using the HLookup function or VLookup but is it possible in VBA and across two applications Word and Excel??

Kind regards

and thanks in advance

Sanj

 

[TonyJollans]

Hi sanjdhiman,

Something like this ought to work:

Private Sub ComboBox1_Change()

Dim appXL As Excel.Application

Set appXL = CreateObject("Excel.Application")
appXL.Workbooks.Open "

C:\YourPath\YourWorkbook.xls

"

YourVariable

 = appXL.WorksheetFunction.VLookup(ComboBox1.Text, appXL.Range("

A1:B100

"), 2)

appXL.Quit
Set XL = Nothing

End Sub

You'll need a reference to the Excel library for it to work.

Enjoy,
Tony