[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

EXCEL ACCESS VBA data transfer

Posted on 2013-05-14
3
Medium Priority
?
459 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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

650 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