Help adding code to export column headers to Excel

[Murdockj]

I have been looking for a while to export a crosstab query from Access to an existing Excel spreadsheet. The data in the query is very dynamic as is the column count.

I did find code on the net that will do exactly what I am looking to do. The only issue is that it does not transfer the column titles to the spreadsheet. I cannot post the code here as it is not allowed. I can post the link to the code however. The code I am using is the second code found on this page: http://access.mvps.org/access/modules/mdl0035.htm.

Would it be possible to get some help with adding the column headers to this code? I do not know what to change. If I can get this answered this code works perfect for me.

Thanks,
Murdockj

[twinnyfo]

Murdock,

When you export, before you begin exporting the actual data, use the following:

rstRecordsetName.Fields(1).Name

To add the field name to your Spreadsheet. Because you will have to determine how many Fields the query has, just use

Fields.Count

Then, because fields are numbered 0 - n, use a counter to go through all your Field Names This same method will be used for exporting your data to the spreadsheet also.

[Murdockj]

I am not sure where or how I am to put this in the code? Working on this for so long I am getting lost.

Here is the code to write to the spreadsheet:

  With objSht
      .Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
          intLastCol)).ClearContents
      .Range(.Cells(1, 1), _
        .Cells(1, rs.Fields.Count)).Font.Bold = True
      .Range("A2").CopyFromRecordset rs
 

How would I include this into the existing code?

Thanks,
Murdockj

[twinnyfo]

Are you using a fresh spreadsheet each time? If so, there is no need for lines 2-3.

However, if you want the headers (Field Names), then before you copy the recordset, you will want to determine the number of fields, and copy them over.

This code is NOT complete, but given so you understand the concept.

Dim intFields As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim I As Integer
 
Set db = CurrentDB()
Set rst = db.OpenRecordset("Your Query", dbOpenDynaset)
 
intFields = rst.Fields.Count
 
For I = 1 to intFields
    With objSht
        .Cells(1, I) = rst.Fields(I - 1).Name
    End With
Next I
 
'The rest of your code

I have not tested this, as I am writing it freehand, but it should get you pointed in the right direction.

Hope this hepps!