Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    34

    Unanswered: Copying an Access table to an Excel file

    Okay,
    I know how to get data from an access table,
    How do I easily export it to an excel file?

    Thanks,
    Chen

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    1) Open your Access Database.

    2) Right-Click the name of the table that you want to export.

    3) Select Export in the menu that pops up.

    4) Change the file type to Excel and press Export.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Posts
    34
    I meant through Visual Basic :|

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    If you have Excel on your machine you can automate it i.e.

    Code:
    Dim lRowCounter As Long
    Dim lColCounter As Long
    Dim exlApp As Excel.Application
    Dim rs As ADODB.Recordset
    
        Set exlApp = New Excel.Application
        
        lRowCounter = 1
        blnFileCreated = True
        Do While lRowCounter <= rs.RecordCount
            lRowCounter = lRowCounter + 1
            For lColCounter = 1 To 32 'NUMBER OF FIELDS TO EXPORT
                 exlApp.Cells(lRowCounter, lColCounter).Value=rs.Fields(lColCounter)
            Next lColCounter
        Loop
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  5. #5
    Join Date
    Mar 2004
    Posts
    34
    Thank you very much.

    Let's say I don't have Excel (I can't count on that), is there any way to do it anyway? ...

    mm.. funny anyway sentence.

  6. #6
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Use DoCmd as follows:

    DoCmd.OutputTo acQuery, "qry_month", "MicrosoftExcel(*.xls)", "D:\Recertifications.xls"
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  7. #7
    Join Date
    Mar 2004
    Posts
    34
    I am unfamilier with DoCmd and copying that line doesn't seem to work (though that was probably dumb of me)

  8. #8
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    You must replace the values to the ones appropriate to your database. Do a search in the Access help for "OutPutTo" command. In the meantime here is a summary of the command:

    OutputTo Action
    You can use the OutputTo action to output the data in the specified Microsoft Access database object (a datasheet, form, report, module, data access page) to several output formats.

    Settings
    The OutputTo action has the following arguments.

    Object Type The type of object containing the data to output. Click Table (for a table datasheet), Query (for a query datasheet), Form (for a form or form datasheet), Report, Module, Data Access Page, Server View, Stored Procedure, or Function in the Object Type box in the Action Arguments section of the Macro window. You can't output a macro. If you want to output the active object, select its type with this argument, but leave the Object Name argument blank. This is a required argument. The default is Table.

    Object Name The name of the object containing the data to output. The Object Name box shows all objects in the database of the type selected by the Object Type argument.
    If you run a macro containing the OutputTo action in a library database, Access looks for the object with this name first in the library database, then in the current database.

    Output Format The type of format you want used to output the data. You can click HTML (*.htm; *.html), Text Files (*.txt), Microsoft Active Server Pages (*.asp), Microsoft Excel (*.xls), Microsoft Excel 5-7 (*.xls), Microsoft Excel 97-10 (*.xls), Microsoft IIS (*.htx, *.idc), Rich Text Format (*.rtf), Data Access Page (*.htm; *.html) , or XML (*.xml) in the box. Modules can be output only to MS-DOS text format. Data access pages can only be output in HTML format. Only forms and reports can be output to data access pages. Microsoft Internet Information Server and Microsoft Active Server formats are available only for tables, queries, and forms. If you leave this argument blank, Access prompts you for the output format.
    Output File The file you want to output the data to, including the full path. You can include the standard file name extension (.asp, .htm or .html, .htx, .xls, .txt, .rtf, or .xml) for the output format you select with the Output Format argument, but it's not required. If you output to Internet Information Server or Active Server files, Access will always create files with the standard .htx and .idc or .asp file name extensions. If you output data access pages, Access will always create files with .html file name extensions. If you leave the Output File argument blank, Access prompts you for an output file name.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  9. #9
    Join Date
    Mar 2004
    Posts
    34
    You saying it will work from pure VB ?

Posting Permissions

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