Exporting report with dlookups in heading

[rabravenec1]

I am using VBA to export an Access Report which uses dlookups in the heading to set a value to an actually month name.

This dlookup values work good when using the following.
DoCmd.OutputTo acOutputReport, "rpt_DivisionDoc_LTM_Coll", acFormatPDF, strFileName, False

However, when I attempt to take the same report and export it to access using
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_DivisionDoc_Test_FY_Coll_LTM", strFileName3, False

The dlookup values do not transfer to the spreadsheet.

I've tried different methods to export this report and have it transfer the heading values retrieved by the dlookups in the reports and find
that it does work fine with the
DoCmd.OutputTo acOutputReport, "Rpt_DivisionDoc_LTM_Coll", acFormatTXT, strFileName4, False string.

But, I need the report in Excel with the actual dlookup values.

Please help... users driving me crazy...

 

[ranman256]

put the headings in the query.
if you use a form , make textbox that has the dlookup (or other method)
the query get this off the form,
the query exports with the data.

 

[rabravenec1]

put the headings in the query.
if you use a form , make textbox that has the dlookup (or other method)
the query get this off the form,
the query exports with the data.

Thank you Ranman256

In theory, this makes perfect since. However, this database is one that I was given control over last year. There are over 300 reports and queries. This will require way too much modifications to the reports and the queries, that I am not comfortable with doing.

Richard

 

[welshgasman]

Well if you are exporting just the query, what do you expect?, as the other data is created in your report.?

Have you tried exporting the report as Excel format.?

Export access 2010 report to excel 2010 xlsx

 

[JonXL]

Instead of updating the query already there, you could create a new query that selects from the query underlying your report and then add the lookup field to it. Then you can use TransferSpreadsheet to export the new query while leaving the report's record source untouched.