Paste Special Bitmap Excel VBA to Word

[JohnNoviceVB]

Dear Gurus and Masters,
I am a novice VBA coder. I have a code that generates a company report in Excel VBA then to copies the tables in Bitmap format to Word.
As the report gets longer and longer manual intervention is getting more annoying so I tried to automate this step. However a very strange problem is happening.
I have two VBAs one in Excel generating the tables and the other in Word defining the Paste Special Datatype :=4 as a PastaS VBA. I run this PasteSpecial Word VBA from Excel WordApp.Run "PastaS" and have a shortcut Cntrl-Q too.
The strange problem occuring is that, when I manually key in the Short Cut the Paste Special Bitmap works perfectly yet when the code does it, a blank object is generated and copied into Word.
If the Gurus and Masters of VBA could provide help on this issue, I shall be more then gratefull for their help.
Regards,
Novice VBA

[rory]

I think we'd need to see the codes.

[JohnNoviceVB]

Dear Gurus and Masters,
I hope I will not make you laugh with my amateur style,

The Word bit :
Sub Pastas()
'
' PasteSpecial Macro
'
'
Selection.PasteSpecial datatype:=wdPasteBitmap

Selection.InsertBreak Type:=wdPageBreak


End Sub


The Excel VBA :

sub MPNL()

'
'
' * I have copied this piece of code from thce VBA Dev. Guide
'
Const wdWindowStateMaximize As Integer = 1
Const wdNormalView As Integer = 1
Const wdAlignParagraphCenter As Integer = 1
Const wdAnimationShimmer As Integer = 6
Const wdPasteMetafilePicture As Integer = 3
Const wdInLine As Integer = 0
Const wdPageFitFullPage As Integer = 1
Const wdGoToAbsolute As Integer = 1
Const wdGoToLine As Integer = 3
Dim WordDoc As Object
Set Wordapp = CreateObject("Word.Application")
With Wordapp
.Visible = True
.WindowState = wdWindowStateMaximize
.Documents.Open ("C:\Documents and Settings\MonthlyPnL\Trial\Doc1.doc")
Set WordDoc = .ActiveDocument
End With
WordDoc.ActiveWindow.View = wdNormalView
.
.
.
WordApp.Run "Pastas"
.
' I have also tried the Sendkeys "+%{TAB}+^q",true (Cntrl-Q as the Word macro short cut) but after a number of succcesful cut and pastes this method fails unfortunately.
.
End Sub()

Thanks very much for your time and wise guidance,
Regards,

[rory]

Since you are automating Word anyway, you may as well just add the PasteSpecial to the Excel code:
[VBA]Sub MPNL()
'
'
' * I have copied this piece of code from the VBA Dev. Guide
'
Const wdWindowStateMaximize As Integer = 1
Const wdNormalView As Integer = 1
Const wdAlignParagraphCenter As Integer = 1
Const wdAnimationShimmer As Integer = 6
Const wdPasteMetafilePicture As Integer = 3
Const wdInLine As Integer = 0
Const wdPageFitFullPage As Integer = 1
Const wdGoToAbsolute As Integer = 1
Const wdGoToLine As Integer = 3
Const wdPasteBitmap As Integer = 4
Const wdPageBreak As Integer = 7
Dim WordDoc As Object
Dim WordApp As Object

Set WordApp = CreateObject("Word.Application")
With WordApp
.Visible = True
.WindowState = wdWindowStateMaximize
Set WordDoc = .Documents.Open("C:\Documents and Settings\MonthlyPnL\Trial\Doc1.doc")
End With
WordDoc.ActiveWindow.View = wdNormalView
With WordApp
.Selection.PasteSpecial DataType:=wdPasteBitmap
.Selection.InsertBreak Type:=wdPageBreak
End With
End Sub
[/VBA]

This assumes that you have copied the table before running the code.

[JohnNoviceVB]

Dear Roy,
Thank you for your kind time and advice.
I am so sad to say that the same problem is still not solved.
As I am doing the PasteSpecial bit in Word with the WordApp.Run the same result we are reaching, we are both PasteSpecial:=wdPasteBitMap
to Word but the pasted object in word is an empty object with nothing inside.

Anyhow once again thank you for your time and advice.

Regards,

[rory]

Which version of Excel and Word are you using and what range did you copy (as an example) before running the code? I tested it before posting and it worked perfectly for me.

[tpoynton]

Perhaps a reference to Word needs to be set in the Excel project?

[rory]

Shouldn't require that as it's all using late binding.

[TonyJollans]

Is it possible that you just have Word set to display picture placeholders instead of actual pictures?

[tpoynton]

Shouldn't require that as it's all using late binding.

I'll cop to not knowing what late binding is, except that it seems to mean I dont need to set references I was able to paste fine from Excel to Word (using paste), but not pastespecial using Excel or Word VBA methods. Word seemed to ignore any Excel methods (for example, xlpastevalues), while using any Word methods in Excel led to the error described by the OP.

I ran into the same exact issue just yesterday...and decided to not output to Word because of it. I'd like to know the solution should one arise, as it would change my mind

OK, fiddled a little with it. You get a compile error 'variable not defined' on the wdpastebitmap. when I added a reference to Word, I got a 'specified datatype not available' runtime error. when I switched the pastespecial line to .PasteSpecial (4), it runs without error, but still pastes the charts in Word as embedded Excel objects.

EDIT - also, FYI, the .pastespecial (4) works exactly the same without the reference to Word...so you are probably right Rory that a ref doesnt need to be set, but something is still amiss! I also tried the enhanced metafile with the same results..

hope this helps...

[rory]

If you use the code I posted, you would not get a compile error as I declared a constant for wdPasteBitmap. Late binding means that you declare everything as generic Object type and use
[VBA]Dim appWord As Object
Set appWord = CreateObject("Word.Application")[/VBA]
for instance rather than
[VBA]Dim appWord as Word.Application
Set appWord = New Word.Application[/VBA]
It also means that you have to declare any of the constants you use or use their literal values.
Which version of Office are you using, by the way?
As an aside, I have come across people having issues with wdPasteEnhancedMetafile pasting purely as a wdPasteMetafilePicture.

[tpoynton]

Thanks for explaining Rory - I'm using 2003, and will give the constant thing a go later