Import Graph from Excel

[Mjz]

Hello, i am curious if it is possible to import a graph from Excel to Access via VBA. To go one step further is it possible to import that graph and automatically attach it to a picture control. Essentially what i am trying to achieve is to have 1 button on a form, that when pressed it auto generates a graph from two columns of points (i assumed Excel would do the best job) and then adds that graph into a picture control on the form. Any input would be appreciated. Thank you.

[ranman256]

No. Make the graph in access.

[Forbes]

ranman256 is correct, with the time an effort you will put into this, using the database tables in access and creating the graph within will be your best bet. you can however import your excel data tables into Access making the graph re-creating much easier depending on the project.

[Micron]

Maybe I'm in a small crowd when I say this, but AFAIC, Access stinks and always will when it comes to charting. Even for simple ones. This will never change as long as there's a M$oft product that can be sold to you, which will do a far better job. I'd rather link the db data, or export it to Excel and graph from there. You can get at if from Access if need be - like when I presented and/or emailed the graph (as a pdf). I suppose there are other ways of presenting the chart as well.
Just my 2 cents.

[Forbes]

Ha ha, small crowd indeed, although you bring up good points with the charts/graphs. You can create the original in Excel and have a form load the excel doc in a popup window.

Another question came to mind when reading Micron's post, if you have no use for form inputs why not just stick to excel? I guess it would help to know more about your project for us to give a more catered answer.

[Micron]

Maybe a bigger crowd than you think.
Asking here because I don't know since I avoided Access graphs as much as possible - in Access can you create a graph with 2 y axes with different scales?
Can you double click on a chart point and retrieve all the related field data from the row on which that point data comes from?

The first, I doubt. The second I seriously doubt. Maybe it's just me/we not fully grasping their structure that leads me to that opinion. Or maybe it those who like them just haven't been confronted with the need for anything more complicated than a single y axis pie or column chart. I honestly don't know.

[Forbes]

I'm definitely in agreement on Excel over Access for charts and graphs, one thing you could also try is having excel pull its data from the DB for the chart/graph and in return have a "form pop-up" display the graph when needed via cmd.button or other means.

You could also make fun use of Share point and Office Online in this situation. let me explain:

1. Import your data controlling the Excel Graph into the Access DB and organize it accordingly with proper headings.
2. Re-create your graph in Excel, using the linked data in the DB
3. Create your graph clear any grid lines or other undesirables until you have it looking the way you would like.
4. Share/protect the workbook once you have completed it.
5. Save the Excel chart to your One Drive or Share Point storage device.
6. Create a "Web Form" bound to your "Share point Storage" filepath=(Excelfilename)
7. Open the Web Form in Form view until you have it looking the way you would like (there are many things you can do to make this look nice)

Let us know what you end up doing!

[ssanfu]

I'm in the crowd with you Micron. That is me waving.........

When I have had to do graphing, I used Excel. So the Access dB has the data, I export the data to a workbook. Then I use automation to create and format the chart.
The first time I add the data, then record the steps to create/format the chart. I convert the Excel code to Access code.

I could click a button, export the (query) date to Excel, format the data if required, create a chart and format the chart, all using automation. A lot of code but once done, it is easy and reliable..


My $0.02.......

[Micron]

Yes, I see you! Nice to have company of your calibre (and Forbes too, who seems to have some SharePoint smarts).

I have done similar to you, but I think a bit more dynamic perhaps. The XL chart was based on one or more dynamic named ranges. The data was exported to the named range(s) via automation, and no matter how many rows there were, the chart never needed to refresh the data reference or be rebuilt.

This is turning into a thread of its own!