Export Qry to Excel

[bconner]

If I have a Qry with more than 65,000 rows how can I export it into excel and have any rows after 65,000 drop into another worksheet in the same workbook?


Any help is greatly appreciated.....

 

[dbDamo]

Could your query not be split into smaller queries?

 

[bconner]

Unfortunately not.......

 

[boblarson]

So what version of Access/Excel are you on?

 

[dbDamo]

Unfortunately not.......

Why not?

You are going to have to split it over worksheets anyway, you might as well apply some logic to that split...

 

[MSAccessRookie]

Unfortunately not.......

You should be able to do that. You need to loop through the Recordset and write 65000 Records or less on each pass (remembering to increment the WorkSheet Tab ID) until the RecordSet is fully written to as many Excel Tabs as it takes.

 

[Funkyaccess]

You should be able to do that. You need to loop through the Recordset and write 65000 Records or less on each pass (remembering to increment the WorkSheet Tab ID) until the RecordSet is fully written to as many Excel Tabs as it takes.

Could you give me an example of looping through the recordset? I'm also trying to extract data from my query to excel.

 

[Funkyaccess]

Bump bump bump....

 

[namliam]

You could try using something like:
Select top 65000 .... from ...

then do an outer join with above query for sheet2
And another outer join with both above for sheet3
etc...

That is assuming you have some kind of PK in the query(s)