Using Excel Chart in Access (Access 2002)

Topic

New Reply

Is there a way to use Excel to create a chart, then import–or actually link it into Access for use in a report or form? I am running into limitations with the MS Chart wizard.

Reply | Quote

Replies

You should be able to copy an Excel chart to the clipboard, then Paste Special > Paste Link it into an Access form or report.

Perhaps if you indicate what kind of problem you run into with the chart wizard, someone will have a suggestion how to get around it.

Reply | Quote

You should be able to copy an Excel chart to the clipboard, then Paste Special > Paste Link it into an Access form or report.

Perhaps if you indicate what kind of problem you run into with the chart wizard, someone will have a suggestion how to get around it.

Reply | Quote

Another thing you might want to do is set up an Excel file and use Data | Get External Data… and create live links to the Access into the Excel to generate your charts in Excel. You can then Paste the chart back into Access as a Paste Link to make it an Excel Chart. You might get some permissions errors if you have anything open in Access for editing when you try and refresh the data in Excel but other than that it should work. (Although it certainly sounds a circular!)

I am currently working on a project using a lot of External Data links in Excel from a source Access db and Paste Links in Publisher to make complex Annual Reports. I don’t like Access reports all that well actually and this solution works (Access data –> Excel for Charts –> Publisher for layout) works very well.

Reply | Quote

Thanks Steve and Hans for your input.

Hans, it appears that MSChart expects data with series in columns. My data is coming in with series in rows. I couldn’t figure out how to MSChart to read it the other way. If anyone knows how to do this, I would love to hear it. I can’t change the orientation of the data because Access is reading directly from our AS/400. That is my problem with MSChart.

Your idea regarding Past Special… did the trick, though. That comment prompted me to pull the data into Excel, create a chart, then Paste Special it back into Access. I just got through with this when Steve’s post came across with the exact same solution I just used. Steve–it worked like a charm! There is even a parameter in the base query that I thought I would have to use code to get Excel to update the data as the parameter changes, but it gets propagated automatically. Way cool.

Reply | Quote

Glad it worked out! I am working on a project that involves a whole lot of charts and I don’t like Access’s charting features but do like Excel’s. With the (sometimes rough) magic of OLE/DDE you can use Excel charts all over the place. You can also take advantage of Excel VBA to do things like ensure bar charts are in Descending order, etc. Much better than Access’s grumpy chart engine.

Reply | Quote

I agree! One thing to look out for: During a previous project, I tried to use Word as a report writer, which OLE’d a chart from Excel, which in turned pulled it’s data from Access, which in turn pulled it’s data from an AS/400. Quite a data path. The solution included some code within Word to force Excel to update it’s data from Access. It worked, except it would partially corrupt the Excel file. By partially, I mean that the solution would produce the intended results, but the Excel file could no longer be opened and therefore it could not be edited if something changed or needed to be added. If you tried to open the Excel file, nothing would happen–No error message or anything. I finally abandoned that solution.

Reply | Quote

yecch! what version of Excel and Word and Access and OS in this scenario?

I am working on a similar project (Access –> Excel –> Publisher) and, perhaps instinctively, am being cautious about what to do when. For example, I’ll open Excel and run a macro that refreshes all the data from Access and also sorts a variety of charts, then save. THEN open Publisher and request to Update All Links. Once I had the experience of Publisher (apparently) trying to open the same Excel file for each link, which caused the Virus warning dialog and I ended up going into Task Manager to shut the two programs down. Luckily, no file corruption occurred. Another time the data seemed to refresh without problems. I suspect an unorthodox sequence in the chain was causing a problem. If I can establish what’s the best way to do the whole thing, perhaps wrap the whole project into a shell script…

Another thing I am doing is keeping all the related files in a folder in the C: root to make it very easy for OLE/DDE to establish the server/containter setup.

Basically, if DDE is involved, I try to be very careful…

Unfortunately, Access’s reporting leaves too much to be desired for doing nice charting so I like the ability to link Access data to Excel and create charts in the spreadsheet. I am not terribly surprised that a more complex data stream might end up breaking.

