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

Question

Hello everyone, i have a slight problem with something i am working on. I found this module here:

http://www.codeproject.com/KB/office/Excel_Automation.aspx

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'"



  conn.Open()



 End Sub



Public Sub Dbclose()

  'check and close db connection

  If conn.State = ConnectionState.Open Then

   conn.Close()

   conn.Dispose()

   conn = Nothing

  End If

  'check and close excel application

  If chkexcel = True Then

   osheet = Nothing

   oexcel.Application.DisplayAlerts = False

   obook.Close()

   oexcel.Application.DisplayAlerts = True

   obook = Nothing

   oexcel.Quit()

   oexcel = Nothing

  End If



  End



 End Sub



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click



  Try

   Dbopen()

   '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

    File.Delete(Filename)

   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

      oexcel.Application.Sheets(J).delete()

      J = oexcel.Application.Sheets.Count()

     Loop



    End If

    'to check the session of excel application

    chkexcel = True





    oexcel.Visible = True

    'this procedure populate the sheet

    Generate_Sheet()

    'save excel file

    obook.SaveAs(Filename)

    'end application object and session

    osheet = Nothing

    oexcel.Application.DisplayAlerts = False

    obook.Close()

    oexcel.Application.DisplayAlerts = True

    obook = Nothing

    oexcel.Quit()

    oexcel = Nothing

    chkexcel = False



   End If

  Catch ex As Exception

   



   Finally



   Dbclose()



  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:I1").Merge()

  osheet.Range("A1").Value = "Excel Automation With Charts"

  osheet.Range("A1").EntireColumn.AutoFit()

  '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("A3").BorderAround(8)

  osheet.Range("B3").Value = "Sale"

  osheet.Range("B3").BorderAround(8)

  osheet.Range("C3").Value = "Income"

  osheet.Range("C3").BorderAround(8)

  '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.Close()

  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)

   .SetSourceData(chartRange)

   'set how you want to draw chart i.e column wise or row wise

   .PlotBy = Excel.XlRowCol.xlColumns

   'set data lables for bars

   .ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)

   '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

   Loop

   'display value

   osheet.Cells(R, columnno).Value = Htotal

   'format colums

   With DirectCast(osheet.Columns(columnno), Excel.Range)

    .AutoFit()

    .NumberFormat = "0,00"

   End With

  Next

  '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)

   .SetSourceData(chartRange)

   .PlotBy = Excel.XlRowCol.xlRows

   .ChartType = Excel.XlChartType.xl3DPie



   .ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent)

   .HasLegend = False

   .HasTitle = True

   .ChartTitle.Text = "Sale/Income Pie Chart"

   .ChartTitle.Font.Bold = True

  End With

 End Sub







End Class

Answer 1

If you dont get an answer to this issue here , you may consider posting it in excel developers forum http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

kaymaf

---

CODE CONVERTER SITE

http://www.carlosag.net/Tools/CodeTranslator/.

http://www.developerfusion.com/tools/convert/csharp-to-vb/.

Answer 2

Found the answer, i removed the last two:

obook.Close()

oexcel.Quit()

And the program does not exit now.