Advanced export to excel file

Discussion in 'Microsoft Access Queries' started by Hallgeir, Apr 17, 2005.

  1. Hallgeir

    Hallgeir Guest

    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
     
    Hallgeir, Apr 17, 2005
    #1
    1. Advertisements

  2. Hallgeir

    Guest Guest

    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"
     
    Guest, Apr 18, 2005
    #2
    1. Advertisements

  3. Hallgeir

    Hallgeir Guest

    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, Apr 18, 2005
    #3
  4. Hallgeir

    JohnFol Guest

    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
    >
     
    JohnFol, Apr 18, 2005
    #4
  5. Hallgeir

    Gary Walter Guest

    "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
     
    Gary Walter, Apr 18, 2005
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JerryB

    Qurey producing empty Excel Export file

    JerryB, Jan 28, 2004, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    194
    JerryB
    Jan 28, 2004
  2. Guest

    export 1 query multiple times to 1 excel file

    Guest, Jun 19, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    212
    Ken Snell
    Jun 19, 2004
  3. Guest

    export 1 query 5 times to 1 excel file

    Guest, Jun 19, 2004, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    143
    Guest
    Jun 19, 2004
  4. Guest

    export 1 query 5 times to 1 excel file

    Guest, Jun 19, 2004, in forum: Microsoft Access Queries
    Replies:
    7
    Views:
    238
    Ken Snell
    Jun 20, 2004
  5. Guest

    Export query as csv file instead of a text file

    Guest, May 17, 2005, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    363
    Guest
    May 17, 2005
Loading...

Share This Page