使用contentcontrolsbytitle()将数据从Excel传输到Word文档 [英] Transfer data from Excel to Word document using contentcontrolsbytitle()

问题描述

我正在尝试将数据从Excel工作簿传输到预先存在的Word文档中.

I am trying to transfer data from the Excel workbook to a pre-existing Word document.

在Excel工作簿中，我为货件编号，PO，Shipto创建了一个名称，然后在工作表上为每个重量和批次创建了一个名称，名称为"ShipmentSummary".

Within the Excel workbook, I have created a name for shipmentnumber, PO, Shipto, and then a name for each weight and lots on the worksheet named "ShipmentSummary".

我已经在Word文档上添加了内容控件，分别称为货件编号，采购订单，发货目的地，然后为每个需要重量和批号的位置添加了标题.

I have added content controls on the Word document called shipmentnumber, PO, shipto and then a title for each spot a weight and lot number needs to go.

我希望将来自Excel的货件号转移到带有称为装运"的内容控件的Word文档中，希望将来自Excel的PO转移到带有称为"PO"的内容控件的Word文档中，将来自Excel的ShipTo转移到使用称为"ShipTo"的内容控件将其转移到Word文档.Word文档上的每个装运编号，PO和Shipto控件都有50个.然后，我想要来自名为"VBA_data"的工作表中的权重值.要转移到带有内容控件标题w1-w50的Word文档中.最后，我想从工作表名称"VBA_data"中获取批次值.将被转移到控件标题为l1-l50的Word文档中.

I want the shipmentnumber from Excel to be transferred to the Word document with the content controls called "shipment", PO from Excel to be transferred to the Word document with the content controls called "PO", ShipTo from Excel to be transferred to the Word document with the content controls called "ShipTo". There are 50 of each shipmentnumber,PO and shipto controls on the Word document. I then want the weight values from the worksheet named "VBA_data" to be transferred to the Word document with content controls title w1-w50. Lastly I want the lot values from the worksheet name "VBA_data" to be transferred to the Word document with control title l1-l50.

Sub ReplaceControlsOnCaseLabel()
    Dim wordApp As Word.Application
    Dim wDoc As Word.Document
    Dim cc As ContentControl
    Dim rngCC As Word.Range
    
   set the word application and word document
    Set wordApp = CreateObject("word.application")
    Set wDoc = wordApp.Documents.Open("C:\Users\tyler.masson\Desktop\Shipment_VBA\Case Labels.docx")
    wordApp.Visible = True

'Send value of shipment number in excel workbook to word document with content controls titled "shipment", there are 50 in total.
    For Each cc In wDoc
        If cc.Title = "shipment" Then
            ActiveDocument.Sheets("ShipmentSummary").Range("shipmentnumber").Value
        End If
    Next
    
 'Send value of PO in excel workbook to word document with content controls titled "PO", there are 50 in total.
    For Each cc In wDoc
        If cc.Title = "PO" Then
            ActiveDocument.Sheets("ShipmentSummary").Range("PO").Value
        End If
    Next
    
 'Send value of shipto in excel workbook to word document with content controls titled "shipto", there are 50 in total.
    For Each cc In wDoc
        If cc.Title = "shipto" Then
            ActiveDocuments.Sheets("ShipmentSummary").Range("ShipTo").Value
        End If
    Next
    
    
'Send values of each weight from worksheet "VBA_data" to the word document with contentcontrols title w1-w50
    With wordApp.ActiveDocument
        wDoc.SelectContentControlsByTitle("w1") = ActiveDocument.Sheets("VBA_data").Range("w1").Value
        wDoc.SelectContentControlsByTitle("w2") = ActiveDocument.Sheets("VBA_data").Range("w2").Value
        wDoc.SelectContentControlsByTitle("w3") = ActiveDocument.Sheets("VBA_data").Range("w3").Value
        wDoc.SelectContentControlsByTitle("w4") = ActiveDocument.Sheets("VBA_data").Range("w4").Value
        wDoc.SelectContentControlsByTitle("w5") = ActiveDocument.Sheets("VBA_data").Range("w5").Value
        wDoc.SelectContentControlsByTitle("w6") = ActiveDocument.Sheets("VBA_data").Range("w6").Value
        wDoc.SelectContentControlsByTitle("w7") = ActiveDocument.Sheets("VBA_data").Range("w7").Value
        wDoc.SelectContentControlsByTitle("w8") = ActiveDocument.Sheets("VBA_data").Range("w8").Value
        wDoc.SelectContentControlsByTitle("w9") = ActiveDocument.Sheets("VBA_data").Range("w9").Value
        wDoc.SelectContentControlsByTitle("w10") = ActiveDocument.Sheets("VBA_data").Range("w10").Value
    End With


