PC Review


Reply
Thread Tools Rate Thread

Advanced export to excel file

 
 
Hallgeir
Guest
Posts: n/a
 
      17th Apr 2005
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


 
Reply With Quote
 
 
 
 
=?Utf-8?B?Q2hyaXNK?=
Guest
Posts: n/a
 
      18th Apr 2005
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"


 
Reply With Quote
 
 
 
 
Hallgeir
Guest
Posts: n/a
 
      18th Apr 2005
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


 
Reply With Quote
 
JohnFol
Guest
Posts: n/a
 
      18th Apr 2005
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" <(E-Mail Removed)> wrote in message
news:Z4L8e.5411$(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
Gary Walter
Guest
Posts: n/a
 
      18th Apr 2005

"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



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Advanced Conditional Formatting Ideas Needed! (ok, maybe not that advanced...) shadestreet Microsoft Excel Misc 2 21st Jul 2006 03:04 PM
"More Advanced" button in Advanced Find =?Utf-8?B?b2ZyYQ==?= Microsoft Outlook Discussion 2 21st Jun 2005 07:36 AM
EXPORT EXPORT EXPORT jubejoie@aol.com Microsoft Access External Data 1 12th May 2004 12:05 AM
"More Advanced" button in Advanced Find is inactive SAM Microsoft Outlook 0 26th Dec 2003 09:40 PM
Outlook 97 - Journal - File - Import & export - Export to a file - Micros Excel Sam Microsoft Outlook Discussion 0 25th Jul 2003 01:56 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:52 PM.