Solved: Paste special from Excel to Word

[jazzyt2u]

Hi,

How do you paste special something from Excel to word.

I tried the following and it doesn't work

Dim oWord As Object
Dim oDoc As Object
Set oWord = CreateObject("Word.Application")
MyFile = InputBox("Please copy and paste the file path that contains the word file for this project.)
Set oDoc = oWord.Documents.Open(MyFile)
Sheets("UnderstandingChart").Select
Range("A2").Select
Dim rngT4 As Range
Dim rngD4 As Range
Dim rngTD4 As Range
Set rngT4 = Selection
ActiveCell.Offset(7, 16).Select
Set rngD4 = Selection
Set rngTD4 = Range(rngT4, rngD4)

Range("C1").Select


oDoc.Bookmarks("UnderstandingChart").Range.Select
Worksheets("UnderstandingChart").Range(rngT4, rngD4).CopyPicture xlScreen, xlBitmap
oWord.Selection.PasteAndFormat Type:=3 'This is for wdPasteMetafilePicture


When I do that I get a 5342 error saying data type unavailable.
If I use wdPasteMetafilePicture instead of 3 the copy is blurry.

If even tried


...
oDoc.Bookmarks("UnderstandingChart").Range.Select
rngTD4.copy
oWord.Selection.PasteAndFormat Type:=3 'This is for wdPasteMetafilePicture


:help

[fumei]

1. It apears you are not using Option Explicit - MyFile is not declared.

2. Also, the syntax for the Inputbox is missing an ending ". This would be detected if you were using Option Explicit.

MyFile = InputBox("Please copy and paste the file path " & _
"that contains the word file for this project.[color red]"[/color red])

3. Why are you doing all that Selecting in Excel????

4. Did you look up PasteandFormat in Help? It would be a good idea. It clearly shows that wdPasteMetafilePicture is not....wait for it....not a valid data type. Further, 3 is ALSO not a valid number for PasteandFormat. There is no 3 for PasteandFormat.

5. You are trying to use wdPasteMetafilePicture...but you are copying it as xlScreen, xlBitmap.

6. You are using Worksheets("UnderstandingChart") in your code...but there is no "UnderstandingChart" in the supplied file. It is Charts. ANOTHER reason for using Option Explicit!

7. You do NOT need to select the Word bookmark, and then paste using Selection. You can paste directly via the Range of the bookmark. I made a Word file with a "UnderstandingChart" and this works:
Worksheets("Charts").Range(rngT4, rngD4) _
.CopyPicture xlScreen, xlBitmap
oDoc.Bookmarks("UnderstandingChart").Range _
.PasteSpecial DataType:=4
' 4 = wdPasteBitmap

oDoc.Save
oDoc.Close
Set oDoc = Nothing
Set oWord = Nothing



Note the destroying of the objects.

[fumei]

This is not meant, in any way, to be critical. I would strongly suggest you learn to use the ObjectBrowser (in both Word and Excel). It is a massive resource.

[jazzyt2u]

Hi Fumei,

Well aren't you just the smart butt today and normally you're quite nice to people... :*)

A. Thank you for your help.:bow:

B. The message box was just a quick cut and paste from my main macro and I deleted some stuff from it which probably included the ending ". And I changed myFile from MyFolder which is declared. Sorry for doing a half assz job about it...

C. I'm doing all of the selecting in Excel because that's where the data resides. I'm trying to get it into Word...so how would I get it in there otherwise?

D. Yes I did look up pasteandformat in Word Help the only example was Selection.PasteAndFormat Type:=wdChartPicture
It didn't give me what was valid and what wasn't.

E. Yeah forgot to change the code to the renamed tab when posting this. LOL I really messed this up...

F. I would have figured to use pastespecial Data Type... if the Macro recorder for Word showed me pastespecial instead of "Selection.PasteAndFormat (wdPasteDefault)" even though I did a paste special.

G. I have used object browswer but not that familiar with it...still learning. Is there a book...yeah right of course not that would be too helpful.

Now let me see if this works the way I'm hoping...

[fumei]

"and normally you're quite nice to people..."

Sorry, does that mean you felt I was not being nice? If so, well sorry, that was not my intention.

