vb query help?

[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 tested the code below as follows:-

1. Create a form with a Command Button called ExportToExcel.

2. Press Ctrl-G to bring up the Debug Window.

3. Select Tools --> References

4. Make sure that a Reference named Microsoft Excel X.X Object Library is
Ticked (where X.X is a number). If it isn't find it in the list and Tick it.

5. In the On Click event of the ExportToExcel Command Button put the
following code (I've annotated it so hopefully you can see what's going on)

Private Sub ExportToExcel_Click()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim rst1 As ADODB.Recordset
Dim strSQL As String
Dim intLoopCounter As Integer
Dim strLastAccMan As String
Dim strCurrentAccMan As String

'Create a string which contains your Query
'Note Account Manager MUST BE the first field in the ORDER BY clause

strSQL = "SELECT tblClientList.Client, tblAccountManager.AccountManager
" & _
"FROM tblAccountManager INNER JOIN tblClientList ON " & _
"tblAccountManager.ID = tblClientList.AccountManagerID " & _
"order by tblAccountManager.AccountManager,
tblClientList.Client"

'Create a Recordset to Hold the results of your Query

Set rst1 = New ADODB.Recordset
Set rst1.ActiveConnection = CurrentProject.Connection
rst1.Open strSQL

'Open Excel and Create a new Workbook

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Add

'Move to the Start of your Recordset

rst1.MoveFirst
intLoopCounter = 2
strLastAccMan = ""

'Populate your Excel Spreadsheet(s)

Do While Not rst1.EOF
strCurrentAccMan = rst1!AccountManager

'Check to see if the Account Manager name for the current record
'matches the Account Manager Name for the previous record
'If it doesn't then create a New Sheet with Name = Account Manager
'and columns headed Client Name and Account Manager

If strLastAccMan <> strCurrentAccMan Then
Set oSheet = oWB.Worksheets.Add
oSheet.Name = strCurrentAccMan
oSheet.Cells(1, 1).Value = "Client Name"
oSheet.Cells(1, 2).Value = "Account Manager"
intLoopCounter = 2
End If
oSheet.Cells(intLoopCounter, 1).Value = rst1!Client
oSheet.Cells(intLoopCounter, 2).Value = rst1!AccountManager
strLastAccMan = strCurrentAccMan
intLoopCounter = intLoopCounter + 1
rst1.MoveNext
Loop

rst1.Close

End Sub

This will return all of the Client Details for all of the Account Managers.
If you want to restrict the selection then you can alter the Query string
accordingly.

HTH

Andy W

 

[Stu Dongel]

Andy,
thank you so much i have quite picked up the ado stuffs quite yet.
i learned alot. and it works great.

stu

 

[Guest]

Stu

Glad I could help.

Regards

Andy W

Andy,
thank you so much i have quite picked up the ado stuffs quite yet.
i learned alot. and it works great.

stu