VBA: How to avoid a date format change when Pasting an Excel Chart

Question

I have created 5 graphs and tables that are uploaded to an Email thanks to this Question Pasting an Excel range into an email as a picture

Unfortunately, my first graph has a change in the date's format when pasted in Excel already, whereas the second one (and all others) still look exactly similar to what I created. What other way are there to copy paste graphs or what should I look at to make sure this doesn t happen?.
First Graph with date as number

Sheets(2).ChartObjects(1).Chart.CopyPicture
    Dim p2 As Picture
    Set p2 = ActiveSheet.Pictures.Paste

'here date format changed

    Sheets(3).ChartObjects(1).Chart.CopyPicture
    Dim p3 As Picture
    Set p3 = ActiveSheet.Pictures.Paste

'here no change

Does that first image really get pasted as a picture? And does it still show up normally in excel? Even if you execute your code step by step? — Luuklag, Dec 14 '17 at 07:50
Good point: No it doesn t. So not an Outlook problem (should I Edit my question?) — Pierre44, Dec 14 '17 at 08:07
I think your problem is with your select statements. That is something you really should try to avoid. See https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 — Luuklag, Dec 14 '17 at 08:10
I am usually not using selections but I m not fit with Outlook vba yet. I deleted everything after "Set p2 = ActiveSheet.Pictures.Paste" And It gives the same wrong format in Excel (=Not a selection issue, I edited my Question) — Pierre44, Dec 14 '17 at 08:16
You could try ´ActiveSheet.Pictures.PasteSpecial Format:=2´ or something like that. Maybe this helps — Moosli, Dec 14 '17 at 08:51
@Moosli I tried but cannot find what PasteSpecial to use properly (yours didn t work) — Pierre44, Dec 14 '17 at 09:14
@Pierre44 see here: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-pastespecial-method-excel for the options. For bitmap it is `Format:=4` — Luuklag, Dec 14 '17 at 09:21
@Pierre44 Can you try this once? "Sheets(1).ChartObjects(1).Chart.Parent.Copy ActiveSheet.Pictures.Paste" — Moosli, Dec 14 '17 at 09:22
@Luuklag No Pastespecial format worked. Now I have tried with "record a macro", it worked the first time (without macro) then when I run the Macro it changes format again — Pierre44, Dec 14 '17 at 09:33
when does the Dates change exactly? During the Copy or before? — Moosli, Dec 14 '17 at 09:35
Data changed while pasting. I solved my issue by pasting directly in Outlook (skipped "Dim p [...] set p [...] p.cut") but now the picture is not wrapped with text on top and bottom like others... — Pierre44, Dec 14 '17 at 13:41

score 0 · Accepted Answer · answered Dec 21 '17 at 08:14

I finally found an answer from another website:

"The date format problem happens because the axis formats are probably linked to the source data. As long as the source data is in an open workbook, Excel will go and find the format and apply it. To get around this, before copying the chart, double click on the axis, select the Number tab, and uncheck the Link to Source checkbox." Jon Peltier

VBA: How to avoid a date format change when Pasting an Excel Chart

1 Answers1