Import Data from Excel into Word template

[Mata97]

Hello,

I have a problem with my vba code for this problem:
There is a list with data in my excel file, for example: B4 has information about the costumer. I also have a template in word where there is a special place for that information. I named the text box where dat information needs "customer".

But I dontknow how i need to make that connection. I tried 10 different codes but there is something wrong..

I really hope someone can help me!

 

[Kenneth Hobson]

Welcome to the forum!

I guess you mean ContentControl textbox?

Maybe post your best code or links to one that get you close?

Maybe this will give you an idea or two.

 'http://www.vbaexpress.com/forum/showthread.php?t=39654"]http://www.vbaexpress.com/forum/showthread.php?t=39654
Sub AddContentControlValues() 
     ' Add Tools > References: Microsoft Word and Microsoft Scripting Runtime
    Dim vField As ContentControl 
    Dim fso As Scripting.FileSystemObject 
    Dim fsDir As Scripting.Folder 
    Dim fsFile As Scripting.File 
    Dim wdApp As Word.Application 
    Dim myDoc As Word.Document 
    Dim vColumn As Integer 
    Dim vLastRow As Integer 
    Dim i As Integer 
    Dim vValue As Variant 
    Dim vFileName As String 
    Dim cell As Excel.Range 
    Dim inPath As String, outPath As String 
     
     'inPath = "Q:\Sales Reports\Unprocessed\"
     'outPath = "Q:\Sales Reports\Processed\"
    inPath = ThisWorkbook.Path & "\in\" 
    outPath = ThisWorkbook.Path & "\out\" 
     
     
    vLastRow = ActiveSheet.UsedRange.Rows.Count + 1 
    vColumn = 1 
     
    Set fso = New Scripting.FileSystemObject 
    Set fsDir = fso.GetFolder(inPath) 
     
    Set wdApp = New Word.Application 
    wdApp.Visible = True 
     
    For Each fsFile In fsDir.Files 
        wdApp.Documents.Open (fsFile) 
        Set myDoc = wdApp.ActiveDocument 
        For Each vField In wdApp.Documents(myDoc).ContentControls 
            vValue = vField.Range.Text 
             '''''''      Workbooks("DARTS.xlsm").Activate 'Needed?  Not needed if macro ran from it.
            Set cell = Cells(vLastRow, vColumn) 
             
            If vField.Type = wdContentControlCheckBox Then 'Or vField.Type = wdContentControlRichText Or vField.Type = wdContentControlComboBox Or vField.Type = wdContentControlDropdownList Or vField.Type = wdContentControlText Then
                Select Case vField.Tag 
                Case "CheckBox1" 
                    vColumn = vColumn 
                    If vField.Checked = True Then 
                        vValue = "YES" 
                    Else 
                        vValue = "Not Checked" 
                    End If 
                Case "CheckBox2" 
                    If vField.Checked = True Then 
                        vValue = "NO" 
                    Else 
                        vValue = "Not Checked" 
                    End If 
                End Select 
            End If 
            cell.Value = vValue 
             
            vColumn = vColumn + 1 
        Next vField 
         
        vColumn = 1 
        vLastRow = vLastRow + 1 
        vFileName = wdApp.ActiveDocument.Name 
        wdApp.ActiveDocument.Close 
        Name fsFile As outPath & vFileName 
    Next fsFile 
     
    wdApp.Quit 
End Sub

 

[Mata97]

Welcome to the forum!

I guess you mean ContentControl textbox?

Maybe post your best code or links to one that get you close?

Maybe this will give you an idea or two.

 'http://www.vbaexpress.com/forum/showthread.php?t=39654"]http://www.vbaexpress.com/forum/showthread.php?t=39654
Sub AddContentControlValues() 
     ' Add Tools > References: Microsoft Word and Microsoft Scripting Runtime
    Dim vField As ContentControl 
    Dim fso As Scripting.FileSystemObject 
    Dim fsDir As Scripting.Folder 
    Dim fsFile As Scripting.File 
    Dim wdApp As Word.Application 
    Dim myDoc As Word.Document 
    Dim vColumn As Integer 
    Dim vLastRow As Integer 
    Dim i As Integer 
    Dim vValue As Variant 
    Dim vFileName As String 
    Dim cell As Excel.Range 
    Dim inPath As String, outPath As String 
     
     'inPath = "Q:\Sales Reports\Unprocessed\"
     'outPath = "Q:\Sales Reports\Processed\"
    inPath = ThisWorkbook.Path & "\in\" 
    outPath = ThisWorkbook.Path & "\out\" 
     
     
    vLastRow = ActiveSheet.UsedRange.Rows.Count + 1 
    vColumn = 1 
     
    Set fso = New Scripting.FileSystemObject 
    Set fsDir = fso.GetFolder(inPath) 
     
    Set wdApp = New Word.Application 
    wdApp.Visible = True 
     
    For Each fsFile In fsDir.Files 
        wdApp.Documents.Open (fsFile) 
        Set myDoc = wdApp.ActiveDocument 
        For Each vField In wdApp.Documents(myDoc).ContentControls 
            vValue = vField.Range.Text 
             '''''''      Workbooks("DARTS.xlsm").Activate 'Needed?  Not needed if macro ran from it.
            Set cell = Cells(vLastRow, vColumn) 
             
            If vField.Type = wdContentControlCheckBox Then 'Or vField.Type = wdContentControlRichText Or vField.Type = wdContentControlComboBox Or vField.Type = wdContentControlDropdownList Or vField.Type = wdContentControlText Then
                Select Case vField.Tag 
                Case "CheckBox1" 
                    vColumn = vColumn 
                    If vField.Checked = True Then 
                        vValue = "YES" 
                    Else 
                        vValue = "Not Checked" 
                    End If 
                Case "CheckBox2" 
                    If vField.Checked = True Then 
                        vValue = "NO" 
                    Else 
                        vValue = "Not Checked" 
                    End If 
                End Select 
            End If 
            cell.Value = vValue 
             
            vColumn = vColumn + 1 
        Next vField 
         
        vColumn = 1 
        vLastRow = vLastRow + 1 
        vFileName = wdApp.ActiveDocument.Name 
        wdApp.ActiveDocument.Close 
        Name fsFile As outPath & vFileName 
    Next fsFile 
     
    wdApp.Quit 
End Sub

Thenks but I think that code is for word in excel? I was hoping the code would be a little bit smaller.. because i don't understand a thing

 

[Kenneth Hobson]

Since this is an Excel forum, we export data to Word. IF you want import Excel data from Word, then you will need to ask in a Word forum.

In either case, it is all VBA and similar work will be done in both.

Press F1 with cursor in or next to a command word if you need extended help.

If your project is Excel -> Word, then attaching a link to your short example file(s), a free shared site might be your best way to get on target help. e.g. dropbox.com

TIP: Quoting all of a post is seldom needed if you respond directly. Even for multiple posts where who you are responding to may be unclear, you can say, Ken, in post #2 , you lost me...

 

[Macropod]

There is a list with data in my excel file, for example: B4 has information about the costumer. I also have a template in word where there is a special place for that information. I named the text box where dat information needs "customer".

But I dontknow how i need to make that connection. I tried 10 different codes but there is something wrong..

This sounds like a job for mailmerge. Have you tried that? See: https://support.office.com/en-us/ar...nvelopes-f488ed5b-b849-4c11-9cff-932c49474705

PS: I've moved the thread to 'General Excel Discussion & Other Questions' as this isn't solely an Excel question.