Topic: Using Excel Chart in Access (Access 2002) @ AskWoody

Using Excel Chart in Access (Access 2002)
Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Using Excel Chart in Access (Access 2002)
- This topic has 14 replies, 3 voices, and was last updated 21 years, 4 months ago.
Author

Topic
New Reply

WSrrdavis
AskWoody Lounger

January 7, 2004 at 3:57 pm #398768

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

Viewing 3 reply threads
Author

Replies
- WSHansV
  AskWoody Lounger
  
  January 8, 2004 at 12:15 am #765256
  
  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
- WSHansV
  AskWoody Lounger
  
  January 8, 2004 at 12:15 am #765257
  
  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
- WSsteve_skelton13
  AskWoody Lounger
  
  January 8, 2004 at 6:52 pm #765665
  
  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
- WSrrdavis
  AskWoody Lounger
  
  January 8, 2004 at 7:08 pm #765677
  
  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
  
  WSsteve_skelton13
  AskWoody Lounger
  
  January 8, 2004 at 7:14 pm #765679
  
  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
  
  WSrrdavis
  AskWoody Lounger
  
  January 8, 2004 at 7:42 pm #765700
  
  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
  
  WSsteve_skelton13
  AskWoody Lounger
  
  January 8, 2004 at 7:53 pm #765706
  
  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
  
  WSsteve_skelton13
  AskWoody Lounger
  
  January 8, 2004 at 7:53 pm #765707
  
  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
  
  WSrrdavis
  AskWoody Lounger
  
  January 8, 2004 at 7:42 pm #765701
  
  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
  
  WSsteve_skelton13
  AskWoody Lounger
  
  January 8, 2004 at 7:14 pm #765680
  
  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
  
  WSHansV
  AskWoody Lounger
  
  January 8, 2004 at 7:18 pm #765683
  
  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
  
  WSHansV
  AskWoody Lounger
  
  January 8, 2004 at 7:18 pm #765684
  
  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
  
  WSrrdavis
  AskWoody Lounger
  
  January 8, 2004 at 7:08 pm #765678
  
  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
- WSsteve_skelton13
  AskWoody Lounger
  
  January 8, 2004 at 6:52 pm #765666
  
  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
Viewing 3 reply threads

Reply To: Using Excel Chart in Access (Access 2002)
You can use BBCodes to format your content.
Your account can't use all available BBCodes, they will be stripped before saving.

Your information:
Name (required):

Mail (will not be published) (required):

Website:

Cancel

Plus Membership

Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.

AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.

Welcome to our unique respite from the madness.

It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.

S	M	T	W	T	F	S
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Using Excel Chart in Access (Access 2002)

Plus Membership

Search Newsletters

Search Forums

View the Forum

Search for Topics

Recent Topics

Recent blog posts

My Profile

Key Links

Remembering Woody

Using Excel Chart in Access (Access 2002)

Plus Membership

Search Newsletters

Search Forums

View the Forum

Search for Topics

Recent Topics

Recent blog posts

My Profile

Login and Registration

Key Links

Remembering Woody