Export table/query to separate excel pages in single workbook

[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.