Solved: Pasting Excel data into a selection of cells in Word tables

[BillHamilton]

I have an Excel worksheet that contains data that has to be transferred to tables in a Word document. I want to write a macro in the Excel workbook to carry out this export function. I'm ok with writing Excel VBA, but know next to nothing about Word macros and although I have dabbled a bit in the past, this is way beyond my previous experience. I've looked at some other posts here which gave me some inklings, but not enough for me to adapt to do the whole of what I want as most of them are too advanced for me to understand fully (mostly using Word VBA functionality I haven't a clue about). Apologies if I haven't come across the right post that might have given me the answer. Issues like this one seem to be quite popular and there are some posts which ask quite similar questions but aren't fully answered or I can't properly adapt. Anyway, enough rambling:

The Word doc is an existing file and the requirement is to find tables in it to paste the data from the Excel sheet. The wrinkle is that the Excel data is not to replace the entire table but only a block of cells within each table. Selecting and copying the Excel cells is a given, but I have no idea how to select the same-sized block of cells in the relevant table in the Word doc.

As there are other tables in the doc which do not require updating in this way, I need to be able to find the right tables (easily identifiable by constant data in the top LH cell), then select the cells within that table to paste the data into.

There are two types of table in the Word doc. One has three rows and six columns. I need to paste data from Excel into row 3, columns 2-6 (i.e (3,2) to (3,6)). There is only one of this type. The other type has six rows and nine columns. I need to paste (different) data into cells (1,2) to (6,3), and into (1,5) to (6,6), and into (1,8) to (6,9). Then repeat that in at least two further identically laid out tables.

I can open the Word doc in my Excel macro, and can cycle through the tables, so each has to be identified before processing them. I'm totally floundering on making that identification on the top left cell and on selecting the right table cells in order to paste in the data from Excel.

Here's the code I have, stripped down so it doesn't have the Excel bits in it to confuse the issue. I can add those in later once I have the Word bit nailed down.


Sub UpdateWordTable()
Dim wdDoc As Object, wdApp As Object
Dim iTableNo As Integer

Set wdApp = CreateObject("word.application")
Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & Application.PathSeparator & "info12.docx")

With wdDoc
For iTableNo = 1 To wdDoc.Tables.Count
With .Tables(iTableNo)

' Code required here to identify the table from data in top LH cell, then select the right
' range of cells in order to paste in the data in the clipboard from Excel

End With
Next iTableNo
End With
wdDoc.Close savechanges:=False
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

Hope someone can help. I'm sure this is a simple thing for someone who knows what they're doing. If anyone does come up with the answer they will be rewarded with a supplementary question related to this - I don't want to confuse the issue with two problems at the same time (but it's to do with altering the heading text above the type 2 tables).

Using Office 2007 under Win HP

Bill

[macropod]

Hi Bill,

If the idea is simply to update a Word document to reflect changes in the Excel data, the whole lot can be done without macros, simply by setting up links between the two files. To do this, simply copy an Excel cell, select the destination cell in Word, then use Paste Special with the 'Paste Link' option and whichever of the available paste formats best suits your needs.

If that's not what you're trying to do, post back and we'll take it from there.

[BillHamilton]

Thanks Paul.

Slaps hand on forehead. I've used Paste Links between Excel and Word before many times but for some reason it didn't occur to me to use it here. Brain failure. I must be getting too old for this sh1t.

However... I would have to set up 240 such links manually - one for each possible cell involved. That's a lot of repetitive mouse-clicking and will take a lot of boring RSI-aggravating time and TBH I would prefer not to have to do it. Great idea though, and it's an excellent solution for my table type 1 with only 5 cells. I've already implemented it there so that's sorted.

Therefore, as macros are meant to save manual labour, I would like to implement a VBA solution for copying the bigger blocks of cells (the type 2 tables), so I'd be grateful if any of the gurus around here could lend me a hand with that.

I've restructured the data so that the supplementary question will no longer be made. I have incorporated the relevant heading text into both the Excel sheet and the tables and is now intended to be copied over by the macro so the copy/paste will involve one more row than originally described.

Bill

[macropod]

Hi Bill,

With so many cells, it might be best to consider creating the entire table(s) as one or more embedded Excel objects with external cell referencing, the same as you'd have for linking two workbooks.

I really don't see that a macro for creating links to larger blocks is feasible. What might be better (if the above isn't suitable) is for you to copy/paste-link one cell, then copy that to multiple locations, after which you simply edit the source cell addresses (press Alt-F9 to expose the field codes, then edit the references in the field codes).

[BillHamilton]

Hi Paul

I admire your persistence on the linking method!

I tried the first suggestion of pasting links as Excel Objects. They won't format suitably. The tables in the Word doc need to be much smaller there than in the Excel sheet (so they fit on the page). I don't want to shrink the data there because other data on the same sheet would be inappropriately affected. The tables in the worksheet are arranged vertically, and horizontally in groups of three in the Word doc. This exercise is intended to populate the first three columns of the table in the doc, then the second three cols then the third three cols; then move onto the next table underneath and repeat for the next three sets of three columns, then for another third table (which may or may not contain any data depending on the source). I can't get the pasting to result in the required layouts. If I paste an object it takes up far too much space (and second ones won't go in the right place, i.e. side by side with a previous one) and as you know you can't re-size an object pasted as a link.

Your Plan B: I tried the suggestion of pasting one link, copying it to multiple places in the tables and editing the links. Seems just as much work as pasting them all individually, I'm afraid. To me at any rate. Alt+F9 doesn't necessarily expand all the cells to show the whole path for the link, and finding the place in the path to update gets fiddly and messy - I'd still have to swap back and forth to the worksheet to ensure I was putting in the right cell references.

No, sorry. I still think I want to do it by macro (copying the actual data, not links). Linking is ok for just a few cells, but not this many. Life's too short! I'm sure a Word macro expert could do this in his/her sleep. I can do the copying, I just need someone to sort out the pasting.

Would it help if I attached a copy of my Word doc?

Bill

[macropod]

Hi Bill,

The first suggestion in my previous post had nothing to do with "pasting links as Excel Objects". It had to do with creating an embedded object and using Excel's standard external referncing formulae. This would also allow you to reference discontiguous ranges and to re-scale the object.

And contrary to your expressed confidence that "a Word macro expert could do this in his/her sleep", I refer you to what I've already said:

I really don't see that a macro for creating links to larger blocks is feasible.

[BillHamilton]

Paul,

I seem to have offended you. Please accept my apologies and be assured that any offence was totally unintended and entirely stemmed from ignorance and nothing else.

I obviously don't know what is meant by 'embedded Excel objects with external cell referencing', and made an incorrect assumption from my limited knowledge and experience. They are obviously not the same as ordinary links, which is as far as I go on the topic of linking. I'd be grateful if you could enlighten me on this new concept as you seem confident that that's the answer, but I'd have to be brought up to speed on what they are and how to do use them.

In reply to your second comment, I'm not looking for a macro to create links - I'm looking for one to transfer the actual data. Or, if further enlightenment is required, is that how copying from Excel into Word tables actually works?

Please be gentle with this poor ignorant soul. I thought I was asking a fairly straightforward question but it obviously isn't.

Bill

[macropod]

Hi Bill,

No offence was taken. Perhaps my response was a little terse.

Here's one way of creating an embedded worksheet with the links.
1. In Excel, create a new workbook and format it as you'd want it to appear in Word. Don't worry too much about its overall size compared to what you what in Word - just get the proportions right. Use external link formulae to reference the relevant contents in your existing workbook. Save & close the workbook.
2. In Word, use Insert|Object>Object>Create from file and select the new workbook. Then simply re-scale & position the inserted object to meet your needs. If need be, you can change the wrapping format and, by opening the object and dragging it's sizing handles, the # rows/columns displayed.

[BillHamilton]

Paul,

Thank you - that works a dream and I shall use that method. One more thing though: is there a way to update the document to show the new linked object without having to open it? So far the only way I've found to refresh it after the Excel data has been changed is to open the doc and double-click the linked-in object. I have the other fields (as you first suggested) to update as I have to set them to manual, and this one does not appear in the list of external links. I'm thinking of updating those ones in a macro to be executed from the Excel sheet and was wondering if this object could be updated that way too. No biggie if not though.

Now the fun part.

There's nothing like being advised not to do something to make me go and do it, so I have been hitting the internet to learn about how to do what I originally wanted i.e. update the Word table by macro. Purely for my interest, education and intellectual satisfaction - I shall be using the linked object method in my project. I've pretty-much got it working the way I want it to but have run into a little problem. Every second time I run the code I get

"Run Error 462: The remote server machine does not exist or is unavailable"

at the red line in my code below.

I checked this error out and many people have reported that it's got something to do with the way the Word environment and/or the document is set up and referenced in the code. I think I've done all the initialisation, startup and closedown functions according to the usual standard methods, but I might be missing something and I'm unable to translate the solutions given to this project. I fully realise that anyone with any kind of experience in Word macros will probably shudder at all the amateur mistakes, but hey, I've only been doing this two days.
Sub experiment1()

Dim wdDoc As Object, wdApp As Object
Dim iRow As Integer, iCol As Integer, iGrpNo As Integer
Dim oMyTable As Object

Set wdApp = CreateObject("word.application")
Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & Application.PathSeparator & "info12x.docx")

wdApp.Visible = True ' set it visible at the start so we can watch the changes happening for testing

With wdApp.Selection
Set oMyTable = .Goto(wdGoToTable, wdGoToNext, 3) 'Groups are in third table in the document

For iGrpNo = 1 To 12 ' copy and paste each 3Rx8C Group to its correct place in the table, 3 across and 4 down.
If Range("baseg" & iGrpNo).Cells(1, 2) <> "" Then
Range("baseg" & iGrpNo).Range(Cells(1, 1), Cells(7, 3)).Select
Selection.Copy

If iGrpNo = 1 Then ' first time through
' Set oMyTable = .Goto(wdGoToTable, wdGoToNext, 3) 'Groups are in third table in the document
.MoveDown Unit:=wdLine, Count:=30, Extend:=wdExtend ' clear out all previous data
.MoveRight Unit:=wdCharacter, Count:=9, Extend:=wdExtend
.Delete Unit:=wdCharacter, Count:=1
End If
If iGrpNo = 4 Or iGrpNo = 7 Or iGrpNo = 10 Then ' go back to first column then select 2nd cell
.MoveDown Unit:=wdLine, Count:=2
.MoveLeft Unit:=wdCell, Count:=6
.MoveRight Unit:=wdCharacter, Count:=3, Extend:=wdExtend
Else
If iGrpNo <> 1 Then .MoveRight Unit:=wdCharacter, Count:=1
.MoveRight Unit:=wdCharacter, Count:=3, Extend:=wdExtend
End If
.MoveDown Unit:=wdLine, Count:=6, Extend:=wdExtend
.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=True, _
RTF:=True '=not linked to Excel, use current Word formatting, RTF not HTML
End If
Next iGrpNo
.MoveLeft Unit:=wdCell, Count:=1 ' unhighlight the last group

' All data now copied in.
' Now reformat the table because the PastExcelTable parm WordFormatting:=True does not actually work.

For iRow = 1 To 25 Step 8
For iCol = 2 To 8 Step 3 ' format the two group header cells across one row
With ActiveDocument.Tables(3).Cell(iRow, iCol).Range ' format Group name cell
.Font.Name = "Times New Roman"
.Font.Size = 8
.Font.Bold = True
.Font.Underline = wdUnderlineSingle
End With
With ActiveDocument.Tables(3).Cell(iRow, iCol + 1).Range ' format no of qualifiers text
.Font.Name = "Times New Roman"
.Font.Size = 8
End With
Next iCol
' format the body of the groups, doing three at a time across the table
With ActiveDocument.Range(ActiveDocument.Tables(3).Cell(iRow + 1, 1).Range.Start, _
ActiveDocument.Tables(3).Cell(iRow + 6, 9).Range.End)
.Font.Name = "Times New Roman"
.Font.Size = 7
End With
Next iRow

.HomeKey Unit:=wdStory ' put insertion point at the start of the doc
End With ' wdApp.Selection
'wdDoc.Save
'wdApp.Visible = True
wdDoc.Close savechanges:=False
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub
I know the erroneous line references the table in a different way from the rest of the code, but this is the only way I could refer to the table to get that formatting bit to work (well, half the time anyway). I tried all the different combinations of WdApp, WdDoc, oMyTable etc etc that I could think of to reference the table in order for the formats to work and they all failed miserably. How should I be referencing the table in order to do these formats?

Each time after the error occurs I manually close the document and Word is no longer running. It works the next time, and if I check TaskManager there is no instance of Word running but it always fails the next time.

You're right, this probably isn't the way to do the job. Far too slow for one thing. But the perfectionist in me won't let this go till I understand what's wrong and get it working. Who knows, I might need something like this in the future, so I won't then need to start from scratch.

If anyone wants to re-write the junk code I've produced - feel free!

Bill

[macropod]

Hi Bill,

The simplest way to update an embedded Excel worksheet with links is to activate, then deactivate it. That can be done manually or via code. A different approach would be to take the workbook you created in my previous post and add its worksheet to the original workbook as a new sheet. You could then link that entire sheet to Word. To give you more control over the linked sheet's size, you could use the 'picture' past format.

As for your code, it's very difficult to work out what's supposed to be going on, as I've seen neither the workbook nor the document and you seem to be using a lot of unnecessary selections and moves instead of simply specifying the ranges to work with.

There doesn't appear to be any reason for using 'selection'. Thus, instead of 'With wdApp.Selection' just use 'With wdApp'. Similarly, instead of:
Range("baseg" & iGrpNo).Range(Cells(1, 1), Cells(7, 3)).Select
Selection.Copy
use:
Range("baseg" & iGrpNo).Range(Cells(1, 1), Cells(7, 3)).Copy
And, since you want to work with the third table in the document, instead of:
Set oMyTable = .Goto(wdGoToTable, wdGoToNext, 3)
use:
Set oMyTable = .Tables(3)

I'm guessing that, with your 'For iGrpNo = 1 To 12' loop, the first iteration is supposed to clear the table. In that case, simply use:
If iGrpNo = 1 Then oTbl.Range.Delete

Then, I gather you want to work in increments of 3. In that case instead of having:
For iGrpNo = 1 To 12
and:
If iGrpNo = 4 Or iGrpNo = 7 Or iGrpNo = 10 Then
simply use:
For iGrpNo = 1 To 12 Step 3
and, after the 'If iGrpNo = 1 Then oTbl.Range.Delete':
Else
As for the rest, I really have no idea what you're doing.

[BillHamilton]

OK, thank you. I can understand it would be pretty hard to see what's going on without seeing the data. We can draw a line under this now I think.

I appreciate your patience and understanding.

Regards,
Bill