[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. :D
[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. :D
[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 :D

[RobDog888]

Yes, but then you get the dialog popup.

Booooo! *SLAP* :p

[Shuja Ali]

Yes, but then you get the dialog popup.

Booooo! *SLAP* :p
But then you have the liberty of saving the file anywhere in your PC (even on your network share) :D

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 :p ;) :)

[Shuja Ali]

Lets do it then http://www.smileypad.com/v10/Cache/Angry/PutEmUp.gif

Your are n MVP so you will be better. :thumb:

[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
:lol: HA HA Great smilie :D

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. :p

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_data_from_Excel_to_Access_(DAO)_using_VBA_in_Microsoft_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. :rolleyes:

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. :sick:

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.

[yanty]

I state both as Adodc1
But the field still has all 14 fields
And new problem is if we insert new data into the database, it overwites part of the previous record. It does not move to next row for new entry.
Actually everything worked out just fine at Post #32, but to hide other fields is the problem.


Private Sub cmdData_Click()
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 & "\CarReg.mdb;Persist Security Info=False"
Adodc1.Recordset.Open

'Create your recordset
Adodc1.RecordSource = "SELECT Custid, CustName FROM ADC"

'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 Adodc1.Recordset.Fields.Count - 1
oWB.Sheets(1).Cells(1, i + 1).Value = Adodc1.Recordset.Fields(i).Name
Next
oWB.Sheets(1).Range("1:1").Font.Bold = True
oWB.Sheets(1).Cells(2, 1).CopyFromRecordset Data:=Adodc1.Recordset, MaxColumns:=2
Adodc1.Recordset.Close
Set oWB = Nothing
Set oApp = Nothing
End Sub

[RobDog888]

Yes, but its probably because your reopening th adodc recordset and complicating the issue. You need to use only one rs and not reopen the same one twoice or use two separate ones.

Isnt the data to be exported already in the grid from the adodc1 recordset?

[yanty]

Sorry for the late reply RobDogg. The problem is solved. Thank you very much u guys. :thumb:


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 Custid, CustCom, CustName FROM Table1 ", oCnn, adOpenKeyset, adCmdText
'Now you will have only 2 columns listed
'Add the field names as column headers (optional)

'Create an instance of Excel and add a new blank workbook
Set oApp = New Excel.Application
Set oWB = oApp.Workbooks.Add
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, , MaxColumns:=3
oRs.Close
Set oRs = Nothing
oCnn.Close
Set oCnn = Nothing
Set oWB = Nothing
Set oApp = Nothing
End Sub