Results 1 to 4 of 4
  1. #1
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36

    Export Qry to Excel with Variable Date in File Name


    Hi...I have a question on exporting a query to Excel with a file name that contains a variable date found in the source query.

    I have added and modified Dim statements a number of time, tried to add the field where the PayDt information is found, etc., however I keep receiving a variety of errors. Currently, my code looks like this:

    Code:
    Private Sub ctlFormUSA_Click()
    DoCmd.SetWarnings False
    DoCmd.OutputTo acOutputQuery, "qry_Form_USA", "ExcelWorkbook(*.xlsx)", "q:\2013Test\Admin\Forms\USA_FORM_" & Format(ProcDt, "ddmmmyyyy") & ".xlsx", False, "", , acExportQualityPrint
    MsgBox "Creation of Form for ""USA"" has been completed.", vbInformation, "Export Complete"
    DoCmd.SetWarnings True
    The ProcDt value needs to be from a field in the "qry_Form_USA". That field in itself is a logic statement that evaluates data from two different tables and determines what the final result should be.

    I would like the file name that is exported to be "USA_FORM_[Then the date from that field which needs to be in 17Jun2013 format]. This would allow us to change the "ProcDt" in the table that the query links to versus having to hard-code it and change it out each time in the code.

    Any suggestions on how to make this work?

    Thanks.

  2. #2
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Is ctlFormUSA a button on form qry_Form_USA?

    If so, then Me.ProcDt is the handle for that control. Me refers to the form which is active (qry_Form_USA), the dot (.) says we're about to name a method or property of that form, in this case a control, and ProcDt is the name of the control.

    So, try replacing ProcDt with Me.ProcDt in your Format statement.

    If that doesn't work, then tell me what the name of the current form is, and whether you are sure that qry_Form_USA must be loaded at the time that ctlFormUSA is clicked.

    If that doesn't work, then tell me

  3. #3
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36

    Export Qry to Excel with Variable Date in File Name

    I think my setup is a little different; I am not sure if that is good or bad.

    This is what I have:
    • I have a form called frm_ChForms.
    • On this form, there are buttons for several countries (USA, UK, etc.). The Name for the USA button is ctlFormUSA.
    • When you click on this button, it runs the code listed above (in the first post). This code exports query qry_Form_USA to Excel.



    The screen looks similiar to this:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	6 
Size:	4.0 KB 
ID:	12783

    Based on the way I have set this up, can you clarify how this may change the information shared? I am very new at this and appreciate your assistance.

    Regards.

  4. #4
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    So, precisely where IS the field ProcDt? calculated in the query?

    Dear.

    Then that field doesn't exist until the query is run, so it can't be accessed at the time you are requesting for the query to be run.

    You might need to open the query, but hidden, grab the value, (maybe close the query), then output the query.

    Is there an easier way to calculate the date you need?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Excel file based on a date and time in file name
    By nhylan in forum Import/Export Data
    Replies: 4
    Last Post: 04-16-2013, 03:26 PM
  2. export data on excel file
    By Mina Garas in forum Queries
    Replies: 1
    Last Post: 12-01-2012, 02:43 PM
  3. Export table to txt file with a variable filename
    By macollins7 in forum Import/Export Data
    Replies: 6
    Last Post: 07-12-2012, 09:44 AM
  4. Export all tables to 1 excel file
    By vestlink in forum Programming
    Replies: 5
    Last Post: 10-03-2011, 02:45 AM
  5. Export to excel (File Name issues)
    By fpmsi in forum Import/Export Data
    Replies: 6
    Last Post: 09-22-2011, 02:09 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums