1

I am developing a VB10 application which will log the data to MS Access database and will generate a report in Excel. What I propose to do is every time when the user clicks "Generate Report" button, a new Excel file will open and the data from Access file will be populated in Excel. The user has an option whether to save the Excel file.

How to write a query exData to populate new Excel file every time the user generates report?

The code is:

    Imports System.Data.OleDb
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Data

    Public Class Form1
        Dim App As New excel.Application ' Data Logging - 19 Nov 15 - Prashant
        Dim WorkBook As excel.Workbook ' Data Logging - 19 Nov 15 - Prashant
        Dim WorkSheet As excel.Worksheet ' Data Logging - 19 Nov 15 - Prashant
        Dim provider As String
        Dim datafile As String
        Dim connString As String
        Dim myConnection As OleDbConnection = New OleDbConnection

    Public Sub ExportToExcelFromAccess()
        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
        datafile = "C:\Database_Tutorial.accdb"
        connString = provider & datafile
        myConnection.ConnectionString = connString
        myConnection.Open()

    '########## Creates new excel file  - 27 Nov 15 - Prashant ##########
    WorkBook = App.Workbooks.Add()
    WorkSheet = WorkBook.Sheets("Sheet1")
    WorkSheet.Range("A1").Value = "DATE"
    WorkSheet.Range("B1").Value = "TIME"
    WorkSheet.Range("C1").Value = "LCK_DIM"
    WorkSheet.Range("D1").Value = "RET_DIM"
    WorkSheet.Range("I1").Value = "PRT_CD"

    WorkSheet.Range("A1:D1").Font.Bold = True
    WorkSheet.Range("A1:D1").Cells.Interior.Color = Color.LightGray
    WorkSheet.Range("A1:D1").Columns.AutoFit()
    WorkSheet.Activate()
    WorkSheet.Application.ActiveWindow.SplitRow = 1
    WorkSheet.Application.ActiveWindow.FreezePanes = True
    App.Visible = True

    '###### Imports data into excel file  - 27 Nov 15 - Prashant ######
    Dim exData As String
    exData = "select * INTO <<  >> from [Tutorial]"
    Dim ExtractData As New System.Data.OleDb.OleDbCommand(exData, myConnection)
    ExtractData.ExecuteNonQuery()
    myConnection.Close()
End Sub
pnuts
  • 58,317
  • 11
  • 87
  • 139
Prashant
  • 93
  • 9

0 Answers0