An Approach to do this via Excel??

[Uncle Gizmo]

I think. Basically I have a query in Access that outputs some values, which I then need to calculate by subgroup in Excel,

I answered a question a while back where the user wanted to take subgroups out of an MS Access table and create a separate sheet for each subgroup in Excel. I found a piece of code by BTAB Developments, it was created by a former member here, Bob Larson.

This is a video showing it running:-

Make Hundreds of Excel Sheets From MS Access Table - Nifty Access​

and there's more information about it on my website here:-

Make Excel Sheets From Access Table - Nifty Access

Excel Sheets From Access Table If you've ever faced the challenge of exporting data from Microsoft Access into Excel, you’re not alone! One of the most common requests is to organize data by categories, like exporting car makes into separate Excel sheets or even separate Excel files. I’ve...

www.niftyaccess.com

thought it might be the sort of code you can hack about to your hearts content!

 

[arpod]

Thanks @Isaac and @Uncle Gizmo ... will have a play with those responses and see if I can make some headway. Appreciated.

A

 

[KitaYama]

I see so many posts for requests to output data from Access to Excel. And it's always a mystery why everyone tries to do it manually or with vba.
I never understand if the data is going to be manipulated in Excel, why everyone tries to export data from Access to Excel. And not importing data from Access to Excel.
Excel has very nice support for importing data from Access or Sql Server.
You can link a table or query in Access to Excel sheet without a single vba line, add tables or pivot table to do whatever you want with the linked data. Just select import data, select your database and the last step is only selecting the source data (table/query/view).

Excel also gives you a very powerful query editor to edit the source query/table before data being imported.

 

[arpod]

Hi @KitaYama .. thanks.

I do use Power Query quite a bit, and quite often using Access queries as the source. .

But in this instance my challenges are:

1. iteratively splitting into subgroups for analysis within Excel, and
2. getting the results automatically back into Access

Regards,
A

 

[Uncle Gizmo]

Excel has very nice support for importing data

I assume it can also import from CSV files?

I would love to see a demo showing how to import the cars CSV and place each model in a separate sheet.

 

[Isaac]

I never understand if the data is going to be manipulated in Excel, why everyone tries to export data from Access to Excel. And not importing data from Access to Excel.

This sentence is hard to understand. If you understand that data is going to be manipulated in Excel, then how is it confusing that the person needs to get it into Excel (from Access), in order to DO that manipulation?

Obviously there are use cases for going in both directions, not just one. Excel has multitudinous number-crunching and statistical features that would be extremely difficult to recreate by hand in Access.

Furthermore, wanting Reports (from database data in Access) to be output TO Excel is probably the #1 most requested delivery method in the world. So why is it hard to understand why people want to get the data into Excel?

 

[KitaYama]

Furthermore, wanting Reports (from database data in Access) to be output TO Excel is probably the #1 most requested delivery method in the world. So why is it hard to understand why people want to get the data into Excel?

Seems that we have a misunderstanding here. I understand why people want to get the data into Excel. I do it regularly.
I don't understand why they do it in Access, while Excel has better tools for import data.
What I wanted to say was it's much easier to import data in Excel.
I've seen a lot of posts that members have used a lot of vba just to send a set of data to Excel.
Adding Excel reference, creating a new instance of Excel app, opening the target Excel file, creating a recordset of data, loop through the recordset and write data to a sheet, and sometimes formating cells,etc.
I normally add a query def that contains the data I need, In Excel I link a sheet to this query, the job is done. As simple as that.

I just was trying to say it's much easier to import data in Excel rather than export in Access.

 

[Isaac]

Ahh, OK, thanks for clarifying.

I can appreciate your position on that matter, although sometimes there is a desire on the part of the developer not to have to maintain that extra Excel file, but I agree - if the Excel portion is fairly complex, then it may warrant maintaining an Excel program/format/template (etc) and just linking to the Access database.

Actually, my suggestion wasn't entirely incompatible with what you are saying. Although it's true I didn't suggest linking from inside Excel, I did support the OP's desire to leave all of the complex stuff in Excel and just get the data into there in a simple way (albeit I admit using Access vba, but that is only a few lines of code - and once you get used to it it doesn't seem much). I've actually found the more modern PowerQuery interface to be extremely troublesome in Excel 2019 and later (including 365), as it assumes too much and transforms columns in a way I didn't like.

There are more reasons why the "doing it from inside Excel" doesn't work well - various characteristics of an Access query aren't accessible by Excel. In fact now that I remember, that's probably the bigger reason I stopped doing that very often. Excel can only import the simplest of Access queries. It gets buggy or downright can't "see" them when certain conditions are true.

Once I found out about all the "gotchas", I just stopped doing it as it was less work to centralize all the coding from inside Access

 

[arpod]

I know this is stale, but thought I'd circle back and a) thank all those that responded with their input, and b) update that I managed to get chatGPT to put together code that does exactly what's required. Took a few revisions and clarifications, but probably about 90 minutes in total from start to testing.

(And as usual, the more specific you get with the requirements, the more accurate the outcome.)