query madness!!

[Stu Dongel]

Hello all,
I am having a heck of a time working on this query/vb code. What I am
trying to do is export data to an excell spread sheet. Specificaly a list of
clients, on a sheet in the work book labeled with the account managers name.
I have done this (sort of half aked) with the folowing code

*******
Dim StrXLFile As String 'Added this (missing)
StrXLFile = "testquery.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "test2query",
StrXLFile, True

Set excelsheet = GetObject(StrXLFile)
excelsheet.Application.Visible = True

excelsheet.Application.Windows(1).Visible = True
******

This exports the data from test2query here is the SQL
******
SELECT tblClientList.Client, tblAccountManager.AccountManager
FROM tblAccountManager INNER JOIN tblClientList ON
tblAccountManager.ID=tblClientList.AccountManagerID
WHERE (((tblAccountManager.AccountManager)="open"));
******

the problem with my current system is that i need to change the criteria of
this query based on a list of acount managers (AccountManager) in the table
tblAccounManager and then loop through so it creates a new sheet for each
manger, till it runs out. i think it is possible to do this all in vb,
rather then relating to a real query.

Im pullin my hair out here! Any help or nudges would be great. Thanks in
advance all.

Stu

 

[Andy Williams]

Stu

I've replied to the query you posted in the FormsCoding group

Andy W