Solved

VB6/Access

Posted on 2000-03-13
3
178 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 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

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…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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
Course of the Month7 days, 23 hours left to enroll

617 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