retrieve excel row content into word doc using command button

[fbastian]

Hi,
can someone get me started with some coding for the subject above please.
I am a learner at the moment so not sure where to begin.
I have an excel file and a word file both attached to this thread.

all I want to do is to be able to type the ID from the excel into the ID textbox in word and then click the go button to retrieve the information on that row onto the textboxes named in word.

Does anyone have any suggestions on how I can start this?

thanking you in advance.

[gmayor]

I personally wouldn't have used activeX text boxes to hold the results or the request, but add the following code to the button to work with what you have provided.


Option Explicit

Private Sub CommandButton1_Click()
If TextBox1.Text = "" Then
MsgBox "Enter the ID!", vbCritical
GoTo lbl_Exit
End If
TextBox2.Text = GetArrayValue(Trim(TextBox1.Text), 1)
TextBox3.Text = GetArrayValue(Trim(TextBox1.Text), 2)
TextBox4.Text = GetArrayValue(Trim(TextBox1.Text), 3)
TextBox5.Text = GetArrayValue(Trim(TextBox1.Text), 4)
lbl_Exit:
Exit Sub
End Sub

Then insert a new module into the document project and add the following to that module. Change the path to that of your workbook and the Sheet Name to the name of the worksheet containing the data. Provided the worksheet data matches your test file, and the text boxes have the same names as the test document then the process will work. Save the document as macro enabled (preferably as a template).



Option Explicit
Private RS As Object
Private CN As Object
Private i As Long, iRows As Long
Private iCols As Long
Private sResult As String
Private sFindText As String
Private sReplaceText As String
Private oRng As Range
Const strWorkBook As String = "C:\Path\test excel.xlsx" 'The path of the workbook
Const strSheet As String = "Sheet1" 'The name of the worksheet

Private Function xlFillArray(strWorkBook As String, _
strWorksheetName As String) As Variant
Dim RS As Object
Dim CN As Object

strWorksheetName = strWorksheetName & "$]"
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkBook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

Set RS = CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM [" & strWorksheetName, CN, 2, 1

With RS
.MoveLast
iRows = .RecordCount
.MoveFirst
End With
xlFillArray = RS.GetRows(iRows)
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
lbl_Exit:
Exit Function
End Function

Function GetArrayValue(strItem As String, iColumn As Long) As String
Dim arr() As Variant
arr = xlFillArray(strWorkBook, strSheet)
For iCols = 0 To UBound(arr, 2)
sResult = arr(0, iCols)
If InStr(1, sResult, strItem) > 0 Then GetArrayValue = arr(iColumn, iCols)
Next iCols
lbl_Exit:
Exit Function
End Function

[fbastian]

Graham,
thank you very much for your response.
I have tried the code you have supplied and get a compiling error as attached.
Also you said you personally wouldn't use this method to retrieve the data. I am open to other suggestions. :)

thanking you again.

[gmayor]

The errant line is actually three lines, however to save drip feeding errors to the forum, I have attached a working version (change the workbook path to that of your workbook).

[fbastian]

hi, thanks again for replying.
I have changed the workbook file path and this time had no errors but nothing happens.

I type the number in the ID text box and then hot GO but no data is retrieved.
if I just hit GO without typing anything into the ID text box, I should get a message alert according to the code, but nothing happens.
Would it be some setting in my PC here at work?
Did you test it after your changes?

sorry for the bother.
I think you're close to getting it though.
thanks again.

[gmayor]

It works fine here. If the number does not exist in the data it will report that, otherwise it fills in the fields. If the data source doesn't exist it will crash with an error message. Neither of those things suggest macros are disabled. Save the document as a macro enabled template in a trusted location and create a new document from it.