access report in excel

Posted on 2011-10-12
Last Modified: 2012-05-12
I import data from odbc into excel. I want to start the access report from a macro button in excel. Is this possible? Without access itself being started..

Im using microsoft query to always keep the data in my excel sheet updated. But I need to show this data like a concatenated list, like you can do with reports in access.

Or, if theres a way of grouping and showing the data like a report in excel I guess that would be the best.?
Question by:bergsprekken
    LVL 19

    Expert Comment

    Yes, this is certainly possible.

    Easiest way to accomplish this is to go to the developer tab and start recording a macro. Then manually perform an update query and stop recording. You can then find the recorded macro code by opening the VBA code editor and looking in the modules section of the spreadsheet.

    In the spreadsheet itself you can add an Active-X button (click on developers tab - insert - activex - button, draw the button on the worksheet). Double-click on the button to generate the appropriate event handler and fill in the name of the recorded macro.
    LVL 8

    Expert Comment


    Author Comment

    What I meant to ask was how to start a access report from excel and use the data in excel.
    LVL 19

    Accepted Solution

    in general that would be something like

    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase "c:\path_to\database.mdb"
    appAccess.DoCmd.OpenQuery "Query Name"

    Open in new window


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now