1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Export a simple query result to an excel file

Discussion in 'Software Development' started by wop_nuno, Mar 9, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. wop_nuno

    wop_nuno Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    46
    Hi everyone,

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

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

    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
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "strSQL", strFullPath & "Test.xls", False

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

    Can anyone help me?
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    You haven't defined it as a type of object. However, I would simply build a regular query - say called qryexport - and use that instead of defining the export in the code (it is far easier and faster to alter a query, and that would be all you needed to do).
    That would give you something like
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryexport", strFullPath & "Test.xls"
     
  3. wop_nuno

    wop_nuno Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    46
    Hi slurpee55,

    Thanks for the reply.

    What i'm trying to do is exactly what you said.
    In my case" strSQL" is the same of your "qryexport", the only problem is that i don't know how, and where to "define" the query.

    Can you help me?

    Thanks
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Have you actually built a simple query and named it "strSQL"?
    Also, make sure all spellings are exactly the same - " strSQL" isn't the same as "strSQL".
     
  5. wop_nuno

    wop_nuno Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    46
    Hi slurpee55,

    My code is in my initial post.

    strSQL = "SELECT ID, NomeCandidato, DataEntrada FROM FichaCandidatura

    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")

    Isn't this my query "SELECT ID, NomeCandidato, DataEntrada FROM FichaCandidatura" (strSQl)?
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    A SQL statement should read like this
    SELECT FichaCandidatura .ID, FichaCandidatura .NomeCandidato, FichaCandidatura.DataEntrada FROM FichaCandidatura;

    Unless you are adept at SQL (and I know only a few who are - not me!) it literally is much easier to build a query with what you want in Access - don't try to code it in your VBA.
    Then just name the query as the source of the data in your VBA.
     
  7. wop_nuno

    wop_nuno Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    46
    Hi slurpee55,

    But that's what i'm doing....
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    No, you are attempting to define the query within the code.
    In Access (you never stated this, but i assumed this export was from Access to Excel - you do state the Excel part), go to Queries.
    Click on Create query in Design view.
    In the pop-up, find FichaCandidatura (Is it a table or a query? Your names don't tell me.)
    Drag the fields ID, NomeCandidato, and DataEntrada to the query.
    Click on save and name it - let's call it strSQL for simplicity.
    (Note, if those 3 fields are all that is in FichaCandidatura, you don't need to do the above, but I don't think they are, given your attempt to define the SQL.)

    At any rate, once you have built an actual query named strSQL, your code should work.

    If you go tothe query and look at it in Design view and then go to SQL view, you will find that your SQL is what I wrote above, that is:

    SELECT FichaCandidatura .ID, FichaCandidatura .NomeCandidato, FichaCandidatura.DataEntrada FROM FichaCandidatura;
     
  9. wop_nuno

    wop_nuno Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    46
    Ok, i've already did what you told me.

    I created a view (i think is the same as a querie) called strSQL.

    I've used this code line:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "strSQl", strFullPath & "Test.xls", False

    and i get this error: Microsoft Office doesn't find the object strSQL.
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    What are you using? I have no idea what you are talking about with a "view." Databases have tables, queries, forms and reports, with some additional items varying by program and versions - but none that I know of called a view.
     
  11. wop_nuno

    wop_nuno Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    46
    I said view, because it's in portuguese.

    So i've created a querie called strSQL, but i still get the message i told you.
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Oh, I would bet you are using MySQL. My bad.
    More later as I read up on views.
     
  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    In Portuguese it is called what? A pergunta? A questão?
    And this code is placed in Access, I presume?
     
  14. wop_nuno

    wop_nuno Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    46
    Could it be because i'm using Access connecting to a SQL database (ADP)?
     
  15. wop_nuno

    wop_nuno Thread Starter

    Joined:
    Mar 9, 2009
    Messages:
    46
    LOLLLL

    Query literally translating is " questão" (question), but it's called a "view".

    So you don't know how i can get trough this?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/807840

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice