Results 1 to 4 of 4
  1. #1
    exceljockey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    8

    Import access query data into excel file, using excel vba

    Hi All



    This has probably been asked a hundred times before, but I have had no luck with searches.

    I am currently trying to make a button in excel where once pressing, my acces database opens, the desired query opens, then this data is copied over to my excel sheet (from where I pressed the button).

    I am new to VBA in general, here is the code I have written so far which opens the database. I am lost past this point:

    Sub OpenAccess2()
    On Error Resume Next
    Dim wrkbook_dest As Workbook
    Dim DATABASE As String
    Dim oApp As Access.Application


    DATABASE = "file path blah blah blah"

    Set wrkbook_dest = ActiveWorkbook
    Set oApp = CreateObject("Access.Application")

    oApp.Visible = True
    oApp.OpenCurrentDatabase DATABASE



    oApp.DoCmd.OpenQuery "query name"

    End Sub

    Everything works up to this point, the DB and Query open, but after this point i am lost.


    FYI I am using excel 2010, and access 2003. Also I am connected to a workgroup, so if you know a way I can bypass the password entry (by having my password somewhere in the vba code), I would appreciate it, but it is not essential since I can just type it in and the code keeps running after it has been entered.

    Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,422
    Can you not just have Excel link to query object in Access?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    exceljockey is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Nov 2013
    Posts
    8
    June7: I could do, but I am making this for certain people who are not access savvy but require certain querys.
    How would I have an excel link to query objects in access any how?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,422
    They may not be Access savvy but they surely know how to push buttons. You could build a form in Access with buttons that will open a query object.

    But really, users should not work directly with tables and queries.

    Why is there a need to have the data presented in Excel? Do users want to do some custom manipulation of the data?

    I don't remember much about 2003 (didn't use it very long). Review http://www.meadinkent.co.uk/excel-import-access.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query used to import data from access > excel
    By Perceptus in forum Queries
    Replies: 1
    Last Post: 07-25-2013, 10:45 AM
  2. Replies: 3
    Last Post: 12-06-2012, 03:47 PM
  3. Import Data from Excel File
    By zwieback89 in forum Import/Export Data
    Replies: 1
    Last Post: 06-27-2012, 08:44 AM
  4. Replies: 1
    Last Post: 04-20-2012, 07:20 AM
  5. Import Excel file to Access
    By emmett in forum Import/Export Data
    Replies: 3
    Last Post: 04-06-2012, 05:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums