Solved

EXCEL ACCESS VBA data transfer

Posted on 2013-05-14
3
447 Views
Last Modified: 2013-05-14
I am looking for any code example which will show me how to connect to an Access database from Excel and copy a field from an Access query and paste it into a field in an Excel worksheet
0
Comment
Question by:Idarac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39165472
place this codes in your excel module and replace the variables to suit your requirements


Sub Open_Query()
    Dim db As Database, rs As Recordset, qd As QueryDef
    Dim ws As Object, i As Integer
    Dim dbPath As String
    dbPath = "<path to your access db>"
    Set db = OpenDatabase(dbPath, ReadOnly:=True)
   
    Set qd = db.QueryDefs("QueryName")
    Set rs = qd.OpenRecordset()
    Set ws = Sheets("sheet1")
    ws.Activate
 
    Range("A1").Select
       
        For i = 0 To rs.Fields.Count - 1
            ws.Cells(1, i + 1).Value = rs.Fields(i).Name
        Next

        ws.Range("A2").CopyFromRecordset rs


       qd.Close
       rs.Close
       db.Close
       
End Sub
0
 
LVL 1

Author Comment

by:Idarac
ID: 39165821
Yep works great just pasted in and it worked.
0
 
LVL 1

Author Closing Comment

by:Idarac
ID: 39165824
Thank you for your quick response
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question