Copy Excel Range with formats into new Doc from Template's table

Hi Scotta,

Unless you copy & paste the Excel data, the formatting won't carry across:

Please Login or Register  to view this content.

Originally Posted by scotta

I will modify this code shortly to paste the unformatted text only using "Paste Special" and DataType:=2 (a constant which is the equivalent of Word's unformatted text constant but not available when writing a macro from Excel).
...
By copying the cell from Excel, then pasting it as unformatted text, I get the decimal place and currency formatting I desire, without the font properties that I do not desire.

In that case, it would be more efficient to use:

Please Login or Register  to view this content.

Hence, the post's question was not quite accurate.

No argument there.

Has it occurred to you that it might be easier to apply all the required end-formatting in Excel, say to a worksheet set up specifically for the purpose, but linked to the original, then have your macro temporarily break the links and copy & paste the desired range into Word as an embeded Excel worksheet? That way, there would be no fiddling around to do with iterating through the thumbnails and getting them positioned correctly in the destination table.

Alternatively, you could start off with a linked worksheet object in the Word document, then simply break its links to the Excel workbook.

I prefer the native word format without the links because it is more conventional for someone to modify the content of the cells and easier to edit and format in word rather than jumping back and forth between different interfaces (word/excel). That is the thought, at least.

Thanks for the response.

This code copies pictures from excel to word ... I might post a more elaborate question into another thread, but I think this is enough for me now.

Please Login or Register  to view this content.

OK, an alternative would be to create the link as 'HTML format' which, once you unlink it, will leave you with a Word table rather than an embedded worksheet.

How about going back one step. Instead of using vba to copy & paste the data, create a template that already has a link to the workbook. You can create the link by copying the Excel range then, in Word, using Paste Special> Paste Link > HTML format. Then, when you create the new document, simply update then break the link.

As for inserting an image into a specific cell in Word, you'd insert it into the cell as an inline shape. There is no need to move the cursor - simply reference the destination range directly. For example, to insert an image from a file into cell B2 in the document's first table, the Word vba would be something like:

Please Login or Register  to view this content.

How do I find this Location of the Word Document so that I can PasteSpecial (as HTML) the table?

You could, for example, have a bookmark in the template at the location where the table is to go, and make that the paste destination:
ActiveDocument.Bookmarks("SomeBookmarkName").Range.PasteSpecial DataType:=wdPasteHTML

Originally Posted by scotta

I like this bookmark idea, but it is going to be atypical for most people trying to customize the starting settings.

That hardly seems relevant if all you're doing is using the bookmark to add a table to the document. As I understand it, the idea with this approach is to add the table immediately the document is created. This has nothing to do with customizing start settings.

I'm wasn't familiar with bookmarks, so I figure that other people will not be familiar with bookmarks either.

That's rather like saying you shouldn't use named ranges in Excel, just because some people might not be familiar with them. Any proficient Word user will be familiar with bookmarks. Don't project your lack of knowledge onto your end-users.

Instead, I was thinking to just have an empty table - say 3x3 (arbitrary) sized - in the Word document as a placeholder for the table being dumped from excel via PasteSpecial HTML method (to overwrite the table).

Frankly, I can't see how this could be seen as an improvement on using a bookmark - you're asking for a table, that will have to be deleted before the new one can be added, to perform the same function as a bookmark (ie act as a placeholder).

How can I programatically replace (overwrite) the existing Word table with a one from Excel different size (say 4x4).

To replace a table with another, 4x4 table, you could use code like:

Please Login or Register  to view this content.

Originally Posted by scotta

I suppose I could have an add-in that could
1. launch a Word template with HTML table links
2. edit the links so that it points to the current excel document

this sounds a little tricky and hard to explain (for people wanting to make adjustments)

that said, how would I edit the links of a Word document so that instead of pointing to A1:C3 of WS.xlsx, it pointed to WS_today.xlsx instead?

Why do you need to change the referenced workbook? I had the impression from what you'd previously posted that it's always the same workbook and that only the table data are changing. In that case, all you'll ever need to do is to update & unlink.

Thanks for the code on deleting and replacing the table in the same range. I expect that running the code twice (and incrementing Tables(2)) will replace multiple tables in series (such that Word maintains order of Tables list as the same as the order in the document).

I'm including a workflow chart to explain the use for this macro (I made it in PPT). It explains what I expect to give to others, what I expect them to change, and why I want the layout it to be as obvious and universally understood as possible. People receiving the macro, word template, and add-in will modify the layout of the XLS and DOTX a bit to suit their needs. With this in mind, I want the starting DOTX to be able to visually indicate that a table will be dumped once running the macro, and I'm thinking that having an existing table there is the easiest way to accomplish this (plus, I can add some documentation into the table too).

Once they've tweaked the XLS and DOT, they will run the same Macro (the hope is they wouldn't have to dig-into code) on a regular basis.

I've read through more of the forum posts since I've become a member and am now realizing that posting a very detailed description of the requirements, starting points, use-cases, etc., does not dissuade forum responses, and maybe should have led here from the start. This exchange has been very helpful, macropod. Thanks again.

--- WORKFLOW IMAGE ---
Attachment 178503 <<<==== Opens JPEG

the table layout will not change, but the workbook will change every time with newly populated data (see image: workflow process.jpg <<<==== Opens JPEG , also described in post above). sorry if this was not clear in earlier posts.

I expect that running the code twice (and incrementing Tables(2)) will replace multiple tables in series (such that Word maintains order of Tables list as the same as the order in the document).

Yes, but if the user simply deletes the a table from the template, or moves it so that it now occurs after table 2, for example, the whole lot is liable to end up as one big smelly mess .... table 2 becomes table 1 and whatever code was meant to run on the original table 1 will now run on the former table 2, and so on.

the table layout will not change, but the workbook will change every time with newly populated data

If it's only the workbook content that changes, the link approach is, IMHO, still the simplest option. If the workbook name/location is variable, the link approach, though still valid, becomes less viable.