#  Other Applications & Softwares  > Word Formatting & General >  > [SOLVED] Copy Excel Range with formats into new Doc from Template's  table

## scotta

I have a nice table in excel with formatted cells.
I want a macro to paste this table into word doc that is created from a template.  The template already has 1 table in it.

I'm trying to figure out a nice simple way to do this.  This is what I have so far ...




```
Please Login or Register  to view this content.
```


Unfortunatley, this code doesn't copy the formats to Word.   I suspect there is a copy/paste line of code that I need, but am not sure how to code it at this point.  Any help is much appreciated.

----------


## scotta

Hi, it's the original poster.  I think the questions I posed was too big.  The question is really a lot simpler....

How can I modify the lines of code (below) so that format is preserved (I want to preserve the Number format as currency, for example) when copying to excel.

Thanks in advance.




```
Please Login or Register  to view this content.
```

----------


## macropod

Hi Scotta,

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



```
Please Login or Register  to view this content.
```

----------


## scotta

Thanks.

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).




```
Please Login or Register  to view this content.
```


This will bring over the proper decimal places that I desire (because the Excel currency format has 2 decimals and a "$" sign, and this is what I want in the word table).  It will only bring over the text, and not the font or size from Excel which in my case is undesirable.

To sum it up, the problem with my code was that I was copying the Value from Excel into Word.  So there were no decimal places when the value showed up in word.

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.

Hence, the post's question was not quite accurate.  It should  have more specifically asked to copy text from excel and paste it into word.  The solution is shown in the code above.

----------


## macropod

> 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.

----------


## scotta

Nice.  

Alas,,,,this question is getting modified again to what I really need to accomplish... (big picture requirement)

I've got 4 picture thumbnails in the sheet too.  
I want to put the excel picture, followed by a space, followed by the excel cells text into a single word table' s cell.

I know the picture orders / names, so I can index the picture in the loop with .pictures(R).

What is the best approach to add both the picture and the text into, say, the first column of the word table?

---------- Post added at 11:24 PM ---------- Previous post was at 11:19 PM ----------





> Nice.  
> 
> Alas,,,,this question is getting modified again to what I really need to accomplish...



Maybe this is just too far off topic and should go in another thread.

----------


## macropod

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.

----------


## scotta

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.
```

----------


## macropod

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.

----------


## scotta

That might be (a whole lot) easier.

What is the constant for PasteSpecial as HTML? 




```
Please Login or Register  to view this content.
```


Is there a reference table for some of these constants for Word for Paste Special

Next question ...

I would still have to loop in order to dump the Pictures from Excel to Word (such as in the code here).




```
Please Login or Register  to view this content.
```


Or, is there any easier way to move pictures from Excel to Word?

(And, just out of interest, what if I didn't want to have a dedicated word cell for the picture.  Instead, I just wanted to move the cursor to the left, add a space, then paste the picture.  This is more for my information and isn't essential to the problem)

----------


## macropod

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.
```

----------


## scotta

Here are the constants from Word Help, Scotta (from WdPasteDataType Enumeration)




wdPasteBitmap 4 Bitmap. 
wdPasteDeviceIndependentBitmap 5 Device-independent bitmap. 
wdPasteEnhancedMetafile 9 Enhanced metafile. 
wdPasteHTML 10 HTML. 
wdPasteHyperlink 7 Hyperlink. 
wdPasteMetafilePicture 3 Metafile picture. 
wdPasteOLEObject 0 OLE object. 
wdPasteRTF 1 Rich Text Format (RTF). 
wdPasteShape 8 Shape. 
wdPasteText 2 Text. 


it feels like i'm having this conversation with myself, internet is not needed.

So your code would be ...




```
Please Login or Register  to view this content.
```


Next Question ...

If I want to put the table in a specific place in the Word document .. "put table here"

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

----------


## macropod

> 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

----------


## scotta

I like this bookmark idea, but it is going to be atypical for most people trying to customize the starting settings.  I'm wasn't familiar with bookmarks, so I figure that other people will not be familiar with bookmarks either.

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).

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

If I delete Table(1) in (ex: Table(1).delete), will the selection location remain in the place where the table was just deleted? -- and therefore will I be able to paste the table as the next line of code (I guess I could try it).

----------


## scotta

For this PasteSpecial > Link > HTML

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?

----------


## macropod

> 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.
```

----------


## macropod

> 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.

----------


## scotta

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

----------


## scotta

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.

----------


## macropod

> 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.

----------


## macropod

> 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.

----------


## scotta

thanks again!

----------

