Click here to Skip to main content
13,201,940 members (71,939 online)
Click here to Skip to main content
Add your own
alternative version


61 bookmarked
Posted 23 Jul 2003

Automated Data Extraction to Excel in Visual Basic .NET

, 23 Jul 2003
Rate this:
Please Sign up or sign in to vote.
Based on user input, program pulls data from a database and extracts it to Microsoft Excel.

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"), _

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

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.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Kenneth Childs
Web Developer
United States United States
Ken is a 28 year old web/database/application design geek at Duke University Medical Center's Department of Anesthesiology. He graduated from the prestegious North Carolina Wesleyan College in 2001 with a double degree in Computer Information Systems and Business Administration.

In his free time, he enjoys hiking, lacrosse, baseball, bowling, pool, candlelight dinners, long walks along a moonlit beach, and talking in the third person. You can e-mail him at or IM him on AIM as KenAtDUMC or KenAtNCWC

You may also be interested in...


Comments and Discussions

QuestionHi Pin
srid816-Oct-11 0:17
membersrid816-Oct-11 0:17 
GeneralAlternative Pin
CikaPero19-Apr-11 22:42
memberCikaPero19-Apr-11 22:42 
Generalerrors Pin
landra17-Jan-08 23:24
memberlandra17-Jan-08 23:24 
GeneralHELP Pin
jarell3120-Jun-07 11:18
memberjarell3120-Jun-07 11:18 
Generalchange font color Pin
vivek_pon28-Mar-06 18:16
membervivek_pon28-Mar-06 18:16 
GeneralRe: change font color Pin
urssamba23-May-06 21:39
memberurssamba23-May-06 21:39 
Generalnot openning the Excel window Pin
tinybunny_830-Jan-06 4:11
membertinybunny_830-Jan-06 4:11 
vivek_pon15-Jan-06 23:09
membervivek_pon15-Jan-06 23:09 
Generalwhere is file.mdb Pin
Cihangir200215-Jan-05 7:13
memberCihangir200215-Jan-05 7:13 
GeneralPrevent improper conversion Pin
Chuck7775-Oct-04 11:24
memberChuck7775-Oct-04 11:24 
GeneralDynamic Excel Creation Based on Results from SQL Pin
anthonylalba2-Jun-04 5:46
memberanthonylalba2-Jun-04 5:46 
GeneralSome options Pin
Moustafa Ali1-Jun-04 1:43
memberMoustafa Ali1-Jun-04 1:43 
GeneralRe: Some options Pin
anthonylalba2-Jun-04 5:55
memberanthonylalba2-Jun-04 5:55 
GeneralRe: Some options Pin
AlesKlemenc23-Mar-05 19:21
memberAlesKlemenc23-Mar-05 19:21 
GeneralLink to a different tutorial Pin
scorpion5306127-May-04 3:16
memberscorpion5306127-May-04 3:16 
Questionhow to add and retreive data from excel by c#.Net 2002 Pin
GeorgeWagdy19-Apr-04 10:10
sussGeorgeWagdy19-Apr-04 10:10 
Generalerror runing example Pin
scumpa14-Apr-04 0:50
memberscumpa14-Apr-04 0:50 
GeneralRe: error runing example Pin
scumpa16-Apr-04 1:07
memberscumpa16-Apr-04 1:07 
GeneralRe: error runing example Pin
tuyennv11-Oct-04 21:40
membertuyennv11-Oct-04 21:40 
GeneralStopping the Excel process Pin
Cindie30-Mar-04 4:20
sussCindie30-Mar-04 4:20 
GeneralRe: Stopping the Excel process Pin
Mort Barsky27-Apr-04 11:19
memberMort Barsky27-Apr-04 11:19 
GeneralRe: Stopping the Excel process Pin
ocbka2118-May-04 18:38
memberocbka2118-May-04 18:38 
Kenneth Childs29-Oct-03 2:28
memberKenneth Childs29-Oct-03 2:28 
Generalcannot launch source code Pin
rapace18-Sep-03 7:59
memberrapace18-Sep-03 7:59 
Generalprob viewing the source code Pin
biott31-Jul-03 20:40
memberbiott31-Jul-03 20:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171020.1 | Last Updated 24 Jul 2003
Article Copyright 2003 by Kenneth Childs
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid