Copying an Access table to an Excel file

Wolverchenus · #1 (**permalink**) 06-28-04, 14:39

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

Thanks,
Chen

SCIROCCO · #2 (**permalink**) 06-29-04, 09:45

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.

Wolverchenus · #3 (**permalink**) 06-29-04, 10:35

I meant through Visual Basic :|

SCIROCCO · #4 (**permalink**) 06-30-04, 08:54

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

Wolverchenus · #5 (**permalink**) 06-30-04, 15:21

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.

SCIROCCO · #6 (**permalink**) 06-30-04, 16:33

Use DoCmd as follows:

DoCmd.OutputTo acQuery, "qry_month", "MicrosoftExcel(*.xls)", "D:\Recertifications.xls"

Wolverchenus · #7 (**permalink**) 06-30-04, 16:43

I am unfamilier with DoCmd and copying that line doesn't seem to work (though that was probably dumb of me)

SCIROCCO · #8 (**permalink**) 07-01-04, 08:54

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.

Wolverchenus · #9 (**permalink**) 07-04-04, 12:57

You saying it will work from pure VB ?

Internet Services

Web Development

Digital Marketing

Consumer Tech