Export Report Column HEADERS to Excel

[Diakonos1984]

I have several goals in this project/question and I believe to achieve them I have to use a combination of Access and Excel. I sort of know what I'm doing with Excel VBA but Access is giving me fits.

I have 30+ different reports in Access I need to be able to produce in one document with a table of contents.

The main reasons I am using Access in this project are:
1) the ability to name the headers of a report differently from the actual field names. My field names are abbreviated database junk, but I need to produce reports with nice complete phrases in the headers. I am happy to write code to control the export, but I don't want to write code to manually over-write each header name.
2) the ability to define column width and have it stay stable no matter what data is in it.

The reason I can't just print directly from Access is that I already have Excel macros for making tables of contents, and my research indicates that a complete table of contents for multiple reports is prohibitively difficult in Access.

The options I know of to export from Access to Excel are:
DoCmd.OutputTo
and
DoCmd.TransferSpreadsheet

It appears neither one of them will give me what I want. TransferSpreadsheet doesn't work for reports (?) and OutputTo looses my nice clean headers and just exports the junk field names. I ran across a forum the other day where someone admitted that we need something else, and wrapped Excel VBA inside their Access module and I think maybe used a loop to read header names directly and replace them in Excel? Unfortunately I can't find that thread now...

Do any of you know how to get the header names out of an Access report and into Excel? I can't afford to manually code each header/field substitution.

I don't care if the method ends up writing a different Excel file for each report--I've got code that will combine multiple Excel files into one so I can use my table of contents macro.

Thanks!

[SamT]

Diakonons.

I seem to be operating on only 3 cylinders today, so the following code block isn't code, just some ideas. Since you seem to be pretty sharp, is might be enough.

In Excel, probably in ThisWorkbook, maybe in Standard Module.


Function ColumnHeads(Operation As String) As Boolean

Type CHeads
Name As STring
Wide As Long
End Type

Static ColHeads As Collection
Dim Cel As CHead

On Error Goto Fail
IF ColHeads =Is Nothing and OPeration is Put then gotoFail

If LCase(Operation) = "get" Then
With Range(Rows(1))
For i = 1 to Cells(1 ,Columns.Count).End(xlToRight).Column
Cel.Name = Cells(1).Text
Cel.Wide = Cells(1).Column.Width
ColHeads.Add Cel
Next i
End With
ColumnHeads = True
Exit Funtcion

ElseIF Operation = "Put" Then
Put values from ColHeads into columns
Set ColHeads= Nothing
ColumnHeads = True
Exit Function
End If

Fail:
If Operation = Get Then Set ColHeads = Nothing
ColumnHeads = False
End Function

[mrojas]

This might be way in left field.

Create a table in Access to store the name of each report and its title, and any other pertinent report information.
When a report is run, using a search function, retrieve from the table its corresponding report title.
I would think that you can connect to your Access database from Excel, create a recordset and use this as your report title source