P
PK
OK guys, this is a tough one…. 
I need to export a report to excel, and dynamically change the field header
to reflect the current month and next month.
I have no problem doing this in a report, and when exporting to Word (RTF)
it would work fine as well. The difficulty I am having is exporting to Excel.
Here is a way that I can show you what I want to do (simplified code and
report for this conversation):
A simple report – one field from a table (fieldname is “Dataâ€)
In the report, I have a label positioned in the Page Header) named
“MyTextBox†(this is what we want to change dynamically from code)
I change the caption dynamically in the Print Event for the report:
Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
MyTextBox.Caption = "This is the text I want to show"
End Sub
OK – Here is the problem:
When I export to Excel, I get a column with the fieldname of “Dataâ€, and the
data below it. I want the fieldname to be "This is the text I want to show".
(Keep in mind that when I view this report in Access, or export to Word
(RTF) everything works perfectly.)
ANOTHER WAY I tried to do this (and it WAS successful) was to create a
“template" report, copy it to a temporary report, rename the Data Field (from
code), and export the report. The problem is that I can not use it because I
am creating an MDE, and this would involve modifying a report in an MDE which
can not be done:
DETAILS:
1) I create a report ("Template Report") with only the “Data†field (no
headers, no label).
2) From a form, I perform the following code:
On Error Resume Next
DoCmd.DeleteObject acReport, "New Report"
On Error GoTo 0
DoCmd.CopyObject , "New Report", acReport, "Template Report"
DoCmd.OpenReport "New Report", acViewDesign
[Reports]![New Report].Data.Name = "This is the text I want to show "
DoCmd.Close acReport, "New Report", acSaveYes
DoCmd.OutputTo acOutputReport, "New Report", acSpreadsheetTypeExcel9, ,
True
Once again – this works, but I cant use it because I eventually want to
create an MDE, which will not allow modifying a report.
TIA for any help you can provide!!!
I need to export a report to excel, and dynamically change the field header
to reflect the current month and next month.
I have no problem doing this in a report, and when exporting to Word (RTF)
it would work fine as well. The difficulty I am having is exporting to Excel.
Here is a way that I can show you what I want to do (simplified code and
report for this conversation):
A simple report – one field from a table (fieldname is “Dataâ€)
In the report, I have a label positioned in the Page Header) named
“MyTextBox†(this is what we want to change dynamically from code)
I change the caption dynamically in the Print Event for the report:
Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
MyTextBox.Caption = "This is the text I want to show"
End Sub
OK – Here is the problem:
When I export to Excel, I get a column with the fieldname of “Dataâ€, and the
data below it. I want the fieldname to be "This is the text I want to show".
(Keep in mind that when I view this report in Access, or export to Word
(RTF) everything works perfectly.)
ANOTHER WAY I tried to do this (and it WAS successful) was to create a
“template" report, copy it to a temporary report, rename the Data Field (from
code), and export the report. The problem is that I can not use it because I
am creating an MDE, and this would involve modifying a report in an MDE which
can not be done:
DETAILS:
1) I create a report ("Template Report") with only the “Data†field (no
headers, no label).
2) From a form, I perform the following code:
On Error Resume Next
DoCmd.DeleteObject acReport, "New Report"
On Error GoTo 0
DoCmd.CopyObject , "New Report", acReport, "Template Report"
DoCmd.OpenReport "New Report", acViewDesign
[Reports]![New Report].Data.Name = "This is the text I want to show "
DoCmd.Close acReport, "New Report", acSaveYes
DoCmd.OutputTo acOutputReport, "New Report", acSpreadsheetTypeExcel9, ,
True
Once again – this works, but I cant use it because I eventually want to
create an MDE, which will not allow modifying a report.
TIA for any help you can provide!!!