Solved

VB6/Access

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

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

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…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

730 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