1. Forums
  2. Programming
  3. Visual Basic 6.0
  4. Create an Excel Chart from Visual Basic 6.0

Create an Excel Chart from Visual Basic 6.0

Submitted by teejaygenius on
I am working on a rainfall regression analysis for 30 Years in an excel file. I have been able to link to the excel file from vb6.0 and now my problem is how do I use the data in the excel file to plot a graph in another sheet inside the same excel file. Below is the code. I have a combobox named cmdLocation which is used to select the state among 3 states of data in the excel file and a command button to be able to read the file named cmdRead
  1. Private Sub cmdRead_Click()
  2. Dim excel_app As Excel.Application
  3. Dim workbook As Excel.workbook
  4. Dim sheet As Excel.Worksheet
  5. Dim sheet2 As Excel.Chart
  6. Dim StartCell As Integer
  7. Dim XValues(30), YValues(30) As Double
  8. Dim d As Integer
  9. Dim Reg As New RegressionObject
  10.  
  11. Const R = 100
  12. Dim P(1 To 200) As PointType
  13.   Dim X As Double
  14.   Dim Y As Double
  15.     Dim LocationValue As String
  16.    
  17.     LocationValue = cmbLocation.Text
  18.    
  19.     If LocationValue = "" Then
  20.         MsgBox "Please select location"
  21.         cmbLocation.SetFocus
  22.     Else
  23.    
  24.  
  25.         ' Get the Excel application object.
  26.         Set excel_app = New Excel.Application
  27.    
  28.         ' Make Excel visible (optional).
  29.         excel_app.Visible = True
  30.    
  31.         ' Open the workbook read-only.
  32.         Set workbook = excel_app.Workbooks.Open( _
  33.             FileName:=App.Path & "\RegressionData.xls", ReadOnly:=True)
  34.    
  35.         ' Get the first worksheet.
  36.         Set sheet = workbook.Sheets("Sheet2")
  37.         Set sheet2 = workbook.Sheets("Chart1")
  38.         If LocationValue = "Lagos" Then
  39.         StartCell = 2
  40.         ElseIf LocationValue = "PHarcourt" Then
  41.         StartCell = 33
  42.         ElseIf LocationValue = "Ilorin" Then
  43.         StartCell = 76
  44.         End If
  45.        
  46.         For d = 1 To 30
  47.             XValues(d) = Val(sheet.Cells(d + StartCell, 2).Value)
  48.             YValues(d) = Val(sheet.Cells(d + StartCell, 14).Value) * 100
  49.         Next d
  50.        
  51.         For d = 1 To 30
  52.             sheet.Cells(d + 2, 100).Value = XValues(d)
  53.             sheet.Cells(d + 2, 101).Value = YValues(d)
  54.         Next d
  55.         'MsgBox LocationValue
  56.         sheet.Cells(3, 102).Value = LocationValue
  57.         sheet2.Select
  58.         ' Save the changes and close the workbook.
  59.         'workbook.Close SaveChanges:=True
  60.    
  61.         ' Close the Excel server.
  62.         ' excel_app.Quit
  63.     MsgBox "Done"
  64. End If
  • 143 views