Automate pasting graphics from Word to Access report?

[Deborah]

I have an Access database that exports data to Excel, where we have a complex spreadsheet already set up with associated charts. Excel takes the data and does it's thing, then I import the spreadsheets back to Access where I use them with a formatted report and it works beautifully.

My problem is the 3-D bar and 20th Century pie charts we are using. I use the unbound object to pull in a graphic but it look TERRIBLE. Color is often lost, lines are distorted, and shadows show up as dots.

I found that if I link a Word doc to those Excel objects, then copy/paste to Access, they look muchmuchmuch better. So how do I automate this?

I'm a newbie to VBA code so I need more than just a direction here...please help.

Thank you.

[jonh]

How do you automate what?

Link a word doc to excel how?
Copy what from Word?
Paste it where in Access?

Graphs are vector based; if you resize them they are simply rescaled and redrawn so there is no loss in quality.

If you manually copy a graph from Excel you create a pointer to that graph object in memory.
What happens to it when you paste depends on what type of thing you are pasting into.

For example if you paste it into Word (and presumably in your case, Access) it remains a vector based graph object.

But if you paste into Paint it will be converted into a bitmap, because Paint doesn't do vector graphics and doesn't know what a graph object is.

Bitmap images usually look horrible if you change the colour depth/size/resolution.

If you reduce the size of an image it loses detail. e.g. Lines become jaggy or disappear.
If you increase the size it will become pixelated. e.g. Thin lines become thick and blocky.
If you decrease colour depth, colours are shifted to the nearest match. Often the nearest match isn't near at all and some colours simply disappear.

If you can't just import the data and recreate the graphs in Access, try making your graphs in Excel the size that you want Access to display them at, set your image control in Access to display images at original size with no zoom or stretch and save your images at a higher colour depth or use another format.

[Deborah]

Link a word doc to excel how? Paste special, link keeping source formatting.
Copy what from Word? The Excel chart.
Paste it where in Access? To a report.

Your last paragraph says to use Excel to create the charts at the size that you want Access to display them, etc. which is exactly what I've been doing with awful looking results. The more 3-D the graphic, with shading and color variation, the worse it looks. Also, Access apparently only wants to grab part of a graphic if it is too large, which is too small for my purposes. Clip/Stretch/Zoom can't effect it when only part of the graphic is brought over.

Yeah, I get all the vector graphics stuff and that is WHY I'm trying to do what I described--because it works best and the quality of the graphic is maintained. Access' ability to create attractive charts is just plain basic and sad. I need better. I need 3-D but Access doesn't play well with 3-D. Word and Excel do. Excel to Access doesn't maintain quality when using the unbound object function. Excel to Access does with a copy/paste. Word to Access does with a copy/paste. Therefore, my answer is to find a way to copy/paste.

Let me boil down my question to this; how can I use VBA to copy/paste from Excel or Word to the Access report? I'm not a coder but I can manage if I'm given an example.

Thanks.

[Deborah]

Scanned an example to show you the difference but am unable to upload a .pdf for some reason. How can I get this to you?

[jonh]

If I paste a chart directly from Excel into Access I get a link to the Excel worksheet (which does look nasty).
If I paste the chart into Word, then copy it into Access it's converted to a picture (and it does look a bit better).

So to export a chart from Excel use something like.
mychart.Export "C:\chart.bmp"