I am working on a project converting a "spreadsheet application" to a database solution. A macro was written that takes screen shots of each page and pastes them into a PowerPoint presentation. Because of the nice formatting options in Excel, the presentation looks very pretty.
我正在开发一个将“电子表格应用程序”转换为数据库解决方案的项目。编写了一个宏,用于截取每个页面的屏幕截图并将其粘贴到PowerPoint演示文稿中。由于Excel中很好的格式化选项,演示文稿看起来非常漂亮。
The problem I'm having is that I haven't ever seen an Access report that would be pretty enough to display to upper management. I think the output still has to be a PowerPoint presentation. It needs to look as close as possible to the original output.
我遇到的问题是我从未见过一个足以向上层管理人员展示的Access报告。我认为输出仍然必须是PowerPoint演示文稿。它需要看起来尽可能接近原始输出。
I am currently trying to write some code to use a .pot (presentation template) and fill in the data programmatically. Putting the data into a PowerPoint table has been tricky because the tables are not easy to manipulate. For example, if a particular description is too long, I need to break into the next cell down (word-wrap isn't allowed because I can only have n lines per page).
我目前正在尝试编写一些代码来使用.pot(演示模板)并以编程方式填写数据。将数据放入PowerPoint表格非常棘手,因为表格不易操作。例如,如果特定描述太长,我需要进入下一个单元格(不允许自动换行,因为每页只能有n行)。
Is there a way to make an Access report pretty, am I headed down the right path, or should I just try to programmatically fill in the Excel spreadsheet and use the code that already exists there to produce the presentation? (I'd still need to figure out how to know when to break a line when using a non-monospaced font, as the users are currently doing that manually when they enter the data in the spreadsheet)
有没有办法使Access报告漂亮,我是走正确的道路,还是应该尝试以编程方式填写Excel电子表格并使用已存在的代码来生成演示文稿? (我仍然需要弄清楚如何在使用非等宽字体时知道何时断线,因为用户在电子表格中输入数据时当前正在手动执行此操作)
Jason Z: If I set it to wrap, and I already have n lines, it would make n+1 or 2 lines on the slide, which is unacceptable.
杰森Z:如果我把它设置为包裹,并且我已经有n行,它会在幻灯片上产生n + 1或2行,这是不可接受的。
Dennis: That article looks very good, I should be able to glean something from it. Thanks!
丹尼斯:那篇文章看起来非常好,我应该可以从中收集一些东西。谢谢!
10 个解决方案
#1
3
Access has the capability to create downright beautiful reports. The problem is that it can't make a spreadsheet look better than Excel. You have to know when to use each tool.
Access具有创建完美漂亮报告的功能。问题是它无法使电子表格看起来比Excel更好。您必须知道何时使用每个工具。
Use Excel when you have spreadsheet-like formatting, need a lot of boxes and lines, or want to draw charts.
如果您有类似电子表格的格式,需要大量的框和行,或者想要绘制图表,请使用Excel。
Use Access when you will output a report as a PDF. It's very useful for one-record-per-page detail reports, formatting where you need to position things very precisely, and where you need to embed subreports with related or unrelated data.
当您将报告输出为PDF时,请使用Access。它对于每页一个记录的详细报告非常有用,格式化您需要非常精确地定位的位置,以及您需要在何处嵌入具有相关或不相关数据的子报表。
Think about the reports that would be nasty in Excel because you'd have to merge cells all over the place and do funny things with the placement and the layout would never work. That's where Access shines.
想想在Excel中讨厌的报告,因为你必须在整个地方合并单元格,并对展示位置做有趣的事情,布局永远不会起作用。这就是Access闪耀的地方。
#2
3
Joel, (your co-host here) did a thing about using access reports for shipping labels a few years back... maybe this could be a inspriation for you?
Joel,(你在这里的共同主持人)做了一件关于几年前使用访问报告来运送标签的事情......也许这可能会激励你?
http://www.joelonsoftware.com/articles/HowToShipAnything.html
#3
2
I have implemented Access reports which were 'pretty' enough. The downside is that it takes a lot of time and effort, and trial and error to produce the desired output.
我已经实现了“非常”足够的Access报告。缺点是需要花费大量的时间和精力,以及产生所需输出的反复试验。
You can definitely get there, but it requires the patience of a saint.
你绝对可以到达那里,但它需要一个圣人的耐心。
#4
1
I guess it depends on what you mean by pretty. For example, I do not find it particularly difficult to produce say, reasonable graphs and tables with alternate line shading in Access. It is also possible to use MS Word and fill in bookmarks, or mail merge. If the present system uses VBA to create the PowerPoint presentation, perhaps much of it could be transferred to Access? Microsoft have an article on Access to Powerpoint: http://msdn.microsoft.com/en-us/library/aa159920(office.11).aspx
我想这取决于你的意思。例如,我发现在Access中生成具有备用线着色的说明,合理的图形和表格并不是特别困难。也可以使用MS Word并填写书签或邮件合并。如果本系统使用VBA创建PowerPoint演示文稿,可能其中大部分可以转移到Access?微软有一篇关于Access to Powerpoint的文章:http://msdn.microsoft.com/en-us/library/aa159920(office.11).aspx
Finally, it is not impossible to build HTML output from Access.
最后,从Access构建HTML输出并非不可能。
#5
1
We create multi-colored, conditionally formated, reports that are printed for the partner meeting each month of a publically traded corporation. They're real pretty.
我们为每个公开交易的公司的合作伙伴会议打印多色,有条件格式的报告。他们真的很漂亮。
#6
1
I would suggest that the problem you're having is because the requirement to replicate the old method identically is an incredibly bad idea.
我建议你遇到的问题是因为同样复制旧方法的要求是一个非常糟糕的主意。
You're not using Excel any more.
你不再使用Excel了。
You're using a different tool with different capabilities.
您正在使用具有不同功能的其他工具。
Thus, you will use different methods to get results.
因此,您将使用不同的方法来获得结果。
Re-evaluate the original requirements to see if they still make sense (e.g., exactly why is PowerPoint involved at all? Can PowerPoint import from the Access report snapshot viewer? Can PowerPoint import from a PDF produced from an Access report?), or if they are too connected to the old tools, and then determine what is important and what isn't, and only then should you start designing your solution.
重新评估原始要求,看看它们是否仍然有意义(例如,究竟为什么要涉及PowerPoint?可以从Access报告快照查看器导入PowerPoint吗?可以从Access报告生成的PDF导入PowerPoint吗?),或者它们与旧工具联系太紧,然后确定什么是重要的,什么不是,只有这样才能开始设计解决方案。
#7
0
I personally would not try to re-invent the wheel here. If you already have an Excel sheet that has the formatting you want, just export the data from Access into Excel for the report. Now, if you didn't have the original Excel sheet to begin with, that would be a completely different story.
我个人不会试图在这里重新发明*。如果您已经有一个具有所需格式的Excel工作表,只需将Access中的数据导出到Excel中即可。现在,如果您没有开始使用原始Excel工作表,那将是一个完全不同的故事。
As for breaking lines with non-monospaced fonts, have you tried setting the cell format to wrap?
对于使用非等宽字体的断行,您是否尝试过设置单元格格式?
#8
0
It sounds like the path of least resistance is to fill in the Excel spreadsheet. We have a contractor who does our Access stuff, and for the more complicated reports he uses Excel. I guess complicated == hard to make look good.
这听起来像阻力最小的路径是填写Excel电子表格。我们有一个承包商负责我的Access工作,而对于更复杂的报告他使用Excel。我觉得复杂==难以让人看起来很好。
#9
0
Rather than filling in the excel spreadsheet programmatically, you may want to use the external data features of Excel and Access. Generally I put a query on each tab, which of course may be hidden. An "update all" causes all the queries to be updated. Then summary tabs show the pretty results from all the individual queries.
您可能希望使用Excel和Access的外部数据功能,而不是以编程方式填写Excel电子表格。通常我会在每个选项卡上放置一个查询,当然可以隐藏它。 “全部更新”会导致更新所有查询。然后摘要选项卡显示所有单个查询的漂亮结果。
For one particularly complex system, a bit of excel vba programmatically changed a query and then walked through the tabs updating each one.
对于一个特别复杂的系统,一些excel vba以编程方式更改了一个查询,然后浏览了更新每个查询的选项卡。
Finally, rather than doing screen shots, Excel has a "copy cells as a image" copy that populates the copy buffer with a resizeable image. This could give you better looking results than a pure screenshot since a screenshot can have various deficiencies depending on pixel density.
最后,Excel没有进行屏幕截图,而是使用“复制单元格作为图像”副本,使用可调整大小的图像填充复制缓冲区。这可以比纯屏幕截图给你更好看的结果,因为屏幕截图可能有各种不足,具体取决于像素密度。
#10
0
Just an update: After a few hours of work, I was able to get a nice report out of Access (almost an exact copy of the excel version). It wasn't as difficult as I thought, I just had to figure the correct mixture of out subreports and pagebreaks.
只是一个更新:经过几个小时的工作,我能够从Access获得一个很好的报告(几乎是excel版本的精确副本)。这并不像我想象的那么困难,我只需要找出子报告和分页符的正确混合。
Working with the wordwrap features of Excel/Powerpoint were a dead end because there could only be a set number of lines per page, period; plus I was too lazy to nail down all the pagination with VBA code issues myself. Like Shelley says, Access shines at report generation.
使用Excel / Powerpoint的wordwrap功能是一个死胡同,因为每页只能有一定数量的行,句号;另外,我懒得自己解决VBA代码问题的所有分页。像Shelley说的那样,Access在报告生成时非常出色。
The output ended up being a PDF (Using Adobe Acrobat Professional). The problem I have left is getting select pages of said PDF into Powerpoint without Powerpoint antialiasing the results for me and making the resulting slide's text fuzzy. I found a couple of articles on converting .snp output to .wmf, which sounds like the way to go on that front.
输出结果是PDF(使用Adobe Acrobat Professional)。我留下的问题是将所述PDF的选择页面导入Powerpoint,而不使用Powerpoint对我的结果进行抗锯齿处理,并使得结果幻灯片的文本模糊。我发现了一些关于将.snp输出转换为.wmf的文章,这听起来像是在前面的方式。
#1
3
Access has the capability to create downright beautiful reports. The problem is that it can't make a spreadsheet look better than Excel. You have to know when to use each tool.
Access具有创建完美漂亮报告的功能。问题是它无法使电子表格看起来比Excel更好。您必须知道何时使用每个工具。
Use Excel when you have spreadsheet-like formatting, need a lot of boxes and lines, or want to draw charts.
如果您有类似电子表格的格式,需要大量的框和行,或者想要绘制图表,请使用Excel。
Use Access when you will output a report as a PDF. It's very useful for one-record-per-page detail reports, formatting where you need to position things very precisely, and where you need to embed subreports with related or unrelated data.
当您将报告输出为PDF时,请使用Access。它对于每页一个记录的详细报告非常有用,格式化您需要非常精确地定位的位置,以及您需要在何处嵌入具有相关或不相关数据的子报表。
Think about the reports that would be nasty in Excel because you'd have to merge cells all over the place and do funny things with the placement and the layout would never work. That's where Access shines.
想想在Excel中讨厌的报告,因为你必须在整个地方合并单元格,并对展示位置做有趣的事情,布局永远不会起作用。这就是Access闪耀的地方。
#2
3
Joel, (your co-host here) did a thing about using access reports for shipping labels a few years back... maybe this could be a inspriation for you?
Joel,(你在这里的共同主持人)做了一件关于几年前使用访问报告来运送标签的事情......也许这可能会激励你?
http://www.joelonsoftware.com/articles/HowToShipAnything.html
#3
2
I have implemented Access reports which were 'pretty' enough. The downside is that it takes a lot of time and effort, and trial and error to produce the desired output.
我已经实现了“非常”足够的Access报告。缺点是需要花费大量的时间和精力,以及产生所需输出的反复试验。
You can definitely get there, but it requires the patience of a saint.
你绝对可以到达那里,但它需要一个圣人的耐心。
#4
1
I guess it depends on what you mean by pretty. For example, I do not find it particularly difficult to produce say, reasonable graphs and tables with alternate line shading in Access. It is also possible to use MS Word and fill in bookmarks, or mail merge. If the present system uses VBA to create the PowerPoint presentation, perhaps much of it could be transferred to Access? Microsoft have an article on Access to Powerpoint: http://msdn.microsoft.com/en-us/library/aa159920(office.11).aspx
我想这取决于你的意思。例如,我发现在Access中生成具有备用线着色的说明,合理的图形和表格并不是特别困难。也可以使用MS Word并填写书签或邮件合并。如果本系统使用VBA创建PowerPoint演示文稿,可能其中大部分可以转移到Access?微软有一篇关于Access to Powerpoint的文章:http://msdn.microsoft.com/en-us/library/aa159920(office.11).aspx
Finally, it is not impossible to build HTML output from Access.
最后,从Access构建HTML输出并非不可能。
#5
1
We create multi-colored, conditionally formated, reports that are printed for the partner meeting each month of a publically traded corporation. They're real pretty.
我们为每个公开交易的公司的合作伙伴会议打印多色,有条件格式的报告。他们真的很漂亮。
#6
1
I would suggest that the problem you're having is because the requirement to replicate the old method identically is an incredibly bad idea.
我建议你遇到的问题是因为同样复制旧方法的要求是一个非常糟糕的主意。
You're not using Excel any more.
你不再使用Excel了。
You're using a different tool with different capabilities.
您正在使用具有不同功能的其他工具。
Thus, you will use different methods to get results.
因此,您将使用不同的方法来获得结果。
Re-evaluate the original requirements to see if they still make sense (e.g., exactly why is PowerPoint involved at all? Can PowerPoint import from the Access report snapshot viewer? Can PowerPoint import from a PDF produced from an Access report?), or if they are too connected to the old tools, and then determine what is important and what isn't, and only then should you start designing your solution.
重新评估原始要求,看看它们是否仍然有意义(例如,究竟为什么要涉及PowerPoint?可以从Access报告快照查看器导入PowerPoint吗?可以从Access报告生成的PDF导入PowerPoint吗?),或者它们与旧工具联系太紧,然后确定什么是重要的,什么不是,只有这样才能开始设计解决方案。
#7
0
I personally would not try to re-invent the wheel here. If you already have an Excel sheet that has the formatting you want, just export the data from Access into Excel for the report. Now, if you didn't have the original Excel sheet to begin with, that would be a completely different story.
我个人不会试图在这里重新发明*。如果您已经有一个具有所需格式的Excel工作表,只需将Access中的数据导出到Excel中即可。现在,如果您没有开始使用原始Excel工作表,那将是一个完全不同的故事。
As for breaking lines with non-monospaced fonts, have you tried setting the cell format to wrap?
对于使用非等宽字体的断行,您是否尝试过设置单元格格式?
#8
0
It sounds like the path of least resistance is to fill in the Excel spreadsheet. We have a contractor who does our Access stuff, and for the more complicated reports he uses Excel. I guess complicated == hard to make look good.
这听起来像阻力最小的路径是填写Excel电子表格。我们有一个承包商负责我的Access工作,而对于更复杂的报告他使用Excel。我觉得复杂==难以让人看起来很好。
#9
0
Rather than filling in the excel spreadsheet programmatically, you may want to use the external data features of Excel and Access. Generally I put a query on each tab, which of course may be hidden. An "update all" causes all the queries to be updated. Then summary tabs show the pretty results from all the individual queries.
您可能希望使用Excel和Access的外部数据功能,而不是以编程方式填写Excel电子表格。通常我会在每个选项卡上放置一个查询,当然可以隐藏它。 “全部更新”会导致更新所有查询。然后摘要选项卡显示所有单个查询的漂亮结果。
For one particularly complex system, a bit of excel vba programmatically changed a query and then walked through the tabs updating each one.
对于一个特别复杂的系统,一些excel vba以编程方式更改了一个查询,然后浏览了更新每个查询的选项卡。
Finally, rather than doing screen shots, Excel has a "copy cells as a image" copy that populates the copy buffer with a resizeable image. This could give you better looking results than a pure screenshot since a screenshot can have various deficiencies depending on pixel density.
最后,Excel没有进行屏幕截图,而是使用“复制单元格作为图像”副本,使用可调整大小的图像填充复制缓冲区。这可以比纯屏幕截图给你更好看的结果,因为屏幕截图可能有各种不足,具体取决于像素密度。
#10
0
Just an update: After a few hours of work, I was able to get a nice report out of Access (almost an exact copy of the excel version). It wasn't as difficult as I thought, I just had to figure the correct mixture of out subreports and pagebreaks.
只是一个更新:经过几个小时的工作,我能够从Access获得一个很好的报告(几乎是excel版本的精确副本)。这并不像我想象的那么困难,我只需要找出子报告和分页符的正确混合。
Working with the wordwrap features of Excel/Powerpoint were a dead end because there could only be a set number of lines per page, period; plus I was too lazy to nail down all the pagination with VBA code issues myself. Like Shelley says, Access shines at report generation.
使用Excel / Powerpoint的wordwrap功能是一个死胡同,因为每页只能有一定数量的行,句号;另外,我懒得自己解决VBA代码问题的所有分页。像Shelley说的那样,Access在报告生成时非常出色。
The output ended up being a PDF (Using Adobe Acrobat Professional). The problem I have left is getting select pages of said PDF into Powerpoint without Powerpoint antialiasing the results for me and making the resulting slide's text fuzzy. I found a couple of articles on converting .snp output to .wmf, which sounds like the way to go on that front.
输出结果是PDF(使用Adobe Acrobat Professional)。我留下的问题是将所述PDF的选择页面导入Powerpoint,而不使用Powerpoint对我的结果进行抗锯齿处理,并使得结果幻灯片的文本模糊。我发现了一些关于将.snp输出转换为.wmf的文章,这听起来像是在前面的方式。