1 Replies - 10595 Views - Last Post: 25 December 2008 - 07:39 AM Rate Topic: -----

#1 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

export data to excel from Access with the help of vb.net

Post icon  Posted 25 December 2008 - 05:19 AM

Here first i filled Datset & then tried to send data in excel.

here my sql query is fine as i also tried it to filled datagridview & it worked .

But i am not able to export data to excel.

Imports System.Data.OleDb
Public Class frmReport
	Dim cn As OleDbConnection
	Dim ds As DataSet
	Dim ad As OleDbDataAdapter
	Dim mdbpath, q, q1, mdbconn As String

	Private Sub btnReport1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReport1.Click
		Try
			Data1()
			conn()
			exportdata()
		Catch ex As Exception
			MsgBox(ex.Message)
		End Try
	End Sub
	Public Sub Data1()

		' Radio Buttons Selections

		If RbJal.Checked Then
			'Report 1 query
			q = "select f.[Packet No],f.[Mobile No],mi.Distr_Name,f.Dod,F.Doh from fresh f inner join mef_info mi on f.[packet no]=mi.pack_no where f.cluster='JAL' and f.[mobile no]<> all(select f.[mobile no] from fresh f inner join ussd_master um on f.[mobile no]=um.mob_no where um.act_date>dateadd('m',-2,date()))"

		End If
		If RbAsr.Checked Then
			'Report 1 query
			q = "select f.[Packet No],f.[Mobile No],mi.Distr_Name,f.Dod,F.Doh from fresh f inner join mef_info mi on f.[packet no]=mi.pack_no where f.cluster='ASR' and f.[mobile no]<> all(select f.[mobile no] from fresh f inner join ussd_master um on f.[mobile no]=um.mob_no where um.act_date>dateadd('m',-2,date()))"
		End If
		If RbChd.Checked Then
			'Report 1 query
			q = "select f.[Packet No],f.[Mobile No],mi.Distr_Name,f.Dod,F.Doh from fresh f inner join mef_info mi on f.[packet no]=mi.pack_no where f.cluster='CHD' and f.[mobile no]<> all(select f.[mobile no] from fresh f inner join ussd_master um on f.[mobile no]=um.mob_no where um.act_date>dateadd('m',-2,date()))"
		End If
	End Sub

Public Sub conn()
		mdbpath = "D:\Database\chesnut_master.mdb"
		mdbconn = "provider=microsoft.jet.oledb.4.0;data source={0};jet oledb:database password=Godfather"
		mdbconn = String.Format(mdbconn, mdbpath)
		cn = New OleDbConnection(mdbconn)
		cn.Open()
		ds = New DataSet
		ad = New OleDbDataAdapter(q, cn)
		ad.Fill(ds, "fresh")
		' DataGridView1.DataSource = ds.Tables(0)

	End Sub

Public Sub exportdata()
		Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
		Dim wbook As New Microsoft.Office.Interop.Excel.Workbook
		Dim wsheet As New Microsoft.Office.Interop.Excel.Worksheet
		wbook = excel.Workbooks.Add()
		wsheet = wbook.ActiveSheet()

		Dim dt As System.Data.DataTable = ds.Tables(0)
		Dim dc As System.Data.DataColumn
		Dim dr As System.Data.DataRow
		Dim colindex As Integer = 0
		Dim rowindex As Integer = 0
		For Each dc In dt.Columns
			colindex = colindex + 1
			excel.Cells(1, colindex) = dc.ColumnName
		Next

		For Each dr In dt.Rows
			rowindex = rowindex + 1
			colindex = 0
			For Each dc In dt.Columns
				colindex = colindex + 1
				excel.Cells(rowindex + 1, colindex) = dr(dc.ColumnName)

			Next
		Next

		wsheet.Columns.AutoFit()
		'Dialouge box to make a file
		SaveFileDialog1.ShowDialog()
		Dim strfilename As String = SaveFileDialog1.FileName
		Dim blnfileopen As Boolean = False
		Try
			Dim filetemp As System.IO.FileStream = System.IO.File.OpenWrite(strfilename)
			filetemp.Close()
		Catch ex As Exception
			blnfileopen = False
		End Try
		If System.IO.File.Exists(strfilename) Then
			System.IO.File.Delete(strfilename)
		End If
		wbook.SaveAs(strfilename)
		excel.Workbooks.Open(strfilename)
		excel.Visible = True

	End Sub
End Class





But im getting error -- retrieving the com class factory for component with clsid {00020819-0000-0000-C000-0000000046}-failed due to following error-80040154

Is This A Good Question/Topic? 0
  • +

Replies To: export data to excel from Access with the help of vb.net

#2 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: export data to excel from Access with the help of vb.net

Posted 25 December 2008 - 07:39 AM

I write now same application with same code. But that is working. I think there is no need to answer this topic. I have not find any diffrence between both the codes
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1