[mark25]

Q: Can you please help me on how to tell ms access, my database, using visual basic's command button to produce or export a table and in an excel format.

Manual: I can do that in ms access manually just by highlighting the table i've made. Then right click it and choose Export and after that, choose a destination on where to put that file. (Let's say to the desktop, "Save in Desktop"). And then in the "Save as Type: field choose microsoft excel 3 and then click export. That i can do...Manually.

Now i want to convert that steps using vb so that when i click the command button, it will do that task i did above automatically and save it to desktop.

Private Sub Command1_Click()
<-------------Please show what to put here so that it will do the task above.
End Sub

Thanks and regards,

This post has been edited by mark25: 11 May 2008 - 10:13 PM

[anand_the_great]

Hello.
This is the of code to import from Access to system and indirectly to Excel as in it all the field and all the row.
Make sure the name of database and excel is correct.
Try it out.

Private Sub Command1_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim excel_app As Object
Dim excel_sheet As Object
	Screen.MousePointer = vbHourglass
	DoEvents

	Set conn = New ADODB.Connection
	conn.ConnectionString = _
		"Provider=Microsoft.Jet.OLEDB.4.0;" & _
		"Data Source=" & App.Path & "\wage_des.mdb"
	conn.Open
	Set rs = conn.Execute("SELECT * FROM Record ORDER BY NumberOfRecordForAll ASC")
	rs.MoveFirst
	' Create the Excel application.
	Set excel_app = CreateObject("Excel.Application")
	' Open the Excel workbook.
	excel_app.Workbooks.Open (App.Path & "\Report1.xls")
	Set excel_sheet = excel_app.WorkSheets("Sheet1")
	' Use the Recordset to fill the table.
	excel_sheet.Cells.CopyFromRecordset rs
	excel_sheet.Cells.Columns.AutoFit

	' Open the of the Ecxel_File
	excel_app.Visible = True

	' Save the workbook.
	excel_app.ActiveWorkBook.Save

	Screen.MousePointer = vbDefault
	MsgBox "Ok"
End Sub

Thanks.
Anand.