Excel to Powerpoint pastespecial date problem

[wimblejon]

Hi there,

I've created a chart on a sheet using VBA and the chart has dates
running along the x axis (in the UK format: dd/mm/yyyy). Using VBA,
the macro copies the chart and pastes it into a powerpoint slide. The
paste works fine and I can resize and position the chart accurately on
the slide, however the dates are turning into US format, - mm/dd/
yyyy !

If I paste the picture in manually, as a Picture(Enhanced Metafile),
the dates remain in UK format!

This is strange because I'm using the CopyPicture and I would have
though that a picture is like a bitmap and it shouldn't know about
dates!

I'd really appreciate some help with this one.
Thanks, Jon

Code snippet follows:
ActiveSheet.ChartObjects(1).Chart.CopyPicture _
Appearance:=xlScreen, Format:=xlPicture, Size:=xlScreen

With PPSlide
' paste and select the chart picture
.Shapes.PasteSpecial Link:=False, _
DataType:=wdPasteEnhancedMetafile
End With

 

[Barb Reinhardt]

I believe I played around with a similar problem. This is the code I have to
copy

XLApp.Selection.CopyPicture Appearance:=xlPrinter,
Format:=xlPicture

You may try changing the appearance pice to xlPrinter. This is totally a
guess.

 

[wimblejon]

Hi Barb,
Thanks for your suggestion but I did try that and the reults are the
same.

 

[NickHK]

Not sure if this is the cause, but,..

I see the value of DataType you are using is wdPasteEnhancedMetafile, which
looks like a Word constant. This may actually be evaluated to various values
depending on:

- Early binding to the Word Type library > 9 > in PP means ppPasteRTF
- No Early binding to the Word Type library + No Option Explicit > 0 > in PP
means ppPasteDefault
- No Early binding to the Word Type library + Option Explicit > Error of
variable not defined.

I assume you actually want to use ppPasteEnhancedMetafile, which has a value
of 2.
You can all these values by checking them in the Object Browser.
You would have to ask MS why they are different values.

NickHK

 

[wimblejon]

Nick (and others),

Quite correct, I was using 'wdPasteEnhancedMetafile', but changing it
to 'ppPasteEnhancedMetafile' made no difference!
I can only assume this problem is (yet another) 'feature' of the MS
interface.

I've fixed it myself - but only via a frig!

I turned the dates on the sheet into text before creating the graph,
thus fooling VBA into ignoring the date format.

Many thanks for your help.

 

[wimblejon]

Nick (and others),

Quite correct, I was using 'wdPasteEnhancedMetafile', but changing it
to 'ppPasteEnhancedMetafile' made no difference!
I can only assume this problem is (yet another) 'feature' of the MS
interface.

I've fixed it myself - but only via a frig!

I turned the dates on the sheet into text before creating the graph,
thus fooling VBA into ignoring the date format.

Many thanks for your help.