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

avalve

New member
Local time
Yesterday, 23:10
Joined
Jan 17, 2014
Messages
2
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

Enthusiastic Amateur
Local time
Today, 06:10
Joined
Sep 21, 2011
Messages
14,048

Attachments

  • Connection.PNG
    Connection.PNG
    10.3 KB · Views: 170
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 06:10
Joined
Sep 21, 2011
Messages
14,048
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

Enthusiastic Amateur
Local time
Today, 06:10
Joined
Sep 21, 2011
Messages
14,048
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.:cool:

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.
 

Users who are viewing this thread

Top Bottom