Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export Data From Access To Excel

    I am working in Access 2010.

    I have a query that returns a date, a username and a count (activities for the day).

    The project I'm is a yearly project that begins in Jan and ends May.

    I want to capture the activity count of the user for each day. Normally a crosstab query would suffice but considering the length of time involved I thought it best to export the data out daily and send to and update a spreadsheet.

    In the spreadsheet I have date, username and count for user.

    In Excel the process would be similar to the VLookup or HLookup but I need to import from Access into Excel.

    Does anyone have any idea how I might begin this endevor or if it's even possible?

    Thank you in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,513
    Thanks
    3
    Thanked 46 Times in 46 Posts
    It is possible to put data into an Excel workbook from Access - there are export methods, linking methods, and automation of Excel. But it's not clear to me what the advantage would be of putting the data out to Excel each day. Are you anticipating a grid "report" where the users would be row entries and the columns would be dates, with the intersection cell containing the activity count? (It could also be reversed, but the width of user names makes it more visually pleasing to put them in a column of cells, rather than using vertical text.)

    I would be inclined to store a simple record with a user ID, a date and an activity count in an Access table. Then you can take that simple data into Excel whenever you want to display some results and create a pivot table - their version of a crosstab. But the crosstab query should work as well. It sounds like an interesting project - the capture of the activity count is always a challenge in such endeavors.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This report is required by the state and must include daily activities and must be in Excel or cvs format. For the most part, I would prefer a crosstab query w/in Access but over a 5 month period and capturing daily data, I think the best approach is export to Excel (the entire Project is in Access).

    The spreadsheet would have the following:
    Row Heading=User name (i.e. Row 1)
    Column Heading = Date (i.e. Column A)
    Cells w/in Column will be the counts (i.e. Row2 columnA)

    I'm not sure how to get the data out of Access and automatically update the fields at correct intersection with the correct username and correct.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,513
    Thanks
    3
    Thanked 46 Times in 46 Posts
    So how is the activity count captured for a given user and day? If that all happens internally in the database, and you aren't dependent on the user entering something, you could create a task that runs every night (or the next morning) and updates such a worksheet using Automation. However that requires that you understand VBA and both the Access and Excel object models. We do have a project where we have done that, but it ended up being several hundred lines of VBA code.

    Another question - how often do you have to produce the Excel/CSV report for the state. If it ends up being once a month, or once a week then the approach of creating a cross-tab query and exporting it to Excel would definitely be my choice. On the other hand, if you have to do it every day, it probably becomes a toss-up. But I think the real issue is whether you want to take the time to design/test/debug the VBA code (after you've figured out Automation), when the crosstab approach will give you the entire grid each time you want it, and a right-click Export will take it directly to Excel. We do a number of statistical reports that way for our clients.

    If you want to learn more about Automation, our Automation 101 Tutorial is a place to start. To focus specifically on automating Excel, take a look at ACC2000: Using Automation to Transfer Data to Microsoft Excel which is shown as a link in our tutorial.

    Let us know how you decide to go, and if you run into problems, we will do our best to assist you.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •