Consulting

Results 1 to 10 of 10

Thread: Import user defined data from Excel into Word

  1. #1

    Import user defined data from Excel into Word

    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:
    datasample - Copy.jpg

    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.
    Attached Images Attached Images

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    2,796
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    Thanks Greg. A mensch as always.

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

  4. #4
    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/down....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.

  5. #5
    Change
    "Provider=Microsoft.ACE.OLEDB.15.0;"
    to
    "Provider=Microsoft.ACE.OLEDB.12.0;"
    which should be available in most recent Office Versions
    Graham Mayor - MS MVP (Word)
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,424
    Location
    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-m...html#post67097

    In each case, no macros are required.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  7. #7
    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.


  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,424
    Location
    Quote Originally Posted by mellowest View Post
    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.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  9. #9
    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.

  10. #10
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,424
    Location
    Quote Originally Posted by mellowest View Post
    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.
    Cheers
    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •