Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Generating a filename with Output To

    Hi,

    I'm trying to use the output to function in a macro to export the results of a query into an excel spreadsheet. (Access 2000)

    Is there any way to get Access to generate the output file name for me based on some of the results of the query?

    Example:

    I have a field called [territory] in the query.
    I tried something like this but it did not work:

    "c:\data\Report Output\Data for Territory "& [territory] & ".xls"

    Thanks.

  2. #2
    Join Date
    Sep 2003
    Posts
    7

    Re: Generating a filename with Output To

    Assumption: query results are all records from same [territory]

    If so you might try:
    Dim db As Database
    Dim qdf As DAO.QueryDef
    Dim rs As Recordset
    Dim strTerritory As String
    .
    .
    Set qdf = db.QueryDefs("QueryName")
    Set rs = qdf.OpenRecordset()
    .
    .
    rs.MoveFirst
    'assigns value of the [territory] field in the first record to a string
    strTerritory = rs![territory]
    DoCmd.OutputTo ...... "c:\data\Report Output\Data for Territory "& strTerritory & ".xls"
    .
    .
    rs.Close


    Note that if the query is a parameter query you'll need to supply the parameters in the procedure:
    qdf.Parameters("[Forms]![FormName]![FieldName]") = Me![FieldName]
    (this example assumes the parameter is a value in a field on the current form)

    If the query result includes records from more than one territory, obviously the string strTerritory will be the first territory returned.

    OM Gang



    Originally posted by Backle
    Hi,

    I'm trying to use the output to function in a macro to export the results of a query into an excel spreadsheet. (Access 2000)

    Is there any way to get Access to generate the output file name for me based on some of the results of the query?

    Example:

    I have a field called [territory] in the query.
    I tried something like this but it did not work:




    Thanks.

  3. #3
    Join Date
    Sep 2003
    Posts
    2

    Re: Generating a filename with Output To

    Thanks for the reply.

    I tried to use it but I keep getting a type coversion error at this statement:

    Set rs = qdf.OpenRecordset()

    Any idea why? I don't really get how to open and manipulate recordsets yet.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try a small modification:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strTerritory As String
    set db = currentdb
    Set rs = db.OpenRecordset("theNameOfYourQuery")
    rs.MoveFirst
    'etc
    'etc

    (and you will need a reference to the DAO library)

    izy

  5. #5
    Join Date
    Dec 2004
    Posts
    3

    Similar problem

    Hi,

    I am having a similar problem with the outout to function in a macro. I am trying to ouput a report without having to enter the name and location for each one as there are over 30,000 files that will be created. I think I understand what the code is saying but I am unsure as to how to ncorporate the code into the database. Do I need to convert my macro to a module? If so, can I still call the module from the form? The filename string I am trying to create looks something like this:

    OutputTo ...... "f:\claim\type\"[claim]&"_"&[type]&"_123104.rtf"

    where [claim] and [type] are form fields,
    the report name is [offload_notes],
    the underlying query name is [stream]
    and the form name is [input]


    Any help you cna provide would be beneficial.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •