Embedding Excel chart in Access report
I have an unbound Excel chart in the detail section of an Access report. I use code to push specific fields to certain positions in the related spreadsheet. At the end of the code, I switch to the chart. When I view or print the chart, the datasheet displays. The datasheet changes from detail record to the next, as it should. I've tried other tips (Refresh and Clip), to no avail. I've placed a breakpoint on the End Sub statement and confirm that the chart is the current sheet.
Here's the essence of my code:
Dim objExcel As Excel.Application, objExcelWorkbook As Excel.Workbook
Set objExcel = Me.fraInquiries.Object.Application
Set objExcelWorkbook = objExcel.Workbooks(Me.fraInquiries.Object.Name)
.Range("B2") = intInquiryToDateGross
.and other cells.
What am I doing wrong? Why won't my chart display
Thank you for taking the time to report an issue.
What's wrong... Please write below.
in vba, I embed a excel workbook into access report, in access 2007, the chart in excel sheet deform. the line in the chart become more thick and the axis y title become unreadable. mais when I return to Excel design mode, the chart come bace to be normal, I dont uderstand what happend?
I embed the excel workboon like this, it work well in ACESS 2003
Set xlApp = Reports(strReport).OLE1.Object.Application
Set xlWrkbk = xlApp.Workbooks.Item
I have a report based on a query. I have set up a chart which totals up some costs and displays them in a bar chart against the year at which they are projected to occur. The chart works fine with one minor flaw.
When I preview the Report it produces a chart for every record giving me 50 odd pages. Is there a way I can produce a report with just one chart based on the fifty records? I used the chartwizard to produce the chart and I may have missed a trick along the way.
When the report is opened in Access, the chart shows up as an OLE Unbound picture. This has always worked great since charting in Access is so limited.
Last week I converted to Access 2007. Since then, when I open these reports, either no chart shows up at all, or just the upper left corner is on the report. I have allowed all content into the trusted zone, and double checked all of the paths, changing the excel extentions to xlsx.
This is happening to all of my charts, even though they seem to be showing up ok in excel, they aren't showing up in Access.
The problem I am having is with the way the column chart displays. In design view, I formatted 4 of the data series as a stacked column chart, each set up with its own color, and a fifth data series as a line chart on the same axis.
When I change to print preview, the second data series (column) turns into the line chart, & vice versa. I tried cutting & pasting the columns how I want them in the little Excel spreadsheet thing that pops up with the chart in edit mode,(and just what is Expr1002 in the upper left corner?), but it keeps changing back.
I've tried resizing the chart in Excel, and resizing the unbound object frame, but it won't show the whole chart.
So far I have found the Access chart wizard to be much less friendly than Excel. In addition I want to set the text in the title and axis legends based on values on the form.
I have not been able to find a name or list of properties for the chart I created so I'm not sure how to go about it.
"Is Access up to the task?". The motivation is to have the whole application in a single program. I'm not sure it is worth the trouble.
I did many calculations with these including counting them individually and getting the percent for each one based on the over all count. Now I would like to create a bar chart with the four responses listed that will update each time it's run.
I created the chart but have to type in the figures for each response myself. Since the calculations are done on the report and not in the query is there a way to use the fields from the report. Unfortunately, this is my first time trying to use the Chart feature in Access. If I can't do this with the Chart option is there another way to have a bar appear on a report?