Create Looping query to export to individual XL spreadsheets...!

[Shuffs]

I have a table which contains Supplier Purchase Orders.

I want to email each Supplier their own data in Excel format (Excel
97), which is catered for by other code.

I want to do this in a loop and I am having difficulty with how I
would do this! The thing is, I think this should be really easy, but I
am stumped!

I'm certain it is with the TransferSpreadhseet, but as it requires me
to provide a table/query name to export, it flops as my SQL statement
is created within the loop and is not a saved query

My code is below;

Thanks to anyone who can nudge me onto the right tracks!

Cheers Phil

By the way, today is the day my Company starts it's Special High
Intensity Training, and my Manager being very keen, has given me as
much S.H.I.T as I can handle!
It can only get better!!

'************CODE STARTS**************
Private Sub cmdExportToXL_Click()
On Error GoTo Err_XLExport

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSUPP_SQL As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblSUPPLIER_EMAIL")

Do Until rs.EOF

strSUPP_SQL = "SELECT [Branch Overdue Order Chase].* FROM [Branch
Overdue Order Chase] WHERE ((([Branch Overdue Order Chase].SUPP_ID)='"
& rs![SUPP_CODE] & "'));"
Debug.Print strSUPP_SQL

'DoCmd.TransferSpreadsheet <<<---Inspiration required!

rs.MoveNext
Loop

Exit_XLExport:
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
DoCmd.SetWarnings True
Exit Sub

Err_XLExport:
MsgBox "Error Description: " & Err.Description, vbCritical + vbOKOnly,
"Error Number: " & Err.Number
Resume Exit_XLExport
End Sub

[Sith Lord]

On 30 Oct 2002 04:04:59 -0800, in message
<54947ba2.02103...@posting.google.com>,
phi...@globalnet.co.uk (Shuffs) wrote:

Make a query called qryExpSupp, put anything you want in it for now.
In your loop, set the SQL property for that query to your custom
string then export that query, code below.

'************CODE STARTS**************
Private Sub cmdExportToXL_Click()
On Error GoTo Err_XLExport

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSUPP_SQL As String

' *** Added code line ***
Dim qdf as QueryDef

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblSUPPLIER_EMAIL")

Do Until rs.EOF

strSUPP_SQL = "SELECT [Branch Overdue Order Chase].* FROM [Branch
Overdue Order Chase] WHERE ((([Branch Overdue Order Chase].SUPP_ID)='"
& rs![SUPP_CODE] & "'));"
Debug.Print strSUPP_SQL

' *** Added code lines ***
Set qdf=db.querydefs("qryExpSupp")
qdf.SQL = strSUPP
qdf.Close
Set qdf = Nothing
' next line may wrap in newsreader
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryExpSupp", "filename.xls"
' *** end of added code lines ***

'DoCmd.TransferSpreadsheet <<<---Inspiration required!

rs.MoveNext
Loop

Exit_XLExport:
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
DoCmd.SetWarnings True
Exit Sub

Err_XLExport:
MsgBox "Error Description: " & Err.Description, vbCritical + vbOKOnly,
"Error Number: " & Err.Number
Resume Exit_XLExport
End Sub

--
Charlie was a chemist, Charlie is no more,
What Charlie thought was H2O, was H2SO4.

[Shuffs]

Many thanks for the response and the solution.
It works a dream, and far elegant than I could picture.
Now I understand querydefs a little better, I have many more places to
use this technique.
Once again, cheers.

Phil

Sith Lord <sith...@besty.org.uk> wrote in message news:<j0kvruobrej860kkg...@4ax.com>...