CODEWITHSUNDEEP
excelvbams-word
JoeB
JoeB

Reputation: 297

Copying Excel Range to Word as A Table Problems

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.

Upvotes: 0

Views: 2220

Answers (1)

JoeB
JoeB

Reputation: 297

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.

Upvotes: 1

Related Questions