Access VBA - Export to Excel without saving with field name

[scotton1982]

Hi All,

I am really struggling with exporting a query into excel without saving. I am currently using the below code which exports the query into Excel without issue, the only issue I have is there is no field name/column heading exported across.

The below code does not include my many attempts at this, but gives you an idea of what I am trying to do:

Dim dbs As DAO.Database
Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object


Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook


objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "Photo Austria"

Set rstGetRecordSet = dbs.OpenRecordset("y_AT_02")

objActiveWkb.Worksheets("Photo Austria").Cells(2, 1).CopyFromRecordset rstGetRecordSet
objActiveWkb.Worksheets("Photo Austria").Columns("A:Z").EntireColumn.AutoFit


Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing

 

[Pat Hartman]

TransferSpreadsheet does this with a single line of code and it has an option to export column names. If you do the export manually, you will also need to export the column headers manually.

 

[Trevor G]

Hi All,

I am really struggling with exporting a query into excel without saving. I am currently using the below code which exports the query into Excel without issue, the only issue I have is there is no field name/column heading exported across.

The below code does not include my many attempts at this, but gives you an idea of what I am trying to do:

Dim dbs As DAO.Database
Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object


Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook


objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "Photo Austria"

Set rstGetRecordSet = dbs.OpenRecordset("y_AT_02")

objActiveWkb.Worksheets("Photo Austria").Cells(2, 1).CopyFromRecordset rstGetRecordSet
objActiveWkb.Worksheets("Photo Austria").Columns("A:Z").EntireColumn.AutoFit


Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing

Welcome to the forum, the Docmd.TransferSpreadSheet is a good idea. Also check out this thread it will give you the code you need.

http://www.access-programmers.co.uk/forums/showpost.php?p=1173201&postcount=2

 

[boblarson]

The code I use does add the names so you might want to look there to see how it is done. I have a few different functions depending on what you want to do.
http://www.btabdevelopment.com/ts/default.aspx?PageId=10


TransferSpreadsheet has its uses but if you want to format anything you would have to reopen it using code and then do the formatting. In the code I have referenced, it does it all in one shot.