I got Excel to Paste single cells into a Word Template, but not a range

[VBAProIWish]

Hello All,

So I found this awesome code on this site (sorry I forgot the name of the thread) and I'm having trouble with "20S", highlighted at the bottom of the code in red below. I have a Word Template named "Apple" and all my fields populate from Excel into Word correctly, but I'm having trouble with copying a range of more than one cell.

All the other fields work, except for 20S, which is a region of cells that I'm trying to paste into ONE field in Word. I even tried just 2 cells "G01:G02" and that didn't work either.

Is there a way that Excel can tell Word to paste a region of cells (including the formatting would really be great!) and not just one cell?

Thanks

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:\Apple, 03.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
 [COLOR=red][B]       .formfields("FIELD20S").Result = ws.Range("G01:G02").Value[/B][/COLOR]
 
 
End With
Set wd = Nothing
Set wdApp = Nothing
End Sub

 

[sykes]

There are several ways to do this - but none of them perfect.

If you've only only got a few cells in the range, then the simplest method would be to just concatenate the ranges into another cell somewhere your workbook, out of sight if necessary:
In, say E21 you could place the formula:
=G1&" "&G2&" "&G3&" "&G4
This would give you a simple string to then use in your 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:\Apple, 03.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("FIELD20S").Result = ws.Range("E21").Value
 
 
End With
Set wd = Nothing
Set wdApp = Nothing
End Sub

....a bit schoolboy, but perfectly useable.

A slightly more "proper" method would be to concatenate the range's values into a string variable at runtime, then pass this variable to the 20S formfield when necessary:

Sub Put_in_Word()
 
Dim wdApp As Object, wd As Object, ac As Long, ws As Worksheet
Dim str As String, cl As Range

Set ws = Worksheets("Sendit")

str = ""
For Each cl In ws.Range("G1:G29")
    If cl.Value = "" Then GoTo nxt
    str = str & " " & cl.Value
nxt: Next

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:\Apple, 03.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("FIELD20S").Result = str
 
 
End With
Set wd = Nothing
Set wdApp = Nothing
End Sub

........obviously changing the "sendit" range to suite your needs..

Finally, if you search the board you'll see that you can't use Excel's built-in Concatenate function on a range, but there are widely available add-ins for this, or many examples of easily building your own user-defined function (UDF) into your VBA.

 

[VBAProIWish]

Hi sykes,

Thanks for that code but it only seems to copy over cells in a single row.

In this section of code below...

For Each cl In ws.Range("G1:G29")

I tried to modify it by putting in this range here...

For Each cl In ws.Range("H7:J11")

But it only copied H11:J11.

Any thoughts on how I can get a whole range into Excel?

Even better would be to also bring the formatting over as well. That seems almost impossible but this is VBA after all, so I guess it could be possible.

Thanks

 

[sykes]

I've changed mine to a row & column and it's picking all the data up no problem.
Don't forget it's only looking for cells containing values, so it'll ignore empty cells. I'm not sure why your cells are not being concatenated into the string. What are the values in the cells being omitted?

 

[sykes]

........we can have a look at formatting the data when the string issue's sorted out.
What's your required format?

 

[VBAProIWish]

Hi Sykes,

Yyyeahh, I see what you mean, it is indeed copying over the data, but it looks terrible on the Word side. You know what would be a lot easier, if I could just copy the Excel range as a picture. I really don't need linking or anything like that. I just need a "picture" of the data. This might make it easier to keep the formatting as well.

I've seen some code out there that copies an Excels range as a picture but I wouldn't know how combine that code with this code here. If I can find this code on pasting an Excel range as a picture, I will post it here.

Any thoughts?

Thank you for your help

 

[sykes]

If you could give us some examples of the data, it might help to understand what we're dealing with here - it's slightly unusual, for example, to be trying to take the content of a range of cells, and bundle it all into one Word field. There's always an answer on this site - it may well come from someone much more Excel-wise than me, but there's always a way. Sometimes the original question doesn't always precisely describe what's required, so would you give a good example of the data in the original range, and what you wish to be the result in the target field.
That way we can better help with the solution.♠

 

[VBAProIWish]

Sykes,

Hey thanks for sticking with me on this one. I found another thread that explains in a little more detail what I want. It's actually a thread started by someone else awhile back. See the last post from me on this one as I think it'll be easier to understand.

http://www.mrexcel.com/forum/showthread.php?t=355225


Thanks Sykes!