Welcome to Dream.In.Code
Getting VB Help is Easy!

Join 136,565 VB Programmers for FREE! Get instant access to thousands of VB experts, tutorials, code snippets, and more! There are 1,900 people online right now. Registration is fast and FREE... Join Now!




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

 
Reply to this topicStart new topic

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

mark25
11 May, 2008 - 09:08 PM
Post #1

New D.I.C Head
*

Joined: 15 Apr, 2008
Posts: 15

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. smile.gif

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 - 09:13 PM
User is offlineProfile CardPM
+Quote Post

anand_the_great
RE: Q:how To Command Ms Access To Export Tables Using VB.
12 May, 2008 - 09:33 PM
Post #2

New D.I.C Head
*

Joined: 15 Apr, 2008
Posts: 34

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.
CODE

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.
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic
Time is now: 12/2/08 11:51PM

Live VB Help!

VB Tutorials

Reference Sheets

VB Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month