[RESOLVED] Export From Microsoft Access to Excel

[yanty]

How do I export data from Microsoft Access to Microsoft Excel?
Both are Version 2002. Thank you.

[mikeyc1204]

You could take your pick from about 10 ways. Are you trying to do it with VBA? Are you looking for a whole table? A built in query? Will the SQL statement be variable?

[yanty]

Yes I'm trying to do it with VBA and whole table too. I'm not sure about the other 2.

[mikeyc1204]

Set a reference to the Microsoft DAO Object Library by clicking "Tools" then "References".

Then edit the following.

Sub PasteTable()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Integer
    
    Set dbs = OpenDatabase("c:\mydatabase.mdb")
    Set rst = dbs.OpenRecordset("SELECT * FROM mytable;")
    
    If Not IsNull(rst.Fields(0)) Then
        For i = 0 To rst.Fields.Count - 1
            Cells(1, i + 1).Value = rst.Fields(i).name
        Next i
        Range("A2").CopyFromRecordset rst
    End If
    
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
End Sub

[Shuja Ali]

If you are going to run it from Access then just write this

DoCmd.RunCommand acCmdExport

@mikeyc1204:
The above process will be slow and DAO is too Old now. Use of ADO is always preffered over DAO.

[mikeyc1204]

I just assumed the process would be done in Excel. Not sure why :\

And I was under the impression that ADO is better in most cases, but DAO is faster for simple jet interaction. Wrong?

[Shuja Ali]

IF this process is going to be done in Excel then it is even more easier. This is how I would do it
1. Open a Excel File.
2. Select Record New Macro from Tools-->Macro Menu.
3. Once the recording has started, open Data Menu and select Import Data from Import External Data Menu.
4. Select the Database, and then select the table you want to import.
5. All other dialogs are self Explanatory.
6. Once you are done with this, stop the macro recording.
7. Press ALT + F11 and you will see that VBA has written all the code for you.

[RobDog888]

One line of code in an Access module. alot easier.
[Highlight=VB]Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "D:\Book1.xls", False, "Sheet1$"[/vbcoe]

[Shuja Ali]

One line of code in an Access module. alot easier.
[Highlight=VB]Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "D:\Book1.xls", False, "Sheet1$"[/vbcoe]

I had also shown a similar one line in my previous post

[RobDog888]

Yes, but then you get the dialog popup.

Booooo! *SLAP*

[Shuja Ali]

Yes, but then you get the dialog popup.

Booooo! *SLAP*

But then you have the liberty of saving the file anywhere in your PC (even on your network share)

slap slap..

[RobDog888]

But if you want to export the table or query results to a specific range in a specific sheet you cant do that with the runcommand. Plus you can always prompt the user for the export location/file if need be. The main plus is the ability to place the data in a particular sheet/cell.


Ps, SLAP SLAP SLAP

[Shuja Ali]

Lets do it then

Your are n MVP so you will be better.

[RobDog888]

In a module...

Private Sub ExportMeDynamically()
    Dim strPath As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .ButtonName = "Export As"
        .Filters.Add "Excel file types only (*.xls)", "*.xls", 1
        .FilterIndex = 1
        .InitialFileName = "Book1.xls"
        .InitialView = msoFileDialogViewDetails
        .Title = "RobDog888's Access to Excel Export™"
    End With
    If Application.FileDialog(msoFileDialogFilePicker).Show = -1 Then
        strPath = Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Item(1)
        Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", strPath, False, "Sheet2$"
    End If
End Sub

HA HA Great smilie

Thanks

[RobDog888]

Note: my code above is for Appending to an EXISTING workbook only. If you want it to work with new files only then you need to use a different file picker dialog.

[DakDarie]

Does SP change anything?

[RobDog888]

SP ?

[DakDarie]

Service Pack

[RobDog888]

Office SP, considering this code, I highly doubt it. Windows SP, still unlikely.

[yanty]

Wow, there are so many options that I duno which one to follow.

How do we open data menu after the recording has started?

[RobDog888]

When your recording a macro just use your mouse to click the menu items that you would normally do to export it.

[yanty]

I dun have an existing Excel workbook so how do I change the file picker dialog?

[RobDog888]

Quick and simple example of a basic save as dialog.