Reply | Quote

yecch! what version of Excel and Word and Access and OS in this scenario?

I am working on a similar project (Access –> Excel –> Publisher) and, perhaps instinctively, am being cautious about what to do when. For example, I’ll open Excel and run a macro that refreshes all the data from Access and also sorts a variety of charts, then save. THEN open Publisher and request to Update All Links. Once I had the experience of Publisher (apparently) trying to open the same Excel file for each link, which caused the Virus warning dialog and I ended up going into Task Manager to shut the two programs down. Luckily, no file corruption occurred. Another time the data seemed to refresh without problems. I suspect an unorthodox sequence in the chain was causing a problem. If I can establish what’s the best way to do the whole thing, perhaps wrap the whole project into a shell script…

Another thing I am doing is keeping all the related files in a folder in the C: root to make it very easy for OLE/DDE to establish the server/containter setup.

Basically, if DDE is involved, I try to be very careful…

Unfortunately, Access’s reporting leaves too much to be desired for doing nice charting so I like the ability to link Access data to Excel and create charts in the spreadsheet. I am not terribly surprised that a more complex data stream might end up breaking.

Reply | Quote

I agree! One thing to look out for: During a previous project, I tried to use Word as a report writer, which OLE’d a chart from Excel, which in turned pulled it’s data from Access, which in turn pulled it’s data from an AS/400. Quite a data path. The solution included some code within Word to force Excel to update it’s data from Access. It worked, except it would partially corrupt the Excel file. By partially, I mean that the solution would produce the intended results, but the Excel file could no longer be opened and therefore it could not be edited if something changed or needed to be added. If you tried to open the Excel file, nothing would happen–No error message or anything. I finally abandoned that solution.

Reply | Quote

Glad it worked out! I am working on a project that involves a whole lot of charts and I don’t like Access’s charting features but do like Excel’s. With the (sometimes rough) magic of OLE/DDE you can use Excel charts all over the place. You can also take advantage of Excel VBA to do things like ensure bar charts are in Descending order, etc. Much better than Access’s grumpy chart engine.

Reply | Quote

I’m glad you’ve found a solution to your problem.

For others reading this thread in the future: the MS Chart engine is capable of handling either columns or rows as chart series. If you double click a chart, there are buttons in the chart toolbar for switching from one to the other. Of course, depending on the row source of the chart, the result may be meaningful or not – YMMV.

Reply | Quote

I’m glad you’ve found a solution to your problem.

For others reading this thread in the future: the MS Chart engine is capable of handling either columns or rows as chart series. If you double click a chart, there are buttons in the chart toolbar for switching from one to the other. Of course, depending on the row source of the chart, the result may be meaningful or not – YMMV.

Reply | Quote

Thanks Steve and Hans for your input.

Hans, it appears that MSChart expects data with series in columns. My data is coming in with series in rows. I couldn’t figure out how to MSChart to read it the other way. If anyone knows how to do this, I would love to hear it. I can’t change the orientation of the data because Access is reading directly from our AS/400. That is my problem with MSChart.

Your idea regarding Past Special… did the trick, though. That comment prompted me to pull the data into Excel, create a chart, then Paste Special it back into Access. I just got through with this when Steve’s post came across with the exact same solution I just used. Steve–it worked like a charm! There is even a parameter in the base query that I thought I would have to use code to get Excel to update the data as the parameter changes, but it gets propagated automatically. Way cool.

Reply | Quote

Another thing you might want to do is set up an Excel file and use Data | Get External Data… and create live links to the Access into the Excel to generate your charts in Excel. You can then Paste the chart back into Access as a Paste Link to make it an Excel Chart. You might get some permissions errors if you have anything open in Access for editing when you try and refresh the data in Excel but other than that it should work. (Although it certainly sounds a circular!)

I am currently working on a project using a lot of External Data links in Excel from a source Access db and Paste Links in Publisher to make complex Annual Reports. I don’t like Access reports all that well actually and this solution works (Access data –> Excel for Charts –> Publisher for layout) works very well.

Reply | Quote