Solved

Making a chart in Excel with Visual Basic 6

Posted on 2007-11-16
1
492 Views
Last Modified: 2008-02-01
I have the following code for putting data into an excel workbook.  However, is there a way to plot the data and then create a chart?

Set ApExcel = CreateObject("Excel.application") 'Creates an object
ApExcel.Visible = True ' So you can see Excel

'ApExcel.Workbooks.Open "\\2000server\JJDP\Referral Database\JJDP Sheets\Attendance Sheet.xls"
ApExcel.Workbooks.Open "C:\Registered Statistic Sheet.xls"

    ApExcel.Workbooks("Registered Statistic Sheet.xls").Sheets("Sheet1").Cells(4, 5).Formula = Com(1)
    ApExcel.Workbooks("Registered Statistic Sheet.xls").Sheets("Sheet1").Cells(4, 6).Formula = Com(2)
    ApExcel.Workbooks("Registered Statistic Sheet.xls").Sheets("Sheet1").Cells(4, 7).Formula = Com(3)
0
Comment
Question by:al4629740
1 Comment
 
LVL 15

Accepted Solution

by:
spprivate earned 500 total points
ID: 20300685
Here is the sample code
Do While Not adrQry.EOF
   If chgtype <> adrQry.Fields("ChangeType") Then
      adrChgType.Find "SystemId = " & adrQry.Fields("ChangeType"), _
                                  , adSearchForward, bkmark
      Set objExcelSI  = objExcelW.Worksheets.add
      objExcelSI.Name = adrChgType.Fields("ChangeType") & " - Issues"
      chgtype         = adrQry.Fields("ChangeType")
      Set objExcelSS  = objExcelW.Worksheets.add
      objExcelSS.Name = adrChgType.Fields("System") & " - Stirs"
      objExcelSI.Cells(1, 1).Value = "Year / Week"
      objExcelSI.Cells(1, 2).Value = "Curent Outstanding"
      objExcelSI.Cells(1, 3)       = "New Issues This Week"
      objExcelSI.Cells(1, 4)       = "Completed Issues This Week"
      objExcelSS.Cells(1, 1).Value = "Year / Week"
      objExcelSS.Cells(1, 2).Value = "Outstanding Stirs"
      objExcelSS.Cells(1, 3).Value = "New Stirs This Week"
      objExcelSS.Cells(1, 4)       = "Completed Stirs This Week"

      Row = 2
      objExcelW.Charts.add
      Set objExcelCI = objExcelW.ActiveChart
      objExcelCI.Activate
      objExcelCI.Name = adrChgType.Fields("System") & " - Issues Chart"
   End If
We have now created a two new sheets: one for the issues (SI) and one for the Stirs/bug fixes (SS). We have also created a chart sheet (the chart sheet will only show a graph of the issues). We will now load the data into the sheet:

objExcelSI.Cells(Row, 1).Value = adrQry.Fields("StatsDate")
objExcelSI.Cells(Row, 2).Value = adrQry.Fields("Curent Outstanding")
objExcelSI.Cells(Row, 3) = adrQry.Fields("New Issues This Week")
objExcelSI.Cells(Row, 4) = adrQry.Fields("Completed Issues This Week")
objExcelSS.Cells(Row, 1).Value = adrQry.Fields("StatsDate")
objExcelSS.Cells(Row, 2).Value = adrQry.Fields("Outstanding Stirs _
                                                This Week")

   adrQry.MoveNext
If we have encounted the end of the recordset or the system/change type has changed, we need to build the chart/graph:

If adrQry.EOF Then
      LastCell = "D" & Mid$(Str$(Row), 2)
      objExcelCI.SetSourceData objExcelSI.Range("a1:" & _
                                                LastCell), _
                                                xlColumns
      objExcelCI.ChartType       = xlLineMarkers
      objExcelCI.Legend.Position = xlLegendPositionBottom
      objExcelCI.HasTitle        = True
      objExcelCI.ChartTitle.Text = objExcelCI.Name
Else
      If chgtype <> adrQry.Fields("ChangeType") Then
         LastCell = "D" & Mid$(Str$(Row), 2)
         objExcelCI.SetSourceData objExcelSI.Range("a1:" & _
                                                   LastCell), _
                                                   xlColumns
         objExcelCI.ChartType       = xlLineMarkers
         objExcelCI.Legend.Position = xlLegendPositionBottom
         objExcelCI.HasTitle        = True
         objExcelCI.ChartTitle.Text = objExcelCI.Name
      End If
   End If
   Row = Row + 1
Loop
The variable Row is incremented as we write a new row, so the line LastCell = "D" & Mid$(Str$(Row), 2) sets LASTCELL to something like D10.

objExcelCI.SetSourceData objExcelSI.Range("a1:" & LastCell), _
                                          xlColumns
This line tells the chart the range of the data it is to use.

objExcelCI.ChartType = xlLineMarkersI want my graph to be a line graph.

objExcelCI.Legend.Position = xlLegendPositionBottomThe legend is to go at the bottom of the screen:

objExcelCI.HasTitle        = True
objExcelCI.ChartTitle.Text = objExcelCI.Name
The chart has a title and the title should be the same as the issues spreadsheet that created it.

Time to close everything now:

objExcelA.DisplayAlerts = False
objExcelW.SaveAs dlgFileLocation.FileName

objExcelA.Quit

Set objExcelA  = Nothing
Set objExcelW  = Nothing
Set objExcelSI = Nothing
Set objExcelSS = Nothing
Set objExcelCI = Nothing
Set objExcelCS = Nothing

If adrQry.State     = adStateOpen Then
   adrQry.Close
End If
If adrChgType.State = adStateOpen Then
   adrChgType.Close
End If

Set adrQry      = Nothing
Set adrChgType  = Nothing

Me.MousePointer = vbNormal

Exit Sub
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now