Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB6/Access

Posted on 2000-03-13
3
Medium Priority
?
181 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
[X]
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
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 150 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

715 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