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.

Exporting an Access table to Excel

Discussion in 'Business Applications' started by karlhaywood, Aug 14, 2010.

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

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    Hi, as you can see from the title i want to export all the data in my access table to an excel spreadsheet. How do i go about this?

    Any help will be appreciated.

    Thank you.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    The turorial is good but this code is quite straight forward and you can put it in a vba pocedure

    Code:
    [COLOR="Red"]Call Xport2Excel(strQuery, strPath)[/COLOR]
    
    Function Xport2Excel(sTable As String, sFilename As String)
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sTable, sFilename, True
    End Function
    
    The red line of code you use to call the function
    strQuery is the query's name or the table's name, that no problem
    strPath expects the fullpath where you want the excel file.

    It's quite fast and works.
     
  4. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    Hi, Thanks both of you for your replies! Keebellah on what event procedure do i put the code? I have no experience with VBA.

    Thanks.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    You need to write a macro for that.
    You need 2 things, a procedure for the query to export and then you create a macro that call this procedure.

    Code:
    Sub Export_Query1()
    Call Export2Excel(<your query name here>, < the full path and filename to export to>)
    End Sub
    
    e.g.  Export2Excel("Selection_query",  "C:\mypath\export\Queryexport.xls")
    
    The maco you use to invoke the Sub...
     
  6. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    Could i just put a command button on my main menu, call it export to excel. On the onclick event put that code in?
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes you can.
     
  8. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    Hi OBP, How are you? Could you help me with the code please?
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Karl, just create a Button and then paste in
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sTable, sFilename, True
    and change sTable to the name of your table or query you want to export and the sFilename to the name of the Excel Workbook.
     
  10. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    I get an error message 2495 The action or method requires a table name argument

    Why is this?
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You need to put your table or query nam in the code, can you post the code that you have used?
     
  12. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    Here is the code i used.

    Private Sub Command0_Click()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tbl_Mailing, NTSImport, True
    End Sub
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Put Inverted commas around the names
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,"tbl_Mailing", "NTSImport", True
     
  14. karlhaywood

    karlhaywood Account Closed Thread Starter

    Joined:
    Jan 17, 2010
    Messages:
    680
    i have put that code in and i get no error message but i have checked the excel shhet an there is nothing there.

    Why is this?
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Does it create the sheet?



    Do you like Guitar Music?
     
  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!

Loading...
Thread Status:
Not open for further replies.

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

  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