An Approach to do this via Excel?? (1 Viewer)

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:04
Joined
Jul 9, 2003
Messages
16,245
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:-


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

KitaYama

Well-known member
Local time
Today, 22:04
Joined
Jan 6, 2022
Messages
1,490
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.


2022-07-06_11-23-31.png
 

arpod

New member
Local time
Tomorrow, 00:04
Joined
Jul 12, 2020
Messages
11
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

Nifty Access Guy
Staff member
Local time
Today, 13:04
Joined
Jul 9, 2003
Messages
16,245
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

Lifelong Learner
Local time
Today, 06:04
Joined
Mar 14, 2017
Messages
8,738
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

Well-known member
Local time
Today, 22:04
Joined
Jan 6, 2022
Messages
1,490
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

Lifelong Learner
Local time
Today, 06:04
Joined
Mar 14, 2017
Messages
8,738
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

New member
Local time
Tomorrow, 00:04
Joined
Jul 12, 2020
Messages
11
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.)
 
Last edited by a moderator:

Users who are viewing this thread

Top Bottom