Solved

Making a chart in Excel with Visual Basic 6

Posted on 2007-11-16
1
505 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
[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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Visual Studio 2005 text editor 10 56
transition to visual .net from vb6 5 81
VB6 - Check if date format = "MM/DD/YYYY' and check if date also exist 23 80
VB6 Enviroment 3 37
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…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

751 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