How do I Export a Microsoft Graph Chart Object (Class: MSGraph.Chart.8) in an Access 2003 Form to PowerPoint?

I have a form in my Access 2003 application (.mdb/.mde file) where users can select the data and chart type (2 or 3d: bar, line, column, pie, area) to display in a Microsoft Graph Chart object.  How do I Export this chart object to PowerPoint?
bobbatAsked:
Who is Participating?
 
puppydogbuddyConnect With a Mentor Commented:
bobbat,
Sorry I could not get back to you sooner, but in addition to being busy with clients, my development PC crashed on Friday.....and is still down.

I think the following link holds a solution for you.  Try this:
         http://support.microsoft.com/kb/198462

1.  Place the query "Category Sales for 1995"  in design view.
2. go to the sql view and eliminate the parameter statement we created previously.
3. go to the citeria row in design view and wrap your parameterwith the eval function
    Eval(Forms!frmCategory!txtCategory)
4. Rerun your function

This should work since Eval outputs the value of the parameter, ie. Beverages, which worked for you before.  Let me know.
0
 
puppydogbuddyCommented:
See this link (contains source code) :

http://support.microsoft.com/?kbid=200551
0
 
Rey Obrero (Capricorn1)Commented:
see if this will help

How to Create an MS Graph in PowerPoint Using Access
http://support.microsoft.com/?kbid=200551
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
bobbatAuthor Commented:
Great minds must think alike!

The link shows how to 'Create a graph on a PowerPoint Slide using a Microsoft Access table.' and I may have to do that.  But I don't want to because I have already created the graphic in the Access form.  I would have to modify the referenced code to produce any of the ten charts the form can produce and to produce more than one chart and those charts have to already exist in the PowerPoint document (I think), etc.

What about Plan B--export the chart to the desktop and save it as a .GIF file.  I have used the following line of code successfuly (it creates the GIf on the desktop):

Me.GraphFX.Object.Application.Chart.Export FileName:="C:\Documents and Settings\...\Desktop\currentchart.gif", FilterName:="GIF"

 but then I get the following error and Access crashes:
The operation on the Chart object failed.  The OLE server may not be registered.  To register the OLE server, reinstall it.

Below is the code for the export command button along with the Access help text for Export:

Any ideas?

Private Sub cmdExport_Click()

'   Exports the chart in a graphic format. Returns Boolean.
'       expression.Export(Filename, FilterName, Interactive)
'       expression---> Required. An expression that returns one of the objects in the Applies To list.
'       Filename-----> Required String. The name of the exported file.
'       FilterName---> Optional Variant. The language-independent name of the graphic filter
'           as it appears in the registry.
'       Interactive--> Optional Variant. Optional Variant. True to display the dialog box that
'           contains the filter-specific options. If this argument is False, Microsoft Excel
'           uses the default values for the filter. The default value is False.

'   This example exports chart one as a GIF file.
'       Worksheets("Sheet1").ChartObjects(1).Chart.Export FileName:="current_sales.gif", FilterName:="GIF"
       
    Me.GraphFX.Object.Application.Chart.Export FileName:="C:\Documents and Settings\...\Desktop\currentchart.gif", FilterName:="GIF"
 
End Sub
0
 
puppydogbuddyCommented:
The first thing  that comes to mind:

Make sure you have the gif graphics filter installed.  It comes with Word, but is not automatically installed. See link toward bottom:
http://support.microsoft.com/kb/q235928/
0
 
puppydogbuddyCommented:
According to the link, you may also need to load Excel text converter for Powerpoint.
0
 
puppydogbuddyCommented:
The second thing....I believe you are correct in stating that the charts have to already be on your PP slides, but the data source for your charts is probably a query......the query is what you will export to Powerpoint.
0
 
puppydogbuddyCommented:
An excellent tutorial for you:

            http://msdn2.microsoft.com/en-us/library/aa159920(office.11).aspx
0
 
bobbatAuthor Commented:
I want to use the code at http://support.microsoft.com/?kbid=200551 but this code only works if the parameters are inside the query.  For example the following code works: SELECT Inventory.[Item Number], Inventory.[Quantity in Stock] FROM Inventory WHERE (((Inventory.[Item Number])="2007 F"));

The following code results in Error: "3061, Too Few Parameters, Expected X".

The problem I have is that all my queries obtain parameters from forms such as: SELECT Inventory.[Item Number], Inventory.[Quantity in Stock] FROM Inventory WHERE (((Inventory.[Item Number])='" & Me.txtModelNumber & "'));

I have tried to use the CreateQueryDef method to write queries but these have the form reference also.  How do I write explicit queries?  Or do I have to create a recordset and save it as a table?

0
 
puppydogbuddyCommented:
bobbat,
I think all you have to do is declare your parameters before executing the select statement.
You may need to use a fully qualified reference ([Forms]![YourForm]![txtModelNumber]) if Access doesn't recognize the short reference, but try the syntax this way and see what happens:

PARAMETERS [txtModelNumber] Text(255);
SELECT Inventory.[Item Number], Inventory.[Quantity in Stock]
FROM Inventory
WHERE (((Inventory.[Item Number])='" & Me.txtModelNumber & "'));
0
 
puppydogbuddyCommented:
if the fully qualified reference is needed, you will replace the Me syntax in the query itself as well as in the parameter.  The Text(255) in the parameter statement refers to the Data Type and length for Text Data type.  I used max 255, but if your parameter's field length is defined as 20 in the table for example, then use Text(20).
0
 
bobbatAuthor Commented:
Can you give me a reference or show me how to do Dates?
0
 
puppydogbuddyCommented:
if you mean date parameters, here is a sample from one of the queries that I have used:

PARAMETERS [From Date] DateTime, [To Date] DateTime;
SELECT Proj.projid, Proj.projTitle, Proj.projStart, ProjActivty.weekEnding, DateDiff("d",[From Date],[To Date]) AS NumDays
FROM Proj INNER JOIN ProjActivty ON Proj.projid = ProjActivty.projid
WHERE (((Proj.projStart) Between [From Date] And [To Date]));

0
 
bobbatAuthor Commented:
The parameterized query works and the graphic is created properly in the Access form; however, the fully qualified references are present in the query so the code in  http://support.microsoft.com/?kbid=200551 rejects it.

Any other ideas?
0
 
puppydogbuddyCommented:
So, you are saying that the parameter query syntax, using the fully qualified reference to the pararmeter, now works to update the Charts in the Access form, but that the PowerPoint slide does not activate ???.....and that it did work when you had the actual value of the parameters "inside the query" (as you put it)  ??.

To be sure I understand the flow of what you are trying to do, please confirm or correct the following:  You have a PowerPoint slide presentation containing Charts that you are updating via parameter queries passed through an Access form to the slide module code supplied by Microsoft in the KB article.  This form is strictly a main form with charts, and is not a subform.

It would be helpful, in addition to confirming/correcting the above process description, if you would provide more details as to the error message you got when you passed the parameter query to the Slide module.  Also, if you would post the relevant portions of the code that you perssonalized with the specifics of your PowerPoint presentation.  The problem could be something as simple as using the bang operator-----> ! instead of the period operator, or the wrong fully qualified reference with respect to the slide..  I need to see your code to see if I can spot any errors in syntax.

0
 
bobbatAuthor Commented:
The code in the KB article works when the parameters "inside the query".  To see the exact problem open Northwind Traders.mdb.  Follow the KB article by pasting in the module code and then type CreateGraphFromFile("C:\MyPPT.ppt", "Category Sales for 1995", "") after the question mark in the intermediate window.  You will create the MyPPT.ppt file on your C drive with a chart in it.

Now create a new form (frmCategory) with a text box (txtCategory).  In the ControlSource Box type ="Beverages".  In the query "Category Sales for 1995" [yours might be 1997] in the criteria box under CategoryName type [Forms]![frmCategory]![txtCategory].  The SQL should be SELECT DISTINCTROW [Product Sales for 1995].CategoryName, Sum([Product Sales for 1995].ProductSales) AS CategorySales FROM [Product Sales for 1995] GROUP BY [Product Sales for 1995].CategoryName HAVING ((([Product Sales for 1995].CategoryName)=[Forms]![frmCategory]![txtCategory]));

Type CreateGraphFromFile("C:\MyPPT.ppt", "Category Sales for 1995", "") in the intermediate window again.  You will get Error: "3061, Too Few Parameters, Expected 1".

It seems like you are in PowerPoint when the chart is created thus it does not see the form frmCategory in Access.
0
 
puppydogbuddyCommented:
OK, will look into this sometime today (Monday) and get back to you..
0
 
bobbatAuthor Commented:
Many thanks in advance.  This is a tough one.
0
 
puppydogbuddyCommented:
bobbat,
will work on it this afternoon.  In the meantime, you should download this sample 2003 PowerPoint presentation using an Access form.  I can't use it because I have Access 2000.

                   http://www.microsoft.com/downloads/details.aspx?FamilyId=5F0C22C8-A61D-41A9-AF1D-C21255E601CB&displaylang=en
0
 
puppydogbuddyCommented:
bobbat,
you are right about this being a tough one, but woth the following modifications to convert the code to te querydef method we are a little closer to solving it. Instead of te previous message, I get the message "can't find Access form Categories ...", which makes sense because I created the control txtCategory in the existing Categories form, rather create a new form. Keep that in mind as you look over the code changes below.  Also, keep in mind that my query is "Category Sales for 1997" no 1995.

Dependng how the day goes with clients, I may not have any more time to look at this today.

--------------------------------------------------------------------------------------------
   Dim CGFF_DB As DAO.Database, CGFF_Qdf As DAO.QueryDef, CGFF_Parm As DAO.Parameter    'CGFF_TD As DAO.TableDef
   Dim CGFF_Rs As DAO.Recordset, CGFF_field As DAO.Field
   Dim CGFF_PwrPntloaded As Boolean
   Dim lheight, lwidth, LLeft, lTop As Single

   ' See if the CGFF Table already exists.
   If IsTableQuery("", CGFF_Tablename) Then
      Set CGFF_DB = CurrentDb()
      Set CGFF_Qdf = CGFF_DB.QueryDefs("Category Sales for 1997")
        CGFF_Qdf.Parameters(1) = [Forms]![Categories]![txtCategory]
         
      Set CGFF_Rs = CGFF_Qdf.OpenRecordset(CGFF_Qdf, dbOpenSnapshot)
      On Error GoTo ERR_CGFF
-------------------------------------------------------------------------------------------------------
0
 
bobbatAuthor Commented:
I downloaded the file (OfficeAccess2PowerPointSample.exe) from the link but all it did was transfer text to PowerPoint.

Is it possible to use a recordset rather than a table or query?  Seems like the hard part would be populating the graph datasheet from the recordset by iteration rather than by a direct link to a table or query in Access.

My 'Access Cookbook' by Getz, Litwin, & Baron shows how to Export and Create a chart in Excel . . .
0
 
puppydogbuddyCommented:
Yes, it is possible and we may have to do that.  But first, I want to check something out.  If you ran my revised code, the message you makes me believe we are almost there. PowerPoint knows that the parameter exists on the Categories form (in my code) /frmCategory(your code), but it can't see the form.  That pursuades me that it  has something to do with the form not being made accessible to Powerpoint.  So, my next step would be to look at the code and determine if maybe the anwer is that we have to declare a form object in PowerPoint and set it equal to the Access form.  
0
 
bobbatAuthor Commented:
OK!
0
 
puppydogbuddyCommented:
bobbat,
Just to bring you up to date: Am investigating the feasibility of using the ShellExecute API function , and am also looking over this link:

      http://www.rdpslides.com/pptfaq/FAQ00496.htm

these things take time and I have to take care of clients,.....so patience, please!
0
 
puppydogbuddyCommented:
PS:  the link has a link called "Create Graph from Text" that looks like it could be the solution. Feel free to try it on your own.
0
 
bobbatAuthor Commented:
I value your time and have lots of patience.
0
 
puppydogbuddyCommented:
bobbat,
I am busy with clients today, but I just realized the link to the tutorial  I gave you at the beginning of the post probably had the answer you are looking for ...it gives two methods of controlling PPT from Access
1.table method
2. form method-see this link    http://msdn2.microsoft.com/en-us/library/aa159920(office.11).aspx#officeaccess2powerpoint_introduction

scroll down until you get to form method.  The sample file requires Access 2003, which I don' have....but I can follow the written code.

Try it and let me know.
0
 
bobbatAuthor Commented:
OK
0
 
puppydogbuddyCommented:
bobbat,
I wondered why I hadn't heard from you, and I just realized I probably forgot to put quotes around the expression as per the MS example in the link I gave you, and you figured it out on your own.....
                            Eval("Forms!frmCategory!txtCategory")
Glad you got it resolved.  Thanks for the points and grade.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.