Advanced export to excel file

This is a bit advanced for me, but I hope someone can help!

I have a recordset "rsCustomer" with the fields "Custno" and "Filename"
I have a query "qryNumbers" also containing, among other fields, "CustNo".
This query hold records for many different customers.
My masterplan was to loop through the recordset and save different excel
files for each customer containing only this customers records. I started
out with something like this:

Do Until rsCustomer.EOF
DoCmd.OutputTo acOutputQuery, "qryNumbers", acFormatXLS, ,
rsCustomer!Filename
rsCustomer.MoveNext
Loop

My problem is that there is not a possibilty (as far as I can see) in the
OutputTo method to put in something like "where CustNo = rsCustomer!CustNo".
So my problem is; How can I filter my query "qryNumbers" based on the field
"CustNo" from my recordset. Maybee this is a completly wrong approach, but I
appreciate any tips or hint.

Regards
Hallgeir

 

Try the following (untested) code

Dim qdf As QueryDef
Dim strSql As String

'Get the sql of the query and strip off the trailing ;
strSql = Left$(CurrentDb().QueryDefs("qryNumbers").SQL,
Len(CurrentDb().QueryDefs("qryNumbers").SQL) - 1)

On Error Resume Next 'in case it already exists
CurrentDb().CreateQueryDef "qryXYZ", strSql
On Error GoTo 0

Set qdf = CurrentDb().QueryDefs("qryXYZ")

Do Until rsCustomer.EOF
qdf.SQL = strSql & " Where CustNo = " & rsCustomer("CustNo") & ";"
DoCmd.OutputTo acOutputQuery, "qryNumbers", acFormatXLS, ,
rsCustomer!Filename
rsCustomer.MoveNext
Loop

CurrentDb().QueryDefs.Delete "qryXYZ"

 

Thanks a lot Chris! With a couple of minor adjustment your code worked just
fine. But I'm wondering about the syntax in:
qdf.SQL = strSql & " Where CustNo = " & rsCustomer("CustNo") & ";"
I'm used to do it like this: rsCustomer!CustNo. Is there any special reason
to do it your way?

regards
Hallgeir

 

Chris' method uses the default Fields collection and looks for the item with
the name "CustNo". He could equally have done this with
rsCustomer.Fields("CustNo") or even rsCustomer(6) / rsCustomer.Fields(6)
where 6 (or whatever number is correct) is the fields index within the
recordset

Some of this is personal preference, but there are subtle differences. I
personally prefer Chris' method (or the longer handed method I have shown)
as it's clear you are after a field and not some property of the recordset.
Makes it easier to read and support.





"Hallgeir" <> wrote in message
news:Z4L8e.5411$...
> Thanks a lot Chris! With a couple of minor adjustment your code worked
> just fine. But I'm wondering about the syntax in:
> qdf.SQL = strSql & " Where CustNo = " & rsCustomer("CustNo") & ";"
> I'm used to do it like this: rsCustomer!CustNo. Is there any special
> reason to do it your way?
>
> regards
> Hallgeir
>

 

"Hallgeir" wrote
> This is a bit advanced for me, but I hope someone can help!
>
> I have a recordset "rsCustomer" with the fields "Custno" and "Filename"
> I have a query "qryNumbers" also containing, among other fields, "CustNo".
> This query hold records for many different customers.
> My masterplan was to loop through the recordset and save different excel
> files for each customer containing only this customers records. I started
> out with something like this:
>
> Do Until rsCustomer.EOF
> DoCmd.OutputTo acOutputQuery, "qryNumbers", acFormatXLS, ,
> rsCustomer!Filename
> rsCustomer.MoveNext
> Loop
>
> My problem is that there is not a possibilty (as far as I can see) in the
> OutputTo method to put in something like "where CustNo =
> rsCustomer!CustNo". So my problem is; How can I filter my query
> "qryNumbers" based on the field "CustNo" from my recordset. Maybee this is
> a completly wrong approach, but I appreciate any tips or hint.
>
Hi Hallgeir,

Under the situation you describe (if I understand correctly),
I probably would abandon DoCmd.OutputTo method and
simply parse out a "create, insert, or append" sql within the rs loop....
something like (untested):

strPath = "C:\TEMP\"
'if "C:\TEMP\CustomerSoAndSo.xls" does not yet exist,
'following will create the xls file w/worksheet of "yyyy_mm_dd_hh_nn_ss"
'if it already exists,
'following will add worksheet of "yyyy_mm_dd_hh_nn_ss"
'to "C:\TEMP\CustomerSoAndSo.xls"
Do Until rsCustomer.EOF
strFileName = rsCustomer!FileName 'assuming FileName like
"CustomerSoAndSo.xls"
strWhere = "WHERE [CustNo] = " & rsCustomer!CustNo
strSQL="SELECT * INTO " _
& "[Excel 8.0;database=" & strPath & strFileName & ";]." _
& Format(Now(),"yyyy_mm_dd_hh_nn_ss") _
& " FROM qryNumbers " & strWhere
CurrentDB.Execute strSQL, dbFailOnError

rsCustomer.MoveNext
Loop

I believe the above will work, but I did not test it.

Good Luck,

Gary Walter