Export From Access to Excel

Question

I want to export some data in one recordset in Access to Excel.

I know the DoCmd.TransferSpreadsheet command but it works only with stored queries, and in my case it's a runtime-filtered recordset.

I've tried some codes to do what I want. I can get the data exported but I cannot get the column name from the recordset.

Any suggestion on the commands or how to get those column names from recordset?

Jacob · Accepted Answer · 2011-07-13 06:55:27Z

3

DAO recordsets have a name property you can use.

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM ARTIKELGRUPPE")
Debug.Print rs.Fields(0).Name
Debug.Print rs.Fields(1).Name

Output for my table:

id
Name

answered Jul 13, 2011 at 6:55

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christian Specht Over a year ago

You can do exactly the same with ADO recordsets, they have the ´name´ property as well.

ChrisPadgham · Accepted Answer · 2012-01-16 21:39:38Z

1

You can alter a stored query prior to calling transfer spreadsheet

Dim myQuery As QueryDef
Set myQuery = CurrentDb.QueryDefs("SampleQuery")
myQuery.SQL = "SELECT * FROM myTable WHERE something"
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "SampleQuery", "c:\test.xls"

answered Jan 16, 2012 at 21:39

ChrisPadgham

8705 silver badges4 bronze badges

Collectives™ on Stack Overflow

Export From Access to Excel

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related