Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    82

    Unanswered: Click to export and open Excel file

    Hi,
    Any idea what's going on in this code below?
    Thx,
    Jabo
    -----------

    Private Sub cmdContractStatusExcelOutput_Click()
    DoCmd.OutputTo acOutputQuery, "qryContractsStatus", acFormatXLS, "ABC-Test", True
    End Sub

    Explanation: I have a button on a form that, when clicked, is supposed to export the data in a query ("qryContractsStatus") as an Excel file and open the file.

    When I gave the full path, I had a compile error; when I removed it and just gave the file name ("ABC-Test"), it appeared to run but did nothing. I also couldn't find the new file anywhere.

  2. #2
    Join Date
    Mar 2004
    Posts
    82
    An update:

    I got this to work - simply by removing the path. Now, the system prompts me for a file location and name. Once given, it opens up the newly exported file in Excel.

    But, my expectation are now a tad higher: I'd like to be able to save like files as worksheets within an Excel workbook (the OutputTo procedure does not have an argument to specify worksheets). Furthermore, I'd like to set up template worksheets, based on some adjacent exported data worksheet, either to graph or provide additional summaries. So, everytime data is exported into a particular worksheet, the worksheet with graphs get updated as well.

    Thoughts?

    Jabo

  3. #3
    Join Date
    Dec 2002
    Posts
    34
    Originally posted by Jablonski
    An update:

    I got this to work - simply by removing the path. Now, the system prompts me for a file location and name. Once given, it opens up the newly exported file in Excel.

    But, my expectation are now a tad higher: I'd like to be able to save like files as worksheets within an Excel workbook (the OutputTo procedure does not have an argument to specify worksheets). Furthermore, I'd like to set up template worksheets, based on some adjacent exported data worksheet, either to graph or provide additional summaries. So, everytime data is exported into a particular worksheet, the worksheet with graphs get updated as well.

    Thoughts?

    Jabo
    You can try transferspreadsheet instead of outputTo. That will add sheets instead of creating a new workbook.

  4. #4
    Join Date
    Mar 2004
    Posts
    82
    Sorry - the TransferSpreadsheet procedure didn't work.
    Does one need to specify (and how?) the worksheet name? My error message said something about append not working.

Posting Permissions

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