Export table/query to separate excel pages in single workbook (1 Viewer)

[avalve]

Howdy! Let me make a small scenario. Let's say I have a table with fields CarMake/CarModel/Year/Color. What I am trying to do is to export the table to a single Excel Workbook with each CarMake having their own page displaying only their results.

I know that this is possible, but I can't wrap my head around it. Does it need to be a loop?

Thanks in advance.

 

[Gasman]

This might help?

https://answers.microsoft.com/en-us/msoffice/forum/all/export-access-records-to-multiple-tabs-in-an-excel/07d0150f-4f59-47f7-b88b-1d2da7836474

Edit:
Sorry, that might be a red herring. I decided to give it a go myself as it might come in useful in the future, but could not get 'GetTableDefsCount(dbs)' to compile.

So I amended to create on the fly and get a connection dialogue presented as shown in the pic and then get error 1004 "application-defined orobject-defined error"

 

[Gasman]

That link might well be useful after all.
In that thread they linked to https://btabdevelopment.com/export-a-table-or-query-to-excel-to-specific-worksheet/?pageid=49 writter by Bob Larsen.

However it expects both the workbook and sheet to already exist.?

So you could use a lot of the first code and inside a loop call Bobs function?

That is the way I would approach it should I need to do this.

The way I would approach your problem would be

Create a recordset for all models
Walk through this set creating a query with the model name as criteria
Use Bobs function to export the data
Loop until end of recordset.

Then tweak for missing data etc.

HTH

 

[Gasman]

Had a bit of a play and I believe I was at fault. Still cannot get that function to work, but by creating the sheet on the fly it works.

Be aware that when the workbook is created then 3 sheets are automatically created by default (with default settings?).

So I reckon you can use that, but your loop will be as I described.

 

[Uncle Gizmo]

I've come up with a solution which you can find on my website here:-

Excel Sheets From Access Table

There are full instructions on my Blog of how to get the Free Version up and running. If you have any problems post back in this thread!

There is also a ready-made demo database........