?
Solved

EXCEL ACCESS VBA data transfer

Posted on 2013-05-14
3
Medium Priority
?
464 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
  • 2
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 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