'Send values of each lot from worksheet "VBA_data" to the word document with contentcontrols title l1-l50
    With wordApp.ActiveDocument
        w wDoc.SelectContentControlsByTitle("l1") = ActiveDocument.Sheets("VBA_data").Range("l1").Value
        wDoc.SelectContentControlsByTitle("l2") = ActiveDocument.Sheets("VBA_data").Range("l2").Value
        wDoc.SelectContentControlsByTitle("l3") = ActiveDocument.Sheets("VBA_data").Range("l3").Value
        wDoc.SelectContentControlsByTitle("l4") = ActiveDocument.Sheets("VBA_data").Range("l4").Value
        wDoc.SelectContentControlsByTitle("l5") = ActiveDocument.Sheets("VBA_data").Range("l5").Value
        wDoc.SelectContentControlsByTitle("l6") = ActiveDocument.Sheets("VBA_data").Range("l6").Value
        wDoc.SelectContentControlsByTitle("l7") = ActiveDocument.Sheets("VBA_data").Range("l7").Value
        wDoc.SelectContentControlsByTitle("l8") = ActiveDocument.Sheets("VBA_data").Range("l8").Value
        wDoc.SelectContentControlsByTitle("l9") = ActiveDocument.Sheets("VBA_data").Range("l9").Value
        wDoc.SelectContentControlsByTitle("l10") = ActiveDocument.Sheets("VBA_data").Range("l10").Value
    End With
        
'Another way to send values to contentcontrolbytitle() ???
    'weight1 = wDoc.SelectContentControlsByTitle("w1") = ActiveDocument.Sheets("VBA_data").Range("w1").Value
    'weight2 = wDoc.SelectContentControlsByTitle("w2") = ActiveDocument.Sheets("VBA_data").Range("w2").Value
    'weight3 = wDoc.SelectContentControlsByTitle("w3") = ActiveDocument.Sheets("VBA_data").Range("w3").Value
    'weight4 = wDoc.SelectContentControlsByTitle("w4") = ActiveDocument.Sheets("VBA_data").Range("w4").Value
    'weight5 = wDoc.SelectContentControlsByTitle("w5") = ActiveDocument.Sheets("VBA_data").Range("w5").Value
    'weight6 = wDoc.SelectContentControlsByTitle("w6") = ActiveDocument.Sheets("VBA_data").Range("w6").Value
    'weight7 = wDoc.SelectContentControlsByTitle("w7") = ActiveDocument.Sheets("VBA_data").Range("w7").Value
    'weight8 = wDoc.SelectContentControlsByTitle("w8") = ActiveDocument.Sheets("VBA_data").Range("w8").Value
    'weight9 = wDoc.SelectContentControlsByTitle("w9") = ActiveDocument.Sheets("VBA_data").Range("w9").Value
    'weight10 = wDoc.SelectContentControlsByTitle("w10") = ActiveDocument.Sheets("VBA_data").Range("w10").Value

    
End Sub

推荐答案

未经测试:

Sub ReplaceControlsOnCaseLabel()
    
    Dim wordApp As Word.Application
    Dim wDoc As Word.document
    Dim cc As ContentControl, wsData As Worksheet
    Dim rngCC As Word.Range, wsShipment As Worksheet, i As Long
    
    'set the word application and word document
    Set wordApp = CreateObject("word.application")
    Set wDoc = wordApp.Documents.Open("C:\Users\tyler.masson\Desktop\Shipment_VBA\Case Labels.docx")
    wordApp.Visible = True

    Set wsShipment = ActiveWorkbook.Sheets("ShipmentSummary") 'or ThisWorkbook
    Set wsData = ActiveWorkbook.Sheets("VBA_data")

    SetCCValueByTitle wDoc, "shipment", wsShipment.Range("shipmentnumber").Value
    SetCCValueByTitle wDoc, "PO", wsShipment.Range("PO").Value
    SetCCValueByTitle wDoc, "shipto", wsShipment.Range("ShipTo").Value
    
    For i = 1 To 10
        SetCCValueByTitle wDoc, "w" & i, wsData.Range("W" & i).Value
        SetCCValueByTitle wDoc, "l" & i, wsData.Range("L" & i).Value
    Next i
    
End Sub

'set text to CCValue in all controls with Title = CCTitle 
Sub SetCCValueByTitle(doc As Word.document, CCTitle, CCValue)
    Dim cc As Word.ContentControl, ccs As Word.ContentControls
    Set ccs = doc.SelectContentControlsByTitle(CCTitle)
    'warn if none found
    If ccs.Count = 0 Then MsgBox "No controls with title '" & CCTitle & "'"
    For Each cc In ccs
        cc.Range.Text = CCValue
    Next cc
End Sub

这篇关于使用contentcontrolsbytitle()将数据从Excel传输到Word文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！