How to export data from SQL server (using query) to Excel file

[Julz80]

Hi,

I am new to programming on VB.net and start using Visual Studio Community 2015.

What I need to do is on single button click (I have a form) make a selection in SQL (I have written query) to be pasted on Excel spreadsheet. I have searched for examples on web, found the below, but it does not properly work for me, eror occurs on: Microsoft.Office.Interop.Excel.Worksheet/Application

I have added Microsoft Excel 16.0 Object Library to my project

Can someone please kindly advice/comment on that.

Public Class frmMain
Inherits System.Windows.Forms.Form
Dim My_Connection As New System.Data.OleDb.OleDbConnection
Dim sql As String
Dim cmd As System.Data.OleDb.OleDbCommand
Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Defining the connection string (you can use your own database provider and data source type)
My_Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\Test.mdb"
Try
My_Connection.Open()
Catch ex As Exception
MsgBox("Failed to connect to database file: " & ex.ToString(), MsgBoxStyle.Critical, "Error")
End
End Try
End Sub
Private Sub frmMain_Closed(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Closed

If My_Connection.State <> ConnectionState.Closed Then My_Connection.Close()
End Sub
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
On Error GoTo ErrorHandler
Dim ExcelFile_Path As String
Dim MySaveFileDialog As New SaveFileDialog
Setting the attributes for "MySaveFileDialog" object
MySaveFileDialog.Filter = "Microsoft Office Excel Files (*.xls)|*.xls"
MySaveFileDialog.FilterIndex = 1
MySaveFileDialog.CheckFileExists = False
MySaveFileDialog.CheckPathExists = True
MySaveFileDialog.CreatePrompt = False
MySaveFileDialog.DefaultExt = "xls"
MySaveFileDialog.DereferenceLinks = True
MySaveFileDialog.InitialDirectory = "C:\Users\jivanovica\Desktop\ME"
MySaveFileDialog.OverwritePrompt = True
MySaveFileDialog.RestoreDirectory = True
MySaveFileDialog.ValidateNames = True
If MySaveFileDialog.ShowDialog() <> DialogResult.OK Then Exit Sub
ExcelFile_Path = MySaveFileDialog.FileName
If File.Exists("C:\Users\jivanovica\Desktop\ME\EV161111.csv") = True Then File.Delete(ExcelFile_Path)
Me.Cursor = Cursors.WaitCursor
Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
Create an empty Excel file in the path specified by the "ExcelFile_Path" string which obtained from the "MySaveFileDialog" object
Usually the new Excel file will contain three empty sheets (sheet1, sheet2, and sheet3)
xlApp = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Microsoft.Office.Interop.Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
xlSheet.SaveAs(ExcelFile_Path)
xlBook.Close()
Exporting the data into Excel file using SQL SELECT command
In this example the data will be exported in a new sheet named "EXPORT"
sql = "SELECT * INTO [Excel 8.0;Database=" & ExcelFile_Path & "].[EXPORT] FROM TEST_TABLE ORDER BY FIELD1,FIELD2"
cmd = New System.Data.OleDb.OleDbCommand(sql, My_Connection)
cmd.ExecuteNonQuery()
Reopen the Excel file
xlApp = New Microsoft.Office.Interop.Excel.Application
xlApp = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
xlBook = xlApp.Workbooks.Open(ExcelFile_Path)
Delete all sheets in the Excel file except the "EXPORT" sheet
For Each xlSheet In xlBook.Worksheets
If xlSheet.Name <> "EXPORT" Then xlSheet.Delete()
Next
xlSheet = New Microsoft.Office.Interop.Excel.Worksheet
xlSheet = CType(xlBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim xlRange As Microsoft.Office.Interop.Excel.Range
Make the first row in the "EXPORT" sheet as bold (this is recommended if the first row is a header row)
xlRange = DirectCast(xlSheet.Rows(1), Microsoft.Office.Interop.Excel.Range)
xlRange.Font.Bold = True
Auto fit the whole columns in the "EXPORT" sheet
xlRange = DirectCast(xlSheet.Columns, Microsoft.Office.Interop.Excel.Range)
xlRange.AutoFit()
Save and close the Excel file
xlBook.Save()
xlBook.Close()
Me.Cursor = Cursors.Default
Exit Sub
ErrorHandler:
Me.Cursor = Cursors.Default
xlBook.Close(SaveChanges:=False)
MsgBox("Operation failed", MsgBoxStyle.Critical, "Error")
End Sub

Friend WithEvents Button1 As Button

Private Sub InitializeComponent()
Me.Button1 = New System.Windows.Forms.Button()
Me.SuspendLayout()

Button1

Me.Button1.Location = New System.Drawing.Point(63, 78)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(167, 75)
Me.Button1.TabIndex = 0
Me.Button1.Text = "Button1"
Me.Button1.UseVisualStyleBackColor = True

frmMain

Me.ClientSize = New System.Drawing.Size(282, 255)
Me.Controls.Add(Me.Button1)
Me.Name = "frmMain"
Me.ResumeLayout(False)

End Sub
End Class

Continue reading...