+ Reply to Thread
Results 1 to 8 of 8

ACCESS: Using Excel formulas with ACCESS ranges

  1. #1
    Registered User
    Join Date
    03-06-2013
    Location
    EU
    MS-Off Ver
    Excel 2010
    Posts
    4

    ACCESS: Using Excel formulas with ACCESS ranges

    Hello,

    I googled a bit about my issue here I have not found the answer to my question.
    I store data monthly on MS ACCESS databases. I then have an Excel workbook that I want to use to analyze the data.
    The data stored in ACCESS is recorded through Excel too btw; so Excel acts as a bridge for the data going in AND out of the ACCESS database.

    So what I am trying to do is create a Connection to the ACCESS database, and then create formulas in Excel using ranges within that ACCESS database.

    But I have not found out if it's actually possible or not.
    I could copy the data back into Excel in the worse case scenario and use it from there, but I am pretty sure I can direct link my formulas to ACCESS directly.

    I have managed to create Jet OLEDB connection to the MSA database, but I dont know what are the handles to exploit the newly made connection to MSA.

    Any HowTo's would be greatly appreciated.

    Thanks for helping!
    Last edited by zazathedog; 06-05-2013 at 05:51 AM.

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: ACCESS: Using Excel formulas with ACCESS ranges

    I'm not really clear on what you're tying to do. Can't you create a query in access then bind a query table/ pivot table to it in Excel? Or maybe power pivot?

  3. #3
    Registered User
    Join Date
    03-06-2013
    Location
    EU
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: ACCESS: Using Excel formulas with ACCESS ranges

    Ok so I need to bind the database from ACCESS to Excel as a Table or Pivot Table first?
    For example I want to use the SUMIF formula, using data stored in the ACCESS Database.
    What I want to avoid doing is copying the data from ACCESS to EXCEL, and then having to delete it later, but maybe that is the only possible way to do the job.
    Last edited by zazathedog; 06-05-2013 at 05:58 AM.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: ACCESS: Using Excel formulas with ACCESS ranges

    Can't you do it in SQL? Or the Access query builder? Access has a lot of built in functions and if you have a relational database, it's often easier to do calculations on the data directly in SQL rather than exporting it.

  5. #5
    Registered User
    Join Date
    03-06-2013
    Location
    EU
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: ACCESS: Using Excel formulas with ACCESS ranges

    EXCEL would be acting as a dashboard, that's why I need to analyze the data from EXCEL and not from ACCESS.

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: ACCESS: Using Excel formulas with ACCESS ranges

    Then import it with a Query table or a pivot table and then just refresh it when you want the latest data

  7. #7
    Registered User
    Join Date
    03-06-2013
    Location
    EU
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: ACCESS: Using Excel formulas with ACCESS ranges

    That was the situation where I was, but I decided to store the raw data on ACCESS, and only use Excel to analyze the data.

    So it is NOT possible to make any direct relation to the data store on ACCESS by using Excel formulas?

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: ACCESS: Using Excel formulas with ACCESS ranges

    Stop SHOUTING I get it, you're using Access.

    If you want to analyse something in Excel you need to import it - if you didn't performance would be terrible. The idea of analysing with Excel is that you don't import the whole database, you import subsets that you can work with (usually aggregated datasets) - so you set up queries that pull a subset of the data you want and then bind your query tables to this - you can then paramaterise these tables, have a search for "Excel parameter queries" and you'll get plenty of hits.

    Typically dumping data into Excel without aggregation results in huge data sets since you have 1-to-many or many-to-many relationships which will duplicate your data; so you usually do some sort of pre-processing in SQL

    Does that address your requirements?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1