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

x
?
Solved

Making a chart in Excel with Visual Basic 6

Posted on 2007-11-16
1
Medium Priority
?
516 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 2000 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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 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…
Suggested Courses

705 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