by Gehan Fernando (47 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB.NET
Difficulty: Unknown Difficulty
Originally Published: Tue 28th November 2006
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Data Transfer from SQL Server / MS Access to Excel
VBcoders
Private Tbl As DataTable
Private Col As DataColumn
Private Row As DataRow
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ExcelApp = New Excel.Application()
ExcelApp.Application.Workbooks.Add(True)
End Sub
Private Function GetData() As System.Data.DataTable
Dim OleCon As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB")
Dim OleAdapter As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Customers ORDER BY CustomerID", OleCon)
Dim OleDataSet As New System.Data.DataSet()
OleAdapter.Fill(OleDataSet, "Customers")
Return OleDataSet.Tables("Customers")
End Function
Private Sub CmdTransfer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdTransfer.Click
Try
Me.Cursor = Cursors.WaitCursor
Dim ColIndex As Integer = 0
Dim RowIndex As Integer = 1
Tbl = New DataTable("Customers")
Tbl = GetData()
Col = New DataColumn()
For Each Col In Tbl.Columns()
ColIndex += 1
ExcelApp.Cells(1, ColIndex) = Col.ColumnName.ToString()
Next
Prg1.Value = 0
Prg1.Maximum = Tbl.Rows.Count
For Each Row In Tbl.Rows
RowIndex += 1
ColIndex = 0
For Each Col In Tbl.Columns()
ColIndex += 1
ExcelApp.Cells(RowIndex, ColIndex) = Row(Col.ColumnName).ToString()
ExcelApp.Columns.AutoFit()
Next Col
Prg1.Value += 1
Application.DoEvents()
Next Row
ExcelApp.Visible = True
Me.Cursor = Cursors.Default
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Me.Cursor = Cursors.Default
End Try
End Sub
