Solved

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

Posted on 2006-11-02
29
814 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:bobbat
  • 18
  • 10
29 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
See this link (contains source code) :

http://support.microsoft.com/?kbid=200551
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
see if this will help

How to Create an MS Graph in PowerPoint Using Access
http://support.microsoft.com/?kbid=200551
0
 

Author Comment

by:bobbat
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
According to the link, you may also need to load Excel text converter for Powerpoint.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
An excellent tutorial for you:

            http://msdn2.microsoft.com/en-us/library/aa159920(office.11).aspx
0
 

Author Comment

by:bobbat
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 

Author Comment

by:bobbat
Comment Utility
Can you give me a reference or show me how to do Dates?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 

Author Comment

by:bobbat
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:bobbat
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
OK, will look into this sometime today (Monday) and get back to you..
0
 

Author Comment

by:bobbat
Comment Utility
Many thanks in advance.  This is a tough one.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 

Author Comment

by:bobbat
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 

Author Comment

by:bobbat
Comment Utility
OK!
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 

Author Comment

by:bobbat
Comment Utility
I value your time and have lots of patience.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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
 

Author Comment

by:bobbat
Comment Utility
OK
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
Comment Utility
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
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now