Paste unformatted text into Word via Excel

[iamtehwalrus]

I'm new to VBA. I'm trying to take an excel range and paste it as unformatted text into Word. The problem is I can't figure out how to paste as unformatted text specifically. Everything else works fine.

Please Login or Register  to view this content.

Running this says "Compile Error: variable not defined." with wdPasteText highlighted. I tried pasting this way in word and it WORKS, but not in excel

All my code works if I replace the paste line with

Please Login or Register  to view this content.

But this isn't what I want since it pastes everything as a table.

Any ideas?

Thanks

EDIT: I should probably mention I'm using Office 2010 Beta :o

[Marcol]

Try

Please Login or Register  to view this content.

"wdPasteText" is a word constant not an excel one, that is why you get a compiler error.

Hope this helps, I'm not up on word

[iamtehwalrus]

Run-time error '448':
Named argument not found

sosad

[teylyn]

iamthewalrus, please don't quote whole posts.

[iamtehwalrus]

Sorry, I did it out of habit

And I fixed my problem. I went into word and made this macro

Please Login or Register  to view this content.

and '2' popped up, so I just replaced wdPasteText with 2 in my original code

[Marcol]

Okay, I was wrong to suggest using "Paste:=xlPasteValues"

It seems when you paste to Word a shape not a table is the result, it looks like a table but it's not,

I tried

Please Login or Register  to view this content.

that also failed.

And as I ventured into my next theory, you solved it yourself, well done!

Nice one, I'll remember that!

Regards
Alistair

[iamtehwalrus]

Yeah, unfortunately it's just a makeshift workaround. Definitely not ideal. I'm not entirely sure what's going on, but I'm assuming Excel doesn't have access to those Word constants (which is why they start with wd). There's probably some way to fix that but I don't know what that is

[Marcol]

Don't know if there will be, You have probably found the only way.

Here is a list of all the constants. (You could "tell" Excel the values, as below)

Please Login or Register  to view this content.

wdPasteDefault depends on what is on the clipboard

This is probably why it pastes a shape not a table when copying Excel cells if it dosn't know what the constant is.

Why wdPasteOLEObject is also 0, I don't know.
Don't know what happened to 6

Cheers.....