Excel for presenting Access Data
 

I have developed a database that produces a report made up of many subreports.
In most cases the data is unrelated, but each subreport relates to the date
and location on the main report. Is there any way to import this date into
excel formatted the same way it is in the Access reports. When I export the
report to excel using the "Analyze in MS Excel" it does not keep the data in
the same format.
I cannot think of anything but creating a huge macro to import all the data.
Can that work dynamically or do ranges have to be pre set?

Excel for presenting Access Data
 

Joe,
To me the point is, do you want to present it or analyse the data ?
Presenting (and I assume printing) the report is done in Access.
Analysing requires the data not the formatting, so it's not surprising that
"using the Analyze in MS Excel it does not keep the data in the same
format".

One possibility may be to save the Access report as HTML, then in Excel
DataNew Web Query, with full HTML formatting.

NickHK

"JoeA2006" wrote in message
...
I have developed a database that produces a report made up of many
subreports.
In most cases the data is unrelated, but each subreport relates to the
date
and location on the main report. Is there any way to import this date
into
excel formatted the same way it is in the Access reports. When I export
the
report to excel using the "Analyze in MS Excel" it does not keep the data
in
the same format.
I cannot think of anything but creating a huge macro to import all the
data.
Can that work dynamically or do ranges have to be pre set?

Excel for presenting Access Data
 

Nick,
Your suggestion almost does what I need. Users want to be able to work with
the data in a spread sheet the way I have it formatted in the Access
report(for analysis). The procedure I followed was

1. I ran the report in print preview
2 File Export .. I selected HTML Pages
3. In Access 2003 You can either select HTML Template or other options. I
selected
Default encoding.
3 Click on the export button
4 When I opened the HTML an Excel Icon showed up on the browser toolbar.
That opened up a spread sheet with the data. Some of the data is in shifted
into the wrong columns an the headers and subtotals dont all line up

I guess the question is what is HTML seeing that is causing the formatting
to go awry.
Is there a better procedure ? Do I need to change the layout of the access
report?
Anyway. I am hoping we are on the right track.

"NickHK" wrote:

Joe,
To me the point is, do you want to present it or analyse the data ?
Presenting (and I assume printing) the report is done in Access.
Analysing requires the data not the formatting, so it's not surprising that
"using the Analyze in MS Excel it does not keep the data in the same
format".

One possibility may be to save the Access report as HTML, then in Excel
DataNew Web Query, with full HTML formatting.

NickHK

"JoeA2006" wrote in message
...
I have developed a database that produces a report made up of many
subreports.
In most cases the data is unrelated, but each subreport relates to the
date
and location on the main report. Is there any way to import this date
into
excel formatted the same way it is in the Access reports. When I export
the
report to excel using the "Analyze in MS Excel" it does not keep the data
in
the same format.
I cannot think of anything but creating a huge macro to import all the
data.
Can that work dynamically or do ranges have to be pre set?

Excel for presenting Access Data
 

If you have output in Excel that is vaguely similar I think you should feel
satisfied.
You are going from Access reportHTMLExcel, all of which use separate
concepts for their display, so some changes are to be expected.

If you really need exactly the same layout and look, forget the Access
report and query the database from Excel, making your report in Excel also.
Then you do not need the HTML step and you users get the same layout to do
their analysis.

Otherwise forget the look and use the data in Excel. For analysis, they will
probably have to add columns/rows/formulae to check numbers etc. This throws
the layout out the window anyway.
It they are not going to add these to Excel, then there's no point it being
in Excel in the place as a paper print out would serve the same purpose.

NickHK

"JoeA2006" wrote in message
...
Nick,
Your suggestion almost does what I need. Users want to be able to work
with
the data in a spread sheet the way I have it formatted in the Access
report(for analysis). The procedure I followed was

1. I ran the report in print preview
2 File Export .. I selected HTML Pages
3. In Access 2003 You can either select HTML Template or other options. I
selected
Default encoding.
3 Click on the export button
4 When I opened the HTML an Excel Icon showed up on the browser toolbar.
That opened up a spread sheet with the data. Some of the data is in
shifted
into the wrong columns an the headers and subtotals dont all line up

I guess the question is what is HTML seeing that is causing the formatting
to go awry.
Is there a better procedure ? Do I need to change the layout of the access
report?
Anyway. I am hoping we are on the right track.

"NickHK" wrote:

Joe,
To me the point is, do you want to present it or analyse the data ?
Presenting (and I assume printing) the report is done in Access.
Analysing requires the data not the formatting, so it's not surprising
that
"using the Analyze in MS Excel it does not keep the data in the same
format".

One possibility may be to save the Access report as HTML, then in Excel
DataNew Web Query, with full HTML formatting.

NickHK

"JoeA2006" wrote in message
...
I have developed a database that produces a report made up of many
subreports.
In most cases the data is unrelated, but each subreport relates to the
date
and location on the main report. Is there any way to import this date
into
excel formatted the same way it is in the Access reports. When I
export
the
report to excel using the "Analyze in MS Excel" it does not keep the
data
in
the same format.
I cannot think of anything but creating a huge macro to import all the
data.
Can that work dynamically or do ranges have to be pre set?