My question re: selecting in Excel was that it seems to be a lot of selecting going on, and I am not sure what, exactly, of the file you are trying to get into Word.

You Select A2.
Offset...then Select
Select C1

I do not understand in particular why the C1 selection.

You state "It didn't give me what was valid and what wasn't." regarding Help on PasteandFormat. Not quite correct. It states the syntax required:


expression.PasteAndFormat(Type)


and states the Type is required, and that it is one of the wdRecoveryType.


Type Required WdRecoveryType. The type of formatting to use when pasting the selected table cells.

WdRecoveryType can be one of these WdRecoveryType constants.

wdChart Pastes a Microsoft Excel chart as an embedded OLE object.
wdChartLinked Pastes an Excel chart and links it to the original Excel spreadsheet.
wdChartPicture Pastes an Excel chart as a picture.
wdFormatOriginalFormatting Preserves original formatting of the pasted material.
wdFormatPlainText Pastes as plain, unformatted text.
wdFormatSurroundingFormattingWithEmphasis Matches the formatting of the pasted text to the formatting of surrounding text.
wdListCombineWithExistingList Merges a pasted list with neighboring lists.
wdListContinueNumbering Continues numbering of a pasted list from the list in the document.
wdListRestartNumbering Restarts numbering of a pasted list.
wdSingleCellTable Pastes a single cell table as a separate table.
wdSingleCellText Pastes a single cell as text.
wdTableAppendTable Merges pasted cells into an existing table by inserting the pasted rows between the selected rows.
wdTableInsertAsRows Inserts a pasted table as rows between two rows in the target table.
wdTableOriginalFormatting Pastes an appended table without merging table styles.
wdTableOverwriteCells Pastes table cells and overwrites existing table cells.


The above is directly copied from Help. Notice that wdPasteMetafilePicture (which you used) is NOT a valid type.

If you open ObjectBrowser and type in PasteandFormat, you get items for range and selection. At the bottom it shows the relevant Sub.


Sub PasteAndFormat(Type As WdRecoveryType)
Member of Word.Selection


If you click on WdRecoveryType, it redisplays showing the valid items within WdRecoveryType.

wdChart
wdChartLinked
wdChartPicture
wdFormatOriginalFormatting
wdFormatPlainText
wdFormatSurroundingFormattingWithEmphasis
wdListCombineWithExistingList
wdListContinueNumbering
wdListRestartNumbering
wdSingleCellTable
wdSingleCellText
wdTableAppendTable
wdTableInsertAsRows
wdTableOriginalFormatting
wdTableOverwriteCells

Clicking on any one will display its Constant, for example, wdTableOriginalFormatting will display:

Const wdTableOriginalFormatting = 12
Member of Word.WdRecoveryType

If you click through all of them you can see there is no 3.

I apologize if it seemed i was being rude.

Good luck.

[jazzyt2u]

I knew I saw that list before but my mind was gone by time I was looking in help and didn't even think to click on wdRecoveryType to see what it was talking about. I was just like what a crappy example it's giving...LOL

Thank you so much!!!!!!!!!

I've used Object Viewer to look up constance before...aside from that what's another good reason to use Object View...as I'm not all that familiar with it...

[fumei]

ObjectBrowser does not just shows constants, but all properties and methods for all objects. Well...almost all. There are some exceptions. Also true, that some items do not have a Help item asscoiated with it, but quite a number DO.

I admit, it takes a little get used to exploring through it, but it is well worth the effort.

Just as an example:

Type in Selection into the search box (with <All libraries> as the searching area). You get GOBS of listed items. Say scroll down to Class - ComboBox, and SelectionMargin as the Member.

It displays all the members of Combobox, with SelectionMargin highlighted. Right click SelectionMargin, and select Help...and voila, you get Help on using SelectionMargin (a property of Combobox).

Alternatively, say you wanted to understand, and work with, a combobox, type in combobox. ObjectBrowser lists all properties and methods for combobox. By exploring through these you can - with some persistence I admit - get a greater depth of understanding of how comboboxes function.

Again, I admit it takes time and effort (as there are a LOT of objects, and LOTS and LOTS of properties and methods), but it is a huge resource. Other than actually trying to code things, it is one of the best ways to grasp the Object Model. And, IMO, understanding the Object Model is the key to using VBA...no matter what application.