Splitting Query into Different Excel Worksheets from the same workbook

[Paddy]

Hi All



I have a problem which I am really struggling to solve.

I have a query, which has several fields, CompanyId, Company Name, CostCentre and a value

What I'm trying to do is export the query into an Excel spreadsheet, but instead of getting a worksheet with all the records, I'm trying the export the query, so it sets up a new spreadsheet, for every Company ID within the query, and then shows the records for each of the relevant Company IDs.

So for example

CompanyID	CompanyName	CostCentre	Value
1	Co A	AAA1	34
1	Co A	AAA2	234
1	Co A	AAA3	435
1	Co A	AAA4	4576
1	Co A	BBB1	3
3	Co B	CCC1	456
4	Co C	CCC2	2
6	Co D	DDD1	123
7	Co E	EEE1	324
7	Co E	EEE2	3245
7	Co E	EEE3	678

This would then produce 5 spreadsheets, each labelled CoId 1, 3, 4, 6 and 7 and show the relevant records for each one of them.

I get so far, but I cant figure out how to do the split. I cant set up a query for each companyID because, there could be 100 CompanyId's and it would be impractical to get someone to split out one excel spreadsheet onto many smaller ones, manually.

Any help would be very gratefully received.

Many Thanks

Paddy

[ranman256]

have a listbox with all the possible values needed, then scan the list.
A query uses the listbox as criteria, so each item in the list produces a different recordset.
i.e.: select * from table where [field] = forms!frmRpts!lstBox

'------------
Public Sub ExportSheets()
'------------
Dim vTabName,vFile
dim i as integer

vFile = "c:\folder\MyWorkbook.xlsx"

     'scan the list box
For i = 0 To lstBox.ListCount - 1
   vTabName= lstBox.itemdata(i)
   lstBox = vTabName
  
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsMyQry", vFile, True, vTabName
Next
End Sub

[ssanfu]

I get so far, but I cant figure out how to do the split.

What code do you have so far?


Maybe this will help. See
Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm