SUNIF/Query link between Excel & Access?

[agormanshaw]

Well, my first post to this board, here goes...

Originally, I set up two worksheets in Excel, one with data and one with a report. I used SUMIF to pull the appropriate data for my report.

My data has gotten big (& complex) enough that I have moved it to Access. The data is used for other purposes than this particular report, so moving it to Access was a good thing for me.

What I would like is an equivalent method of getting the data that I want from the Access table into a cell in Excel. Something to the effect of :
In the table GLData, total the field Current for Month = "Jan" and Year = "2003" and Group = "Revenue"
(Month, Year, Group, & Current are all fields in the table GLData).

DSUM would work perfectly except that when Excel talks about "database", it means a selection of cells in Excel as opposed to an external database like Access.

I have considered writing the report in Access, but it would require a huge pile of sub reports, and would not be flexible as reporting requirements are changed by my boss...

Thanks for any help on this...

 

[lickalotapus]

There is probably an easier way to accomplish what you're trying, but since nobody has replied yet, I figured I'd throw out two potential solutions...

1) You could write a custom function in Excel that uses DAO/ADO to execute a query in the Access database and return a single value to the cell. I'm not sure how comfortable you are with DAO/ADO, but here's a page that explains some of the different methods w/examples:
http://www.erlandsendata.no/english/vba/adodao/index.php

2) You could set up a pivot table (which would act as your summary report) in Excel and use the Access database as the data source. This also has the advantage of letting you summarize more than 65,536 rows of data (which is the limitation of an Excel worksheet). Here's a basic tutorial on pivot tables in case you haven't used them:
http://www.ozgrid.com/Excel/PivotTables/ExCreatePiv1.htm

Hope that helps...

 

[agormanshaw]

Thanks! I'll have a look at the DAO/ADO thing and see if I can get it to work. I'm not at all familiar with it, but I'll give it a whirl.

I've already set up a pivot table to summarize data, and it works well for some general reporting. Financial statements in a predefined format are another headache though...

 

[agormanshaw]

Well, the ADO technique worked.

I am using that code you pointed me to to fill an array within Excel, and then running calculations off that array.

It is much faster than putting the data into Excel, and the Excel file size is smaller.

Thanks again for your help!



Aaron