How do I export data from Microsoft Access to Microsoft Excel?
Both are Version 2002. Thank you.
Printable View
How do I export data from Microsoft Access to Microsoft Excel?
Both are Version 2002. Thank you.
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?
Yes I'm trying to do it with VBA and whole table too. I'm not sure about the other 2.
Set a reference to the Microsoft DAO Object Library by clicking "Tools" then "References".
Then edit the following.
Code: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
If you are going to run it from Access then just write this@mikeyc1204:Code:DoCmd.RunCommand acCmdExport
The above process will be slow and DAO is too Old now. Use of ADO is always preffered over DAO.
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?
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.
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 :DQuote:
Originally Posted by RobDog888
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) :DQuote:
Originally Posted by RobDog888
slap slap..
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 ;) :)
Lets do it then http://www.smileypad.com/v10/Cache/Angry/PutEmUp.gif
Your are n MVP so you will be better. :thumb:
In a module...
:lol: HA HA Great smilie :DCode: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
Thanks :)
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.
Does SP change anything?
SP ?
Service Pack
Office SP, considering this code, I highly doubt it. Windows SP, still unlikely.
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?
When your recording a macro just use your mouse to click the menu items that you would normally do to export it. :)
I dun have an existing Excel workbook so how do I change the file picker dialog?
Quick and simple example of a basic save as dialog.
Code: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
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
VB Code:
Did I miss something? Sorry I'm new in this.
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 ;)
You need a chat people :P
Lets cheat.. here's one I prepared earlier..
http://www.vbforums.com/showthread.php?t=352849
Look at my first post
RobDog888, I had saw one post which you helped and I tried writing it in my VB application.
VB Code:
May I know why does duplicate worksheets of Book1 appear? Eg: Book2, 3, 4 of the same data...
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.
Code: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
Great. Thank u so much. I hope I did the correct thing of exporting Microsoft Access to Excel dynamically.
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:
VB Code:
In your select query instead of writing * explicitly mention the Field Names that you want to export
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
I don't see why Excel should be doing this. Your recordset contains just 10 fields and it should export only 10 fields.
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.
VB Code:
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.
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.
This is the code i usually connect to my database.
VB Code:
I tried to change to Adodc and it gives me a headache. :sick:
VB Code:
Are you getting any errors?
Looks like your running tow recordsets. One adodc and one ors.
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.
VB Code:
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?
Sorry for the late reply RobDogg. The problem is solved. Thank you very much u guys. :thumb:
VB Code: