Automated Data Extraction to Excel in Visual Basic .NET

• Download source files - 15.6 Kb

Purpose & background

About 2 months prior to writing this article, a need was brought to me to extract data in some way, but without me having to do it every month. Through trial and error, this is the best way that I've found so far. The application allows the user to input a beginning and end date, and then pull data based on that criteria. The data can be placed into either a DataGrid control, or an Excel spreadsheet. The DataGrid population is pretty straight forward, but the Excel extraction required some tinkering.

The code (or at least parts of it)

These are snippets of the ExtractData procedure, full code can be found in the source project/code.

First, you'll want to be sure to add references for both Excel (v. 9 for 2000, v. 10 for XP) and Office (same). Otherwise, this whole thing just won't work, and then I look like a giant tool, and nobody wins like that.

You'll want to make a new instance of the Excel application, then the workbook, then the actual worksheet you'll be working with. I've set the application to invisible for the time being. If someone begins to play around with the spreadsheet while it's being populated, the population of it will end and an error will be returned, and that would suck.

Private Sub ExtractData()
  If (beginDate.Value <= endDate.Value) Then
    Dim excelApp As New Excel.Application
    Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
    Dim excelWorksheet As Excel.Worksheet = _
        CType(excelBook.Worksheets(1), Excel.Worksheet)
    excelApp.Visible = False

We then need to create a disconnected table to populate the spreadsheet from. You know the drill:

With excelWorksheet
   Dim ds As New DataSet("DataSetName")
   Dim dr As DataRow
   Dim myConnection As New OleDb.OleDbConnection _
        ("Provider=Microsoft.Jet.OLEDB.4.0;Data " + _ 
        "Source=//server/folder/file.mdb;Persist Security Info=False")
   Dim myAdapter As New OleDb.OleDbDataAdapter
   Dim myCommand As New OleDb.OleDbCommand _
        (("SELECT LastName, FirstName, Details, " + _ 
        "DateWorkComplete FROM CompletedAll WHERE" + _ 
        " DateWorkComplete Between #" + _
        beginDate.Value.ToShortDateString() + "# And #" + _
        endDate.Value.ToShortDateString() + _ 
        "# ORDER BY Division, DateWorkComplete"), _
        myConnection)

Also, you'll want to format the cells in some way so that most everything's readable. You can adjust the setting, the value/font/width for the column headings with a simple Excel.Worksheet.Range("CellNumber").WhateverYouNeedToFormat.

.Range("A1").Value = "Last Name"
.Range("A1").Font.Bold = True
.Range("A1").ColumnWidth = 15
.Range("B1").Value = "First Name"
.Range("B1").Font.Bold = True
.Range("B1").ColumnWidth = 15

We then want to go through the data in the DataSet and place the values into the spreadsheet. That's done with a simple For Each statement. The name after the value call is simply the name of the field within the table.

For Each dr In ds.Tables(0).Rows
  .Range("A" & i.ToString).Value = dr("LastName")
  .Range("B" & i.ToString).Value = dr("FirstName")
  .Range("C" & i.ToString).Value = dr("DateWorkComplete")
  .Range("D" & i.ToString).Value = dr("DetailsOfProblem")
  i += 1
Next

We then finally need to make Excel visible.

excelApp.Visible = True

Again, the full code is in the source project/code above. If there any questions, contact me.