Dim oDLG As FileDialog
Dim sFile As String
Set oDLG = Application.FileDialog(msoFileDialogSaveAs)
oDLG.Show
If oDLG.SelectedItems.Count = 1 Then
    sFile = oDLG.SelectedItems.Item(1)
End If

[yanty]

This is what I have done. Can u check whether it is correct?

'from Shuja Ali
1. Open a Excel File.
2. Select Record New Macro from Tools-->Macro Menu.
3. Once the recording has started, open Data Menu and select Import Data from Import External Data Menu.
4. Select car.mdb
5. Stop the macro recording.
7. Press ALT + F11

In the Macro..module window i type

Private Sub ExportMeDynamically()
    Dim oDLG As FileDialog
Dim sFile As String
Set oDLG = Application.FileDialog(msoFileDialogSaveAs)
oDLG.Show
If oDLG.SelectedItems.Count = 1 Then
    sFile = oDLG.SelectedItems.Item(1)
End If
End Sub

Did I miss something? Sorry I'm new in this.

[RobDog888]

No, if you want to go the recording a macro route then all you need to do is record the macro doing your manual steps necessary to import the access data.
Then press Alt+F11 to view the generated code in the VBA IDE's Modules folder.

You were mixing two different proposed methods

[DakDarie]

You need a chat people :P

[dannymking]

Lets cheat.. here's one I prepared earlier..

http://www.vbforums.com/showthread.php?t=352849

Look at my first post

[yanty]

RobDog888, I had saw one post which you helped and I tried writing it in my VB application.

Private Sub cmdData_Click()
Dim oRs As adodb.Recordset
    Dim oCnn As adodb.Connection
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Dim i As Integer
    'Connect to your Access db
    Set oCnn = New adodb.Connection
    oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    App.Path & "\Car.mdb;Persist Security Info=False"
    oCnn.Open
 
    'Create your recordset
    Set oRs = New adodb.Recordset
    oRs.Open "SELECT * FROM Table1;", oCnn, adOpenKeyset, adCmdText
 
    'Create an instance of Excel and add a new blank workbook
    Set oApp = New Excel.Application
    oApp.Workbooks.Open (App.Path & "\Book1.xls")
    oApp.Visible = True
    Set oWB = oApp.Workbooks.Add
 
    'Add the field names as column headers (optional)
    For i = 0 To oRs.Fields.Count - 1
        oWB.Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
    Next
    oWB.Sheets(1).Range("1:1").Font.Bold = True
    oWB.Sheets(1).Cells(2, 1).CopyFromRecordset oRs
    oRs.Close
    Set oRs = Nothing
    oCnn.Close
    Set oCnn = Nothing
    Set oWB = Nothing
    Set oApp = Nothing
End Sub

May I know why does duplicate worksheets of Book1 appear? Eg: Book2, 3, 4 of the same data...

[RobDog888]

Not sure what context the code was concerning for the thread but its adding a new workbook with this line. If you dont need/want it then use the ammedments below.

oApp.Workbooks.Open (App.Path & "\Book1.xls")
'Should be
Set oWB = oApp.Workbooks.Open (App.Path & "\Book1.xls")

'Take this line out.
'Set oWB = oApp.Workbooks.Add

[yanty]

Great. Thank u so much. I hope I did the correct thing of exporting Microsoft Access to Excel dynamically.

[Radjesh Klauke]

http://www.exceltip.com/st/Export_da...Excel/426.html

http://databases.about.com/cs/tutorials/a/aa033003a.htm

[yanty]

Thanks Radjesh for the tips but I tink I just stick to RobDog888's since it worked. One question though.
After using the code below, I managed to export the whole table from Access to Excel. How do I specify that I want to export eg 10 out of 14 fields from Access to Excel?
Meaning though my table has 14 fields, I want to show only the first 10 fields in Excel.
I tried to use oApp.Columns = 10 but instead all the Excel cells has digit 10 in it.

Private Sub cmdData_Click()
Dim oRs As adodb.Recordset
    Dim oCnn As adodb.Connection
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Dim i As Integer
    'Connect to your Access db
    Set oCnn = New adodb.Connection
    oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    App.Path & "\Car.mdb;Persist Security Info=False"
    oCnn.Open
 
    'Create your recordset
    Set oRs = New adodb.Recordset
    oRs.Open "SELECT * FROM Table1;", oCnn, adOpenKeyset, adCmdText
 
    'Create an instance of Excel and add a new blank workbook
    Set oApp = New Excel.Application
    Set oWB = oApp.Workbooks.Open (App.Path & "\Book1.xls")
    oApp.Visible = True
    
    'Add the field names as column headers (optional)
    For i = 0 To oRs.Fields.Count - 1
        oWB.Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
    Next
    oWB.Sheets(1).Range("1:1").Font.Bold = True
    oWB.Sheets(1).Cells(2, 1).CopyFromRecordset oRs
    oRs.Close
    Set oRs = Nothing
    oCnn.Close
    Set oCnn = Nothing
    Set oWB = Nothing
    Set oApp = Nothing
End Sub

[Shuja Ali]

In your select query instead of writing * explicitly mention the Field Names that you want to export

[yanty]

Like this? But Excel still shows all the fields in my table
oRs.Open "SELECT CustId,CustCom,CustName,ComCertNo,CustAddr,CustOPhone,CustMobile,CustFax,CustEmail,CustRemark FROM Table1 ", oCnn, adOpenKeyset, adCmdText

[Shuja Ali]

I don't see why Excel should be doing this. Your recordset contains just 10 fields and it should export only 10 fields.

[RobDog888]

This is an easy one, my code contains a loop where its listing out the columns in the sql query. So if your adding the field names to the select list then your loop will only contain that number of fields. You have 10 fields listed in your sql statement so that is how many columns your going to have.

The MaxColumns argument is another way to limit the number of columns copied from the recordset.

oRs.Open "SELECT CustId, CustName FROM Table1 ", oCnn, adOpenKeyset, adCmdText
    'Now you will have only 2 columns listed
    'Add the field names as column headers (optional)
    For i = 0 To oRs.Fields.Count - 1
        oWB.Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
    Next
    oWB.Sheets(1).Range("1:1").Font.Bold = True
    oWB.Sheets(1).Cells(2, 1).CopyFromRecordset Data:=oRs, ,MaxColumns:=2

[yanty]

Sorry but I still can't get it to show me the specified no. of fields. I've been using Adodc1 as ADO Data Control. Is this the case cos I didn't define it in the code.
The reason I've to show lesser fields is becos, my MSHFlexgrid only shows 10 of the fields. I kind of want to show what is on the MSHFlexgrid, is displayed in Excel.

[RobDog888]

Can you post your code? I dont think it may be the same if using the ADODC Data control. Not 100% sure as I havent tried it with it but it is an ADO control so lets see what we can do.

[yanty]

This is the code i usually connect to my database.

Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\Car.mdb;Persist Security Info=False"
 
Adodc1.RecordSource = "SELECT * FROM Table1 "

I tried to change to Adodc and it gives me a headache.

Private Sub cmdData_Click()
Dim oRs As adodb.Recordset
    Dim oApp As Excel.Application
    Dim oWB As Excel.Workbook
    Dim i As Integer
    'Connect to your Access db
    'Set oCnn = New adodb.Connection
    Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    App.Path & "\Car.mdb;Persist Security Info=False"
    Adodc1.Recordset.Open
 
    'Create your recordset
    Set oRs = Adodc1.Recordset
    oRs.Open "SELECT Custid, CustName FROM Table1;", Adodc1, adOpenKeyset, adCmdText
 
    'Create an instance of Excel and add a new blank workbook
    Set oApp = New Excel.Application
    Set oWB = oApp.Workbooks.Open(App.Path & "\Book1.xls")
    oApp.Visible = True
        'Add the field names as column headers (optional)
    For i = 0 To oRs.Fields.Count - 1
        oWB.Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
    Next
    oWB.Sheets(1).Range("1:1").Font.Bold = True
    oWB.Sheets(1).Cells(2, 1).CopyFromRecordset Data:=oRs, MaxColumns:=2
    oRs.Close
    Set oRs = Nothing
    Adodc1.Recordset.Close
    'Set oCnn = Nothing
    Set oWB = Nothing
    Set oApp = Nothing
End Sub

[RobDog888]

Are you getting any errors?
Looks like your running tow recordsets. One adodc and one ors.