Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    1

    Export a simple query result to an excel file

    Hi everyone,

    I'm kind of a newbie, when programming in VB so i'm having a little bit of trouble exporting a simple query result to a excel file.

    I've found this code that works fine when i use a table:

    Const FILE_PATH As String = "C:\My Documents\"
    Dim FULLPath As String
    strFullPath = FILE_PATH


    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tbl_name", strFullPath & "Test.xls", False

    MsgBox ("Export Complete")

    How can i do this using a query?
    Let's say my query is:
    strSQL = "SELECT ID, NomeCandidato, DataEntrada FROM FichaCandidatura"

    I've tried to do this

    Const FILE_PATH As String = "C:\My Documents\"
    Dim FULLPath As String
    strFullPath = FILE_PATH
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "strSQL", strFullPath & "Test.xls", False
    MsgBox ("Export Complete")


    but is says that it can't find the object "strSQL".

    How do i define my query to use in that line?

    Can anyone help me?

  2. #2
    ansentry's Avatar
    ansentry is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    May 2009
    Location
    Melbourne, Australia
    Posts
    67
    wop_nuno,0

    Just replace the name of your table with the name of your query.

    Code:
    Const FILE_PATH As String = "C:\My Documents\"
    Dim FULLPath As String
       strFullPath = FILE_PATH
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qry_name", strFullPath & "Test.xls", False
     
       MsgBox ("Export Complete")
    Just a little side issue why do you have this;

    Code:
    Dim FULLPath As String

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

Similar Threads

  1. cmd to print/view an MS-Excel file
    By gkast in forum Forms
    Replies: 0
    Last Post: 11-23-2008, 07:39 PM
  2. Result of Count Query not known elsewhere
    By Carole in forum Access
    Replies: 1
    Last Post: 09-07-2008, 09:39 AM
  3. Replies: 2
    Last Post: 08-04-2008, 04:16 PM
  4. Possible to export *.mdb file to *.exe file?
    By slpuiwan in forum Import/Export Data
    Replies: 1
    Last Post: 12-16-2007, 06:52 PM
  5. How do you export a file with a unique file name
    By Budman42 in forum Import/Export Data
    Replies: 1
    Last Post: 10-15-2006, 06:10 PM

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