Creating Excel charts from MS Access 2007 database using VB 2008.


  • Hello everyone, i have a slight problem with something i am working on. I found this module here:
    I modified it so that instead of Access 2003 it accepts Access 2007 files and instead of creating an Excel 2003 file it creates and Excel 2007 file. I removed the mailing function as it is not necessary for me. Additionally, instead of the file being created automatically, i attempted to implement the file and chart creation through a button click event.  The problem i am having is that with the modified code, once the excel file is created and displayed with the graph and pie chart, it almost immediately closes down. I have tried but without any luck to fix it.  My understanding is there must be an instruction to close the excel file (the VB program is shown as debugging but Excel is closed) This is the modified code that i have now, thank you for any help you might provide:
    Imports System
    Imports System.Data
    Imports System.Data.OleDb
    Imports System.IO
    Imports Microsoft.Office.Interop
    Public Class Form1
     Public conn As New OleDbConnection()
     Public Filename As String
     Public chkexcel As Boolean
     Public oexcel As Excel.Application
     Public obook As Excel.Workbook
     Public osheet As Excel.Worksheet
     Public R As Integer
     Public Sub Dbopen()
      conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & AppDomain.CurrentDomain.BaseDirectory & "db.accdb'"
     End Sub
    Public Sub Dbclose()
      'check and close db connection
      If conn.State = ConnectionState.Open Then
       conn = Nothing
      End If
      'check and close excel application
      If chkexcel = True Then
       osheet = Nothing
       oexcel.Application.DisplayAlerts = False
       oexcel.Application.DisplayAlerts = True
       obook = Nothing
       oexcel = Nothing
      End If
     End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       'File name and path, here i used abc file to be stored in Bin directory in the sloution directory
       Filename = AppDomain.CurrentDomain.BaseDirectory & "abc.xlsx"
       If File.Exists(Filename) Then
       End If
       If Not File.Exists(Filename) Then
        chkexcel = False
        'create new excel application
        oexcel = CreateObject("Excel.Application")
        'add a new workbook
        obook = oexcel.Workbooks.Add
        'set the application alerts not to be displayed for confirmation
        oexcel.Application.DisplayAlerts = True
        'check total sheets in workboob
        Dim S As Integer = oexcel.Application.Sheets.Count()
        'leaving first sheet delete all the remaining sheets
        If S > 1 Then
         oexcel.Application.DisplayAlerts = False
         Dim J As Integer = S
         Do While J > 1
          J = oexcel.Application.Sheets.Count()
        End If
        'to check the session of excel application
        chkexcel = True
        oexcel.Visible = True
        'this procedure populate the sheet
        'save excel file
        'end application object and session
        osheet = Nothing
        oexcel.Application.DisplayAlerts = False
        oexcel.Application.DisplayAlerts = True
        obook = Nothing
        oexcel = Nothing
        chkexcel = False
       End If
      Catch ex As Exception
      End Try
     End Sub
    Sub Generate_Sheet()
      Console.WriteLine("Generating Auto Report")
      osheet = oexcel.Worksheets(1)
      'rename the sheet
      osheet.Name = "Excel Charts"
      osheet.Range("A1:AZ400").Interior.ColorIndex = 2
      osheet.Range("A1").Font.Size = 12
      osheet.Range("A1").Font.Bold = True
      osheet.Range("A1").Value = "Excel Automation With Charts"
      'format headings
      osheet.Range("A3:C3").Font.Color = RGB(255, 255, 255)
      osheet.Range("A3:C3").Interior.ColorIndex = 5
      osheet.Range("A3:C3").Font.Bold = True
      osheet.Range("A3:C3").Font.Size = 10
      'columns heading
      osheet.Range("A3").Value = "Item"
      osheet.Range("B3").Value = "Sale"
      osheet.Range("C3").Value = "Income"
      'populate data from DB
      Dim SQlQuery As String = "select * from Sales"
      Dim SQLCommand As New OleDbCommand(SQlQuery, conn)
      Dim SQlReader As OleDbDataReader = SQLCommand.ExecuteReader
      Dim R As Integer = 3
      While SQlReader.Read
       R = R + 1
       osheet.Range("A" & R).Value = SQlReader.GetValue(0).ToString
       osheet.Range("A" & R).BorderAround(8)
       osheet.Range("B" & R).Value = SQlReader.GetValue(1).ToString
       osheet.Range("B" & R).BorderAround(8)
       osheet.Range("C" & R).Value = SQlReader.GetValue(2).ToString
       osheet.Range("C" & R).BorderAround(8)
      End While
      SQlReader = Nothing
      'create chart objects
      Dim oChart As Excel.Chart
      Dim MyCharts As Excel.ChartObjects
      Dim MyCharts1 As Excel.ChartObject
      MyCharts = osheet.ChartObjects
      'set chart location
      MyCharts1 = MyCharts.Add(150, 30, 400, 250)
      oChart = MyCharts1.Chart
      'use the follwoing line if u want to draw chart on the default location
      'ochart.Location(Excel.XlChartLocation.xlLocationAsObject, osheet.Name)
      With oChart
       'set data range for chart
       Dim chartRange As Excel.Range
       chartRange = osheet.Range("A3", "C" & R)
       'set how you want to draw chart i.e column wise or row wise
       .PlotBy = Excel.XlRowCol.xlColumns
       'set data lables for bars
       'set legend to be displayed or not
       .HasLegend = True
       'set legend location
       .Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
       'select chart type
       '.ChartType = Excel.XlChartType.xl3DBarClustered
       'chart title
       .HasTitle = True
       .ChartTitle.Text = "Sale/Income Bar Chart"
       'set titles for Axis values and categories
       Dim xlAxisCategory, xlAxisValue As Excel.Axes
       xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
       xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
       xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "Items"
       xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
       xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
       xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = "Sale/Income"
      End With
      'set style to show the totals
      R = R + 1
      osheet.Range("A" & R & ":C" & R).Font.Bold = True
      osheet.Range("A" & R & ":C" & R).Font.Color = RGB(255, 255, 255)
      osheet.Range("A" & R).Value = "Total"
      osheet.Range("A" & R & ":C" & R).Interior.ColorIndex = 5
      osheet.Range("A" & R & ":C" & R).BorderAround(8)
      'sum the values from column 2 to 3
      Dim columnno = 2
      For columnno = 2 To 3
       Dim Htotal As String = 0
       Dim RowCount As Integer = 4
       Do While RowCount <= R
        Htotal = Htotal + osheet.Cells(RowCount, columnno).value
        osheet.Cells(RowCount, columnno).borderaround(8)
        RowCount = RowCount + 1
       'display value
       osheet.Cells(R, columnno).Value = Htotal
       'format colums
       With DirectCast(osheet.Columns(columnno), Excel.Range)
        .NumberFormat = "0,00"
       End With
      'add a pie chart for total comparison
      MyCharts = osheet.ChartObjects
      MyCharts1 = MyCharts.Add(150, 290, 400, 250)
      oChart = MyCharts1.Chart
      With oChart
       Dim chartRange As Excel.Range
       chartRange = osheet.Range("A" & R, "C" & R)
       .PlotBy = Excel.XlRowCol.xlRows
       .ChartType = Excel.XlChartType.xl3DPie
       .HasLegend = False
       .HasTitle = True
       .ChartTitle.Text = "Sale/Income Pie Chart"
       .ChartTitle.Font.Bold = True
      End With
     End Sub
    End Class
    Wednesday, October 20, 2010 9:30 AM


  • Found the answer, i removed the last two:

    And the program does not exit now.

    • Marked as answer by Spyros Spyrou Tuesday, October 26, 2010 4:37 AM
    Tuesday, October 26, 2010 4:37 AM

All replies