Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Question Unanswered: Creating and Updating an Excel file from Access

    I have an Access MDB with multiple tables to track the distribution of daily meals. I would like to dynamically create a monthly Excel spreadsheet with daily columns (updated daily as delivery route documents are created) so there is a record of who did and did not receive meals each day. The spreadsheet rows will be meal recipients, and the columns the days of the month. Before the delivery sheets are created all recipient records are updated with Y or N for each person for each day of a week (the default is Y so only those not receiving a meal are changed to N). I would like to dynamically create a new spreadsheet on the first of each month and then add a column of data dynamically each day as the reports are created. Is this possible? Thanks for your thoughts and especially solutions.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Yes, this is fairly straight-forward. Look in the Help file for Automation - it will tell you how to control Excel from Access.

    Is the spreadsheet to be used for anything else? If not, consider creating an Access report instead.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    My question is why put it into a excel file ?

    Why not just create a report or do the user up date the excel file
    Last edited by myle; 11-10-11 at 05:24.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Nov 2011
    Posts
    2
    Thanks to you both for the quick replies. I did not find any items under 'automation' that fit what I would like to do. I do't think I explained the need clearly and I will continue to look for examples as well as brainstorm on other methods to support my users. Cheers, Ned

    "Perception is reality to the perceiver".

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If Automation can't transfer data from Access to Excel in the way that you need, then I don't know what you're trying to do. I have a number of databases that control Excel and either add and manipulate data in them, or manipulate and retrieve data from them, and they all work without problems.

    From how you've described the situation, you have a cross-tab query for the month that has a new column each day, and you want to add each day's new column to a spreadsheet. The DoCmd method TrasnsferSpreadsheet will take a query and output the results to a named .xls (or probably .xlsx if you're using Office post 2003), so you can either delete the file before each day's output, or just have an ever-increasing number number of sheets in the workbook.

    If you want to exert more control over the transfer of data, you will need to instantiate an Excel application object within Access, and the help file's entries on Automation detail how to achieve this.

    Perhaps some illustrated examples of what you have and what you want to achieve would be useful?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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