Question writing Database to Excel file.

[pekt2s]

Hi. Im trying to write the data from database to excel file. can you pls help me on this. Im having a difficulties on writing to excel.

hoping for you immediate reply.

Thanks and God Bless.

 

[martins.anietie]

you have to MS JET to link to Ms Excel, take the following step:
On MS ACCESS, from FILE MENU, click on Get data, choose IMPORT TABLE.....follow the directory to your MS EXCEL file and choose the WORKSHEET you wish to IMPORT, once imported it will automatically become a table in MS Access.

 

[martins.anietie]

You have to use MS JET to link to Ms Excel,
Take the following step:
On MS ACCESS, from FILE MENU, click on Get data, choose IMPORT TABLE.....follow the directory to your MS EXCEL file and choose the WORKSHEET you wish to IMPORT, once imported it will automatically become a table in MS Access.
Edit/Delete Message

 

[MattP]

Here's an example I wrote for a recent audit where an Excel file for training attendance was required. (Auditors rock! /sigh) Some obvious issues (checking EndsWith when I already have a FileInfo) that could be changed but it got the job done.

Only gotcha is that when you're filling your DataTable you need to set your DataAdapter's AcceptChangesDuringFill to False so all of your rows are passed in Update.

	Private Sub btnSaveFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles btnSaveFile.Click

		With Me.SaveFileDialog1
			.CreatePrompt = True
			.FileName = String.Empty
			.InitialDirectory = "C:\Temp\"
			.Filter = "Excel 2003 (*.xls)|.xls|Excel 2007 (*.xlsx)|.xlsx"
		End With

		If Me.SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
			Dim doc As New FileInfo(Me.SaveFileDialog1.FileName)
			If Me.SaveFileDialog1.FileName.EndsWith("xlsx") Then
				'Excel 2007 Files
				cn = New OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties={1}Excel 12.0 Xml;HDR=YES{1}", doc.FullName, ControlChars.Quote))
			ElseIf Me.SaveFileDialog1.FileName.EndsWith("xls") Then
				'Excel 2003 & earlier files
				cn = New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties={1}Excel 8.0;HDR=YES{1}", doc.FullName, ControlChars.Quote))
			End If

			TransferDataToExcel(ds.Tables("File"), doc.FullName, cn)
		End If

	End Sub

	Public Sub TransferDataToExcel(ByVal dt As DataTable, ByVal FileName As String, ByVal Connection As OleDbConnection)

		Dim cmd As New OleDbCommand(String.Empty, Connection)

		Try
			'Create workbook and worksheet
			If File.Exists(FileName) Then File.Delete(FileName)
			cn.Open()
			cmd.CommandText = "CREATE TABLE [MyTable] ([Last Name] VARCHAR(30), [First Name] Varchar(30), [Attended?] Varchar(15), [Job Title] Varchar(50))"
			cmd.ExecuteNonQuery()

			'Transfer dataset to worksheet
			cmd.CommandText = "INSERT INTO [MyTable] ([Last Name], [First Name], [Attended?], [Job Title]) VALUES (?, ?, ?, ?)"
			With cmd.Parameters
				.Add("@LastName", OleDbType.VarChar, 0, "Last Name")
				.Add("@FirstName", OleDbType.VarChar, 0, "First Name")
				.Add("@Attended", OleDbType.VarChar, 0, "Attended?")
				.Add("@JobTitle", OleDbType.VarChar, 0, "Job Title")
			End With

			Dim da As New OleDbDataAdapter
			da.InsertCommand = cmd
			da.Update(dt)
		Catch ex As Exception
			MessageBox.Show(ex.ToString(), "Error with file creation", MessageBoxButtons.OK, MessageBoxIcon.Error)
		Finally
			cn.Close()
			cmd.Dispose()
		End Try

	End Sub

 

[cjard]

If you search the forums for any threads where:

I have posted
The thread has an attachment
The thread contains the search term ExcelableData

You'll find a way of exporting a dataset to excelwithout using a database driver, or needing excel installed