Click here to Skip to main content
Click here to Skip to main content

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

License

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

Share

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 child014@mc.duke.edu or IM him on AIM as KenAtDUMC or KenAtNCWC

Comments and Discussions

 
QuestionHi Pinmembersrid816-Oct-11 1:17 
GeneralAlternative PinmemberCikaPero19-Apr-11 23:42 
Generalerrors Pinmemberlandra18-Jan-08 0:24 
GeneralHELP Pinmemberjarell3120-Jun-07 12:18 
Generalchange font color Pinmembervivek_pon28-Mar-06 19:16 
GeneralRe: change font color Pinmemberurssamba23-May-06 22:39 
Generalnot openning the Excel window Pinmembertinybunny_830-Jan-06 5:11 
GeneralADD REFRECE OF EXCEL Pinmembervivek_pon16-Jan-06 0:09 
Generalwhere is file.mdb PinmemberCihangir200215-Jan-05 8:13 
GeneralPrevent improper conversion PinmemberChuck7775-Oct-04 12:24 
GeneralDynamic Excel Creation Based on Results from SQL Pinmemberanthonylalba2-Jun-04 6:46 
GeneralSome options PinmemberMoustafa Ali1-Jun-04 2:43 
GeneralRe: Some options Pinmemberanthonylalba2-Jun-04 6:55 
GeneralRe: Some options PinmemberAlesKlemenc23-Mar-05 20:21 
GeneralLink to a different tutorial Pinmemberscorpion5306127-May-04 4:16 
Questionhow to add and retreive data from excel by c#.Net 2002 PinsussGeorgeWagdy19-Apr-04 11:10 
Generalerror runing example Pinmemberscumpa14-Apr-04 1:50 
GeneralRe: error runing example Pinmemberscumpa16-Apr-04 2:07 
GeneralRe: error runing example Pinmembertuyennv11-Oct-04 22:40 
GeneralStopping the Excel process PinsussCindie30-Mar-04 5:20 
GeneralRe: Stopping the Excel process PinmemberMort Barsky27-Apr-04 12:19 
GeneralRe: Stopping the Excel process Pinmemberocbka2118-May-04 19:38 
GeneralONLINE DOCUMENTATION PinmemberKenneth Childs29-Oct-03 3:28 
Generalcannot launch source code Pinmemberrapace18-Sep-03 8:59 
Generalprob viewing the source code Pinmemberbiott31-Jul-03 21:40 

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

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

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