Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
presenting data on multiple sheet on one consolidated sheet | Excel Worksheet Functions | |||
Presenting Excel charts | Excel Discussion (Misc queries) | |||
Calculating repetative data & presenting it via a (Venn)diagram | Excel Discussion (Misc queries) | |||
Help with presenting data in å chart | Charts and Charting in Excel | |||
presenting lots of data in a chart & table | Charts and Charting in Excel |