Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    21

    Unanswered: data manipulation in excel from access

    Hello folks,

    I currently have a backend db table with data that I need to manipulate in excel because of formulae and multiple pivot table looks. Many people are entering data during the day in the db. Is there a good/easy way to either:

    1. Keep the data updated in excel in real time (so I don't have to manually update)?

    2. Export data to excel daily without having to manually do this?

    Also, I'm a newbie to this, but have the developer edition (because I created runtimes for the other computers)...in this edition it talks about being able to have different db's for the same table. I guess what I'd like to do is work out of a "copy" of my backend DB so I'm not screwing something up in the backend DB. Any good way to do this? Thanks so much,
    Aaron

  2. #2
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Re: data manipulation in excel from access

    So in a nutshell, you want to export data from the table into the excel spreadsheet, and do this periodically at a certain time, like midnight?

    1 question; if you want it done like a scheduled task, do you have your computer on all the time?

  3. #3
    Join Date
    Nov 2003
    Posts
    21

    Re: data manipulation in excel from access

    Originally posted by Jerrie
    So in a nutshell, you want to export data from the table into the excel spreadsheet, and do this periodically at a certain time, like midnight?

    1 question; if you want it done like a scheduled task, do you have your computer on all the time?
    .

    Hello,

    Yes, I can have my computer on all the time, but it won't necessarily be logged on for security reasons.

    thanks,
    Aaron

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: data manipulation in excel from access

    Originally posted by jovantovich
    Hello folks,

    I currently have a backend db table with data that I need to manipulate in excel because of formulae and multiple pivot table looks. Many people are entering data during the day in the db. Is there a good/easy way to either:

    1. Keep the data updated in excel in real time (so I don't have to manually update)?

    2. Export data to excel daily without having to manually do this?

    Also, I'm a newbie to this, but have the developer edition (because I created runtimes for the other computers)...in this edition it talks about being able to have different db's for the same table. I guess what I'd like to do is work out of a "copy" of my backend DB so I'm not screwing something up in the backend DB. Any good way to do this? Thanks so much,
    Aaron
    Hi Aaron...

    What version of Access and/or Excel you are using?
    When you say "manually do this", do you mean literally typing in the values to each changed cell?? ('cuz you definitely don't have to do that... )

    It's so hard to give advice since I can't see the database table or the excel spreadsheet... but let me try...

    I do a lot of this kind of thing at work...

    What I would probably do is set up a query in Excel that will connect to the table in the database... and then hit Refresh whenever you want it updated... I'd make a separate worksheet that will hold the data resulting from the query and then use a different sheet for the pivot table(s), formatting and/or data manipulation... referencing the data on the original data sheet...

    Try going to Excel and then use the Data menu to create the query...
    Data --> Get External Data --> New Database Query
    After you play with that for a bit, you'll see what I mean...

    HTH

Posting Permissions

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