Member Avatar for poonams

hi all,

I need a code which export access table data into excel sheet.I am working in vb.net2003

  • 3 Contributors
  • 2 Replies
  • 112 Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by Pgmer

Dani AI

Generated

For a quick, robust solution in VB.NET 2003 (), there are three practical approaches. and pointed to full tools/tutorials; those are useful for a GUI export wizard, but for simple programmatic export the options below are easier to apply and troubleshoot.

Option 1 — CSV (recommended): simplest, no Excel install required, works in .NET 1.1, and opens in Excel. Good for raw data export and servers.
Option 2 — OLE DB to Excel (creates an .xls): use Jet.OLEDB.4.0 (for .xls) or ACE.OLEDB.12.0 (for .xlsx) and run INSERT INTO [Sheet1$]...; requires correct provider and can be picky about column types and HDR settings. Example connection string for .xls:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\out.xls;Extended Properties="Excel 8.0;HDR=Yes;"
Option 3 — Excel automation (Interop): full formatting/control but requires Excel installed and is not recommended on servers.

A minimal, compatible CSV exporter you can call after filling a DataTable from Access:

Function ExportDataTableToCsv(ByVal dt As DataTable, ByVal filePath As String) As Boolean
    Try
        Dim sw As New System.IO.StreamWriter(filePath, False, System.Text.Encoding.UTF8)
        Dim i As Integer
        ' header
        For i = 0 To dt.Columns.Count - 1
            If i > 0 Then sw.Write(",")
            sw.Write("""" & dt.Columns(i).ColumnName.Replace("""", """""") & """")
        Next
        sw.WriteLine()
        ' rows
        Dim r As DataRow
        For Each r In dt.Rows
            For i = 0 To dt.Columns.Count - 1
                If i > 0 Then sw.Write(",")
                Dim field As String
                If r.IsNull(i) Then
                    field = ""
                Else
                    field = r(i).ToString()
                End If
                field = field.Replace("""", """""")
                sw.Write("""" & field & """")
            Next
            sw.WriteLine()
        Next
        sw.Close()
        Return True
    Catch ex As Exception
        ' log or handle ex
        Return False
    End Try
End Function

Troubleshooting notes: ensure write permissions on the target path; when using OLE DB confirm the right provider is installed (Jet vs ACE) and match 32/64-bit; Excel may coerce types (leading zeros, dates) — export as quoted text or use CSV to avoid type guessing; do not use Excel Interop on a server.

Recommended Answers

All 2 Replies

Member Avatar for ivy3

I suggest you one article which introduces how to use a small tool to export data from database. And it shows the source code on designing the tool. You can view it on

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.