Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

    Unanswered: DoCmd.TransferSpreadsheet help

    I am trying to have the user set the name of the file and then I want to recall that user-defined file name to add adidtional query results to that file under new tabs (sheets). How do I recall the user-defined file name to output multiple queries to a single file? The below will give me two files so I am looking to replace "Filename" with the appropriate code that will recall the initial file....Any suggestions will be appreciated. Thanks.

    Private Sub Command0_Click()

    DoCmd.OutputTo acOutputQuery, "qryNEWExpenseType", "ExcelWorkbook(*.xlsx)", "Exceptions" & InputBox("Enter date yyyymmdd:") & ".xlsx", False, "", 0, acExportQualityPrint

    DoCmd.TransferSpreadsheet acExport, 10, "qryALL Expenses by New Type", "FILENAME", False, ""

    DoCmd.TransferSpreadsheet acExport, 10, "qryALL Expenses by Sub Expense Type", "FILENAME", False, ""

    MsgBox "The reports have been exported to your desktop. ", vbInformation, "File Location"

    End Sub

  2. #2
    Join Date
    Sep 2002
    South Wales
    Rather than user InputBox("Enter date") etc. to allow user to specify filename, maybe use a custom form with a text box (or even a datetime picker). You can then refer to this value using VBA (Visual basic for applications).

    Although I have never used the function, transferspreadsheet allows you to specify the Worksheet name - you would specify a different worksheet for each query - more details here;

    TransferSpreadsheet Action

    If this means nothing to you then you might need further assistance - let us know.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Sep 2009
    My goal is to have the file name created by the user and have the user define the date (this is not dependent on today's date). I used transfer spreadsheet because I want the file to contain 3 select query results in one file.

Posting Permissions

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