Export Access table to Excel

[AT76]

Hi,

I'm trying to open a specific table in my access app and export/copy the table into excel.

Is there a simple way of doing this? I looked thru the archives but could not find an example.

Thank you.

 

[SkipVought]

Hi,

Why export an entire table? You could easily set up a QUERY from Excel directly to your Access table for the subset of data that the user would need, using Data/Get External Data/New Database Query -- Access files -- YOUR DATABASE -- YOUR TABLE.....

One it's set up in Excel, all you need a Data/Refresh to get up-to-date data. Could even get new data on workbook open. Check out Data/Get External Data/Data Range Properties.

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[AT76]

Thanks Skip,

I'm not sure I understood what you meant. What I'm trying to do is programatically open a table in access by:

dbCurrent.OpenRecordset ("tableName")

and export or copy it to an excel spreadsheet.

Thanks in adavance!

AT

 

[jadams0173]

I think what SkipVought is saying, is let Excel query your access table to get the data that you need....unless you want the entire table.

 

[AT76]

I understand what you mean. I do want the entire table except the last 2 columns.

Could you get me started on how to how to do this? I'm doing this behing a button in access.

Thank you lots!

 

[peacesine]

Here's a start on exporting a table to excel:

Private Sub Command0_Click()
DoCmd.OutputTo acOutputTable, "NameOfTable", acFormatXLS, "NameOfTable2.xls", True
End Sub

I found it in Visual Basic Help, did a search for "OutputTo Method"

 

[PHV]

You may also consider the DoCmd.TransferSpreadsheet method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Unless your data is static...

unless your table is relatively small

exporting an entire table does not make much sense to me.

It seems to me that you'd want CURRENT data in such chunks (according to some criteria) that would be useful for the immediate need.

That's what a QUERY is for, is it not?

So why not QUERY the data from Excel that you need, when you need it, in order to get the most current data and only the amount that is really required instead of the universe???

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[AT76]

Thank you peacesine... I will look more into the VB Help. Appreciate your help!

 

[AT76]

I tried it using: DoCmd.OutputTo acOutputTable, it works great. How do I access this file in the code as to manipulate its contents (Delete some columns) and save the table to a specific directory. Is there a command I can use to access this excel sheet that was just created? Thank you.

 

[AT76]

SkipVought thank you for your input. But unfortunately I'm still confused. Peacesine's method worked OK because the table I'm dealing with is small. Now I'm trying to manipulate the created xls sheet. Any recommendations?

Thank you!

 

[AT76]

I figured it out! Here's the code:

DoCmd.OutputTo acOutputTable, "ICRs", acFormatXLS, "Name.xls", False


Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlws As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\Documents and Settings\My Documents\Name.xls")
Set xlws = xlWB.ActiveSheet


Thanks to ALL!!!

 

[peacesine]

To export it and save it in a specific directory, you can also do:

DoCmd.OutputTo acOutputForm, "Name", acFormatXLS, "c:\Documents and Settings\My Documents\Name.xls", True

This should open Excel with the table displayed. After that, I'm not sure if you switch to an Excel macro to do the rest of the manipulation or what.