Search code examples
excelms-accessvba

Chart is not copying correctly from Excel to Access

Using data from Access, I have created a line chart in Excel. When I go to copy/paste the chart into a report my DB, the image is not the same. But, if I paste the same image into a work doc or outlook email, it looks fine. The same distorted version appears if I paste it into msPaint. Please see the attached screenshots of the charts. The big issue is the x-axis labels. Here is my copy/paste code.

Dim objChart As Chart    
objChart.CopyPicture xlScreen, xlBitmap, xlScreen
DoCmd.OpenReport ReportName:="rptDeptWindowChart", View:=acViewDesign

ctlC = Reports!rptDeptWindowChart.Controls.Count

Do
    For Each ctl In Reports!rptDeptWindowChart
        ctl.Name = "ctlDelPic"
        DeleteReportControl "rptDeptWindowChart", "ctlDelPic"
    Next

ctlC = Reports!rptDeptWindowChart.Controls.Count
    
Loop Until ctlC = 0

DoCmd.RunCommand acCmdPaste

Original Excel Chart

Image after being pasted into Access

Solution

  • Access is not well-known for it's charting capabilities. If I were you, I'd stick to using Access for managing large data sets, and export final results of queries to Excel, and do your charting/plotting/graphing in Excel. Word is great for creating documents, PowerPoint is great for presentations, etc. You need to use the right tool for the right job.