Microsoft Office Forums - View Single Post - Through VBA, export range from Excel to Word

duugg · 08-24-2009, 07:50 PM

Hello All,

My first post. Below this text, I have code in Excel that does the following...

1. Opens up a Word Template named "Candy01" with 21 Text Form Fields that are locked (I have the lock on).
2. Then, it imports from Excel, a copy of all cells from E01 to E20 into each respective Word Field (Text Form Field).

My problem is FIELD21, I can't get Word to Import A RANGE of cells.

Can this be done?

Also, if possible, I'd REALLY like to get code to put into Excel that will copy a formatted Pivot Table from Excel (non-working, just keep the data and formatting) and paste it into a designated Form-Field in Word. If anyone can pull this off, that would REALLY be great

Thanks much

Code:

Sub Put_in_Word()

Dim wdApp As Object, wd As Object, ac As Long, ws As Worksheet
Set ws = Worksheets("Sendit")
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wd = wdApp.Documents.Open("C:\Candy.dot")
wdApp.Visible = True
With wd
        .formfields("FIELD01").Result = ws.Range("E01").Value
        .formfields("FIELD02").Result = ws.Range("E02").Value
        .formfields("FIELD03").Result = ws.Range("E03").Value
        .formfields("FIELD04").Result = ws.Range("E04").Value
        .formfields("FIELD05").Result = ws.Range("E05").Value
        .formfields("FIELD06").Result = ws.Range("E06").Value
        .formfields("FIELD07").Result = ws.Range("E07").Value
        .formfields("FIELD08").Result = ws.Range("E08").Value
        .formfields("FIELD09").Result = ws.Range("E09").Value
        .formfields("FIELD10").Result = ws.Range("E10").Value
        .formfields("FIELD11").Result = ws.Range("E11").Value
        .formfields("FIELD12").Result = ws.Range("E12").Value
        .formfields("FIELD13").Result = ws.Range("E13").Value
        .formfields("FIELD14").Result = ws.Range("E14").Value
        .formfields("FIELD15").Result = ws.Range("E15").Value
        .formfields("FIELD16").Result = ws.Range("E16").Value
        .formfields("FIELD17").Result = ws.Range("E17").Value
        .formfields("FIELD18").Result = ws.Range("E18").Value
        .formfields("FIELD19").Result = ws.Range("E19").Value
        .formfields("FIELD20").Result = ws.Range("E20").Value
        .formfields("FIELD21").Result = ws.Range("H07:J11").Value

        
        
End With
Set wd = Nothing
Set wdApp = Nothing
End Sub