PDA

View Full Version : Solved: creating pie chart with data from Access



talytech
02-17-2009, 02:41 PM
I have a MS Access database that displays totals for specific categories. I would like to develop a Pie Chart in Excel that would link to my Access database for the values. How would I link to the Access to get the data?

My second issue is: I would like to call this spreadsheet with the Pie Chart from a button on a form in Access.

Kenneth Hobs
02-17-2009, 05:00 PM
You can use a data query or ado. For ado, see http://www.erlandsendata.no/english/index.php?t=envbadac

Not sure why you would want to round robin like that. Why not just do it all in access?

talytech
02-18-2009, 02:00 PM
I didn't know you could do that in Access. I would love to get the job done in one spot. Could you help me along the way? I just need to be pointed in the right direction. Thanks

Kenneth Hobs
02-18-2009, 02:33 PM
Search for Pie in Access's help. One entry will be Pivot Chart View: http://office.microsoft.com/en-us/access/HA010345801033.aspx?pid=CH062526661033

talytech
02-19-2009, 07:48 AM
Thanks for that information and I was able to create my chart using MS Access. I know why I need to do it in excel. My report in Access will not export or merge with MS Word or Excel with the chart object it only shows the data and in order for me to route this report via email the recipient has to have the snapshot viewer which many of them don't. Is there a way around this?

Kenneth Hobs
02-19-2009, 08:05 AM
While we could import your data to Excel and make the chart, is it worth it? You then have to make sure that they have Excel.

You can probably do it all in Access's vba. In Excel, we can export the chart to a filename.
e.g.
Worksheets("Sheet1").ChartObjects(1) _ .Chart.Export _ FileName:="current_sales.gif", FilterName:="GIF"

I'm sure Access has something similar. Your vba code in Access can then email the file as an attachment.

However, if you still want to use Excel, let us know. A sample Access file will be needed.

talytech
02-19-2009, 08:55 AM
While we could import your data to Excel and make the chart, is it worth it? You then have to make sure that they have Excel.

You can probably do it all in Access's vba. In Excel, we can export the chart to a filename.
e.g.
Worksheets("Sheet1").ChartObjects(1) _ .Chart.Export _ FileName:="current_sales.gif", FilterName:="GIF"

I'm sure Access has something similar. Your vba code in Access can then email the file as an attachment.

However, if you still want to use Excel, let us know. A sample Access file will be needed.


I would prefer to use Access and have all this accomplished with a click of a button. Access doesn't give me the capability to customize my chart with percentage values like in Excel. See my attached file in Excel. Basically what I did was ran my report in Access which has the data and the pie chart. Then I exported the report into Excel. Then I had to manually choose the options in Excel to create the pie chart again in Excel.

talytech
02-19-2009, 09:31 AM
OK .. disregard the previous post. Now the client is saying that she simply would like to copy and paste the chart into powerpoint, so I think that if I could do like you did with the Excel chart (export it to a .jpg file) and attach it to an email that would be perfect. Please let me know if this is doable. I'm searching the forum to see if and how this can be done.

Kenneth Hobs
02-19-2009, 01:41 PM
I am confused. Did you want to do it all in Excel or from Access?

A sample mdb would help get you the best help.

Code Steps if doing it all in Excel.
1. Import the data from Access using ADO.
a. If you have the sql string, we can go from there.
2. Update the Chart.
3. Export the chart to a filename.
4. Use the outlook object model, CDO or... to create and send the email with (3).

talytech
02-19-2009, 03:54 PM
OK ... well I actually was able to do it all in access.

1. I have a button that creates the Excel File.
DoCmd.OutputTo acOutputQuery, "qryTotalRecruitsbyStage", acFormatXLS, "C:\Documents and Settings\e3utbl\Desktop\myPie.xls", False


2. Then calls this function "addChart".

Dim xlApp As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open("C:\Documents and Settings\e3utbl\Desktop\myPie.xls").Sheets(1)
With xlApp
.Application.Sheets("qryTotalRecruitsbyStage").Select
'.Application.Range("A1:B6").Select
'.Application.Selection.Font.Bold = True
'.Application.Range("A1:B6").Select
'.Application.ActiveCell.FormulaR1C1 = "Recruits"
.Application.Charts.Add
.Application.ActiveChart.ChartType = xlPie
.Application.ActiveChart.HasTitle = True
.Application.ActiveChart.ChartTitle.Text = "Total Recruits by Stage for 3900"

.Application.ActiveChart.SetSourceData Source:=Sheets("qryTotalRecruitsbyStage").Range("A2:B6"), PlotBy:=xlColumns
.Application.ActiveChart.Location Where:=xlLocationAsObject, Name:="qryTotalRecruitsbyStage"
.Application.ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent, LegendKey:=False, HasLeaderLines:=True
.Application.ActiveWorkbook.Save
.Application.ActiveWorkbook.Close
.Application.Quit
End With
Set xlApp = Nothing
Set xlSheet = Nothing
vStatusBar = SysCmd(acSysCmdClearStatus)



But I don't know how to export the chart to a gif filename. Can you help me with that?

alexgiurca
02-20-2009, 05:03 AM
Hy!

Please try the solution I have posted here:
http://www.vbaexpress.com/forum/showthread.php?t=25157

Let me know if it helped you and post the code once you finish your work.

Also don't forget to change the thread status to Solved.

Regards,
Alex

talytech
02-20-2009, 08:28 AM
Can you size the plot area for the pie chart. When I create the pie chart through code it is really small. I can manually size it but how can I size it through code?

I'm sorry .. Do I need to start a new thread for this question? I'm not sure if I start a new one or continue with this one.

alexgiurca
02-20-2009, 04:07 PM
Hello again!

You have to do the following:

1. create the piechart in excel or directly import it to excel (if you already have it in gif/bmp format).
2. when you import/export it you have to keep track , either in a couple of variables or in a database table, of the width, height and seize of the piechart in order not to get a distorted piechart/image.

To resize the picture just use something like:

xlsht.Shapes.AddPicture "C:\Data_Local\picexport.bmp", True, True, Left:=size2, Top:=size, Width:=rs!imgw, Height:=rs!imgh

-!!! Before you do , please export the chart to a temporary file on HDD, delete it from the sheet, import the chart back into the sheet and use this code snippet to change the size programatically. xlsht is of type SHEET. rs!imgw, rs!imgh, size2, size can be changed to numbers or variables at your conveniance.

Read carefully my post and download the attached files. Once you understand how I made it work, you will also succeed. Although I used my program to import/export pictures from excel , it will also work for any OLE object, be it a chart , drawing, custom shape etc. The point is that you have to save it to the clipboard (but the attached functions - from my project module - will do it for you) and then to HDD. From there you take the picture back to excel.

Let me know if it worked for you. If not, post your code and I'll have a look at it.