Copying Excel Range to Word as A Table Problems

Question

I am having issues copying an Excel range and pasting it as a table in Word via VBA. When I use the following to copy the Excel table and paste to Word my hyperlinks do not copy over (ExcelDataRange is an Excel.Range and WordText = MyWordDoc.Selection).

ExcelDataRange.Copy
WordText.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True

So my work-around was to insert the hyperlinks one at a time via a loop like this (Set WordTable = MyWordDoc.Tables(NewlyAddedTable)) :

For J = 1 To WordTable.Columns.Count
    If LenB(ExcelDataRange.Cells(4, J).Value2) > 0 Then
        MyWordDoc.Hyperlinks.Add WordTable.Cell(4, J).Range, _
        ExcelDataRange.Cells(4, J).Hyperlinks(1).Address, ExcelDataRange.Cells(4, J).Hyperlinks(1).SubAddress
    End If
Next J

Problem with this method is that the .SubAddress is not fully copied over from Excel. My link contains: "\\NetworkName\ItemOfInterest", but the copy results in "\NetworkNameItemOfInterest". The First backslash and the middle backslash are getting lost.

If I manually copy and paste the table from Excel to Word, then the hyperlinks are present and correct.

Edit: I'm using the RTF:=True to get the table to have the exact formatting as Excel, but this is what is not carrying over the hyperlink. I'm using this argument because the Row height is not adjustable when set to false.

What version of Word are you using? Also, when you stated that your hyperlinks did not copy initially (before instituting your loop), did you mean nothing gets copied or that the link text copies as plain text without an operational link? The latter is what I get in Word 2010 prior to converting the link into a field coded hyperlink, which allows it to work in Word. — joeschwa
I am using Word 2010. the copy paste with RTF:=True copies the text and formats, but didn't copy the link. So I set this equal to false and just had to do a little more manipulation. I can close this question out, but I do not know how to do it. Do you? — JoeB
Write up what you did to solve the issue as an answer. After posting the answer, an outline of a checkmark will be displayed next to your answer. Click the checkmark so that it turns green. This marks your answer as the correct one. — joeschwa

JoeB JoeB · Accepted Answer · 2014-09-05T19:12:51

I was using the RTF:=True argument to bring the Excel range's formatting into the Word table, but this does not allow the hyperlinks to copy. So, I used:

.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=True, RTF:=False

The RTF:=False allowed the hyperlinks insert correctly. Downside is I had to add extra code to format the Word table to match the Excel range.

Copying Excel Range to Word as A Table Problems

1 Answers