[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

VB6/Access

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
knanji
Asked:
knanji
1 Solution
 
AshokKumarCommented:
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
 
soosairajCommented:
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
 
knanjiAuthor Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now