Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    link ACC to Excel (formula) (2000)

    At the risk of asking 2 questions in one day, I have been tossed another whammy, that I hope you can give some thought too when you have time...
    I have a formula in an Access Report that gives me a total of Employees I have on hand for the day...I need that formula to go into an Excel spreadsheet and update automatically (link) the worksheet there. Is there anyway to link a formula from Access (Form, Report, AnyWhere) to an Excel Spreadsheet Cell, so that it automatically will show the results in the Excel Cell? I've never tried this venture before and am totally lost.
    Thank you,
    NMPadgett <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: link ACC to Excel (formula) (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> NMP

    OK so you want a formula to come from MS-Access to MS-Excel?! Why if may ask? <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I mean as opposed to the value that needs to be in that cell?

    If you would return all the values into all the cells that matter to you, well that is it.

    Alternatively you could build the formula in MS-Excel and have it act on the returned data from Access. That is if you are talking about # of employees per day then you can use the <font color=red> COUNTA </font color=red> formula in Excel to count the range where you have the names of employees, or some other range that would be filled from Access.

    If you are returning many dates, well you have the SubTotaling...

    I guess if you would tell us what you are trying to do it will add value to the way we answer your questions.

    HTH

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link ACC to Excel (formula) (2000)

    It is possible to generate information in Excel from an Access database using Data|Get External Data|New Database Query (on the main Menu Bar). You may have to rebuild the Query (that is presently giving you your report in Access) in Excel to give you the result you're looking for. (If you're lucky, that may simply be a matter of copying the SQL from Access into your Excel Query.) At this stage, we would need a bit more detail. HTH
    Gre

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link ACC to Excel (formula) (2000)

    I'm sorry. Okay, the data in Access on the report is a list of Marines or Sailors that are present at the time. It is set up to show how many are actually available at the moment. The actual morning report is kept in Excel (WHY? Because I suppose that is where they have kept it for so long, ME, I would keep all of it in Access)...But once the tally is done in Access, they want the formula total to automatically show in an Excel cell in their morning report. I am sure keeping it in Excel (all of the info.,) would be the better idea. I didn't think it could be done, but...as in all of Microsofts apps, have learned to never say "never"....and you guys are so good at this, I thought you might have seen it in passing. A query externally imported might be the answer, but then its not actually a subtotal cell that gets filled in...but the whole query...it is a bit problematic, so if it sounds to wierd, just let me know. Maybe they can be talked into working this information onto a seperate Excel worksheet, and then just linking the first worksheet to the answer they get on sheet two...That would be my suggestion to them.
    Thanks for the effort and thought...ALWAYS
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link ACC to Excel (formula) (2000)

    GIven the structure you're describing, AFAICS the two worksheets Excel solution you've come up with will be the simplest (although not the most elegant) solution. Best of luck on the persuasion front!
    Gre

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link ACC to Excel (formula) (2000)

    And I would expect you can write a "summary" query that just returns a single number into a single cell...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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