Solved

Excel Chart in VB6

Posted on 2001-06-16
9
708 Views
Last Modified: 2008-03-17
I would like to be able to display an Excel chart on a VB6 form. Could someone please point me in the right direct as to how to code the connection. L'd prefer to learn how to code it rather than using a bound control. Thanks in advance,

Dys
0
Comment
Question by:Dys060801
  • 5
  • 3
9 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 6198447
Try this. To load a excel file, you need to create a DSN named "Excel" that points to your that file. Note that when you click on the Open button from the below program, it will pop up a open dialog. This is a bit redundant since the file to be opened is actually based on the DSN pointer and not the file you choose. You can always remove the common dialog control and the relevant code to get away of the open dialog.

http://www.vbdiamond.com/Sources/ViewSource.asp?File=1&ID=16431

hongjun
0
 

Author Comment

by:Dys060801
ID: 6198656
Hi hongjun,

Thanks for your response. It gave me a little bit of help in understanding the relationship between Excel and VB. However, it didn't give me help in the specific area I'm interested in. Let me explain.

I am developing a VB application connected to an MS Access database. A SQL query in my application will return a group of numbers to my application in the form of a recordset (which I could convert to an array).

I would like to send those numbers in the recordset to an Excel spreadsheet, have Excel produce a Pie Chart and then have the Pie Chart returned to my application and displayed on a Form in my application.

I am assuming that returning and displaying a Pie Chart (probably an object) is different than returning a spreadsheet depositing the data into an ADO Recordset.

Hope this clarifies the problem I have.

Thanks,
Dys
0
 
LVL 1

Expert Comment

by:Sorklin
ID: 6198704
Why not embed an OLE object in your form and make it an excel object.  Then you channel the data into that, run the graph and display the result when its ready?

I've done that with word objects.  There are controls you can add to the form, I think you want OLE/COM object and check the properties.

I'm away from work, otherwise I'd check out exactly what to do...
0
 

Author Comment

by:Dys060801
ID: 6198897
Thanks for the response Sorklin. Its seems to be getting closer to the solution I need. However, I'd appreciate some direction on the object insertion and coding involved when you are back at work. Haven't played around much with OLE/COM objects. I'm not in a hurry on this one, I can wait...

Dys  :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Expert Comment

by:Sorklin
ID: 6202551
I'm working on this, so give me a bit of time...
0
 

Author Comment

by:Dys060801
ID: 6202666
No problem I'll wait.
0
 
LVL 1

Accepted Solution

by:
Sorklin earned 50 total points
ID: 6202845
Okay.  I created a form with an OLE object (oleXLChart) and a command button (command1).  When I created the Ole object, I set it to New, Excel Chart (though that really doesnt matter).  Then I pasted the following code into the code sheet for the form:
************************************************
Option Explicit

Dim xlData As Excel.Application

Private Sub Command1_Click()

    'OleXLChart is an ole object that you've set to be a MS Excel chart.
    'The linking below will supply it with data and the chart.
   
   
    'You want to keep track of what your range would be with the data
    'place it after the temporary file you made.
    oleXLChart.SizeMode = 3 'zoom
    oleXLChart.CreateLink "c:\temp\xldata.xls", "A1:B3"
    oleXLChart.Visible = True
   
End Sub

Private Sub Form_Load()

    Set xlData = New Excel.Application
   
    With xlData
        .Workbooks.Add
        .Visible = True
       
        'This is where you would paste the data you've gathered
        'from whatever sources.
        .ActiveSheet.Paste
        .Charts.Add
        'Do whatever you need to to format the file correctly.
        .ActiveChart.ChartType = xlPie
       
        'Save the file in a temporary place so we can create a link.
        'Check first for the existence of the file and if so, delete it.
        If Dir("c:\temp\xldata.xls") <> "" Then
            Kill "c:\temp\xldata.xls"
        End If
        .ActiveWorkbook.SaveAs FileName:="c:\temp\xldata.xls"
    End With 'xlData
   
End Sub

Private Sub Form_Unload(Cancel As Integer)

    xlData.Quit
   
    Set xlData = Nothing

         If Dir("c:\temp\xldata.xls") <> "" Then
            Kill "c:\temp\xldata.xls"
        End If

End Sub
************************************************

This is a big kludge and there is a lot of room for clean up and customizing.  But the idea is that I've created an excel object, written the data to it.  In that excel object, I've created and formatted the chart the way I would like it to be seen.  Then I've temporarily saved the file.  When the user clicks command1 button, the ole form is linked to the file and the graph is displayed.  Works nicely on my system.

There are lots of things I didn't set and lots of things that can be cleaned, tweaked and prodded, but this might get you pointed in the right direction.
0
 

Author Comment

by:Dys060801
ID: 6202869
Thanks very much Sorklin. I'm cellphone communicating right now, but when I get back to my notebook later today I'll give it a try.
0
 

Author Comment

by:Dys060801
ID: 6215541
Thanks Sorklin. It took me a while to get back to this problem, but after applying your solution with a few minor adjustments it worked fine. Many thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help in WHSCRIPT 9 47
VB 6.0 printer how to align 6 58
How to debug this code 7 56
Using an encrypted  value to decrypt and display contents in vb6 9 50
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

896 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

13 Experts available now in Live!

Get 1:1 Help Now