Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

VB6/Access

Posted on 2000-03-13
3
173 Views
Last Modified: 2010-05-02
I am using VB to create a query to retrieve information from an Access database.  Once i have this info, i need to send it to Excel to create a pie chart.  How do i do this.  I am an entry level programmer please provide an answer with comments.  Thanx

knanji
0
Comment
Question by:knanji
3 Comments
 

Expert Comment

by:AshokKumar
ID: 2614831
U sure that u have to use only a pie chart object of Excel. Vb itself has a chart object. All u have to do is to include the component, place it in the form, pass the values and the chart wll be displayed as per your chart type. Do get back to me if you need Vb or excel.
0
 

Accepted Solution

by:
soosairaj earned 50 total points
ID: 2615013
Hello friend..
  samething i did.  you can use my logic..

write your own code in form

strsql = "your sql "
           
 Set qdf = db.CreateQueryDef("", strsql)
 qdf.Parameters("StartDate") = StartDate
 qdf.Parameters("EndDate") = EndDate

 
   Set Xlunitrs = qdf.OpenRecordset(dbOpenDynaset)
    heading = "Incidents Reported During "
    Graph = xl3DPie
   
    Call ExcelGraphPIECount.Graph(Xlunitrs, heading, Graph, graphcolour)






   write this code in Modules


module name is  : ExcelGraphPIECount

Public Sub Graph(Xlunitrs As Recordset, heading As String, Graph As String, graphcolour As String)
 
 ' Checking BlackAndWhite/.Color graph

If graphcolour = "C" Then
    y = False
  ElseIf graphcolour = "B" Then
    y = True
 Else
    MsgBox "Error in selecting in Graph Color.. ", vbCritical, "Warning Message"
    Exit Sub
 End If
 
   Set App1 = CreateObject("Excel.Application")
   App1.Visible = True
   App1.Workbooks.Add

    ''  Column Headings..

   For i = 0 To Xlunitrs.Fields.Count
      Select Case i
        Case 0
           App1.Range("A" & 1).Value = Xlunitrs.Fields(i).Name
        Case 1
           App1.Range("B" & 1).Value = Xlunitrs.Fields(i).Name
        End Select
   Next i
                '-----------------------
'   Fill the Cell Values from Database

    Xlunitrs.MoveLast
          k = Xlunitrs.RecordCount
         
   Xlunitrs.MoveFirst
 
   For i = 1 To k
     For j = 0 To Xlunitrs.Fields.Count - 1
      Select Case j
       Case 0
         App1.Range("A" & i + 1).Value = Xlunitrs.Fields(j).Value
       Case 1
        App1.Range("B" & i + 1).Value = Xlunitrs.Fields(j).Value
       End Select
      Next j
     Xlunitrs.MoveNext
   Next i
     ' ''''''''''''''''''''''''''''''''
Select Case Xlunitrs.Fields.Count
       Case 1
       App1.Range("A1:A" & k + 1).Select
       Case 2
       App1.Range("A1:B" & k + 1).Select
       Case 3
       End Select
       
   Set App1Chart = App1.Charts.Add()
  App1Chart.ChartType = Graph
   With App1Chart
    .HasTitle = True
    .ChartTitle.Characters.Text = "SAFE TRACK " & Chr(10) & heading
    .HasLegend = True
    .Legend.Position = xlLegendPositionBottom
    .ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent, LegendKey _
        :=False, HasLeaderLines:=True
   End With
   
   App1Chart.ChartTitle.AutoScaleFont = True  
'  incresae the title font and Bold

       With App1Chart.ChartTitle.Font
        .Name = "Times New Roman"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
   
     
    With App1Chart.PlotArea.Border             ' no border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
   
   With App1Chart.PlotArea.Interior        ' chnage background color
         .ColorIndex = 2
       .PatternColorIndex = 1
       .Pattern = Solid
   End With
   
   App1Chart.Legend.Delete
   
 App1.Selection.ShapeRange.IncrementLeft App1Chart.ChartArea.Width - 64
    App1.Selection.ShapeRange.ScaleWidth 0.51, msoFalse, msoScaleFromBottomRight
    App1.Selection.ShapeRange.ScaleHeight 0.51, msoFalse, msoScaleFromTopLeft
   
   App1Chart.PageSetup.BlackAndWhite = y
   
    Set App1 = Nothing
    Set App1Chart = Nothing

End Sub


0
 

Author Comment

by:knanji
ID: 2673688
Thank you for your answer.  I am sorry that I did not respond sooner.   Your answer was quite interesting;however, I did not end up using it as I was able to find out a different way before I received your response.  Nevertheless, this should not take away from your creative response.  Thanks.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
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…

856 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