Import user defined data from Excel into Word

[mellowest]

Hello you fine people.

I am trying to figure this out, and I keep failing miserably. The idea is this, starting from Word.

1. Launch Macro within destination document.
2. Input box asks for an item number.
3. Macro opens excel document (address is static)
4. Item number is passed and all rows associated with the item number are copied and pasted into word.

The data looks like this:
22175

I will continue looking, but there seems to be a lot more posts associated with importing Word into Excel than the reverse. Any assistance will be much appreciated.

Thank you.

[gmaxey]

Copying and pasting between Excel and Word could be brutally slow. If you just need the data, then perhaps you could use something like this:


Option Explicit
Private m_arrData As Variant
Private m_strDemoExcelFile As String
Sub ExamplesWithExcel()
Dim lngIN As Long
Dim lngX As Long, lngY As Long
Dim strRecord As String
m_strDemoExcelFile = ThisDocument.Path & "\Book1.xlsx"
lngIN = InputBox("Enter and Item Number", "Item Number", 1)
m_arrData = fcnADODB(m_strDemoExcelFile, "Sheet1$", "WHERE [Item Number] = " & lngIN)
For lngX = 0 To UBound(m_arrData, 2)
strRecord = vbNullString
For lngY = 1 To UBound(m_arrData, 1)
Select Case lngY
Case 1: strRecord = m_arrData(lngY, lngX)
Case UBound(m_arrData, 1): strRecord = strRecord & " " & m_arrData(lngY, lngX) & vbCr
Case Else: strRecord = strRecord & " " & m_arrData(lngY, lngX)
End Select
Next lngY
Selection.Range.InsertAfter strRecord
Selection.Collapse wdCollapseEnd
Next lngX
lbl_Exit:
Exit Sub
End Sub
Public Function fcnADODB(DataBasePath As String, ByVal Table As String, _
Optional Filter As String = vbNullString) As Variant
Dim SQLStatement As String
Dim lngNumRecs As Long, lngIndex As Long
Dim strConnection As String
Dim oConn As Object, oCatalog As Object
Dim oRS As Object
Dim arrData As Variant
'Initialize variables.
fcnADODB = vbNullString
strConnection = "Provider=Microsoft.ACE.OLEDB.15.0;" & _
"Data Source=" & DataBasePath & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Table = "[" & Table & "]"
SQLStatement = Trim("SELECT * FROM " & Table & " " & Filter) & ";"
On Error GoTo Err_Report
Set oConn = CreateObject("ADODB.Connection")
oConn.Open ConnectionString:=strConnection
Set oRS = CreateObject("ADODB.Recordset")
'Get the data
oRS.Open SQLStatement, oConn, 3 'Note - 3 represents cursorLocation adOpenStatic constant. Needed for record count.
'Determine if data found and get record count.
With oRS
If Not .EOF Then
.MoveLast
lngNumRecs = .RecordCount
.MoveFirst
fcnADODB = oRS.GetRows(lngNumRecs)
End If
End With
lbl_Cleanup:
If Not oRS Is Nothing Then
If oRS.State = 1 Then oRS.Close
End If
Set oRS = Nothing
lbl_Exit:
If Not oConn Is Nothing Then
If oConn.State = 1 Then oConn.Close
End If
Set oConn = Nothing
Exit Function
Err_Report:
Application.StatusBar = Err.Number
If InStr(Err.Description, "Data type mismatch in criteria ") = 1 Then
fcnADODB = "Failed Data Mismatch"
Resume lbl_Cleanup
End If
fcnADODB = Err.Description
Resume lbl_Cleanup
End Function

[mellowest]

Thanks Greg. A mensch as always.

And looking at your code...yeah...there was no way I going to figure it out. :eek:

[mellowest]

Hi Greg,

I cannot make this work. I changed m_strDemoExcelFile = ThisDocument.Path & "\Book1.xlsx" to m_strDemoExcelFile = "C:\fullpath\Book1.xlsm"

When I run it, I get a type mismatch. m_arrData shows as "Provider cannot be found. It may not be properly installed." in the locals window.

I went to https://www.microsoft.com/en-us/download/details.aspx?id=13255 and installed AccessDatabaseEngine.exe, but I am still getting the same error.

Would you have any insight as to what I am doing wrong? Thank you.

[gmayor]

Change


"Provider=Microsoft.ACE.OLEDB.15.0;"
to

"Provider=Microsoft.ACE.OLEDB.12.0;"
which should be available in most recent Office Versions

[macropod]

You could, of course, simply configure the document for a directory/catalogue merge and use a SKIPIF field coded along the lines of:
{SKIPIF{FILLIN "Item to process." \o }<>{MERGEFIELD Item}}
to limit the output to just the specified item...

Another option would be to use a DATABASE field in conjunction with a FILLIN field. For a basic implementation (no FILLIN field), see: http://www.msofficeforums.com/mail-merge/21847-mail-merge-into-different-coloumns.html#post67097

In each case, no macros are required.

[mellowest]

Thank you, Graham.

That did the trick. And if you will allow me a follow up question, is it possible to set the lngIN variable to match on strings? As you can see from the picture, the item numbers are saved as text. This is due to some formatting I have to perform on the data. After your suggestion above, the query failed on data mismatch. After I changed it to number, then it worked. Not a big deal; just curious. Thank you again.


Hello Paul,

Thank you for your suggestion. I have never heard of the approach you shared above. I was wondering if you could briefly elaborate on how I would go about configuring the document for a "directory/catalogue merge."? I am not even sure how to start googling for that in context of what I am trying to do. Thanks again.

[macropod]

I was wondering if you could briefly elaborate on how I would go about configuring the document for a "directory/catalogue merge."?
The fundamentals are pretty much the same as for any other merge. The essential difference is that, in Office 2007 & later, you choose the 'Directory' option instead of the default 'Letters' option.

[mellowest]

Hello Paul,

Thanks again for your help. Could you tell me how I could implement {SKIPIF{FILLIN "Item to process." \o }<>{MERGEFIELD Item}} in context of the original post, such that the user could enter an item number to select? I am having trouble conceptualizing how this would be done dynamically, rather than as a fixed set of items. Otherwise, I can see this approach being useful. Thank you.

[macropod]

Could you tell me how I could implement {SKIPIF{FILLIN "Item to process." \o }<>{MERGEFIELD Item}} in context of the original post, such that the user could enter an item number to select?
Simply create the field code I described in your Directory mailmerge main document. It will execute automatically when you complete the merge, asking you which item you want to process.


Note: The field brace pairs (i.e. '{ }') for the field coding are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues.