1 Replies - 8144 Views - Last Post: 12 May 2008 - 10:33 PM Rate Topic: -----

#1 mark25  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 15-April 08

Q:how to command ms access to export tables using VB.

Posted 11 May 2008 - 10:08 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Q:how to command ms access to export tables using VB.

#2 anand_the_great  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 47
  • Joined: 15-April 08

Re: Q:how to command ms access to export tables using VB.

Posted 12 May 2008 - 10:33 PM

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.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1