将报表导出到Excel并使用Access VBA格式化它 [英] Exporting a report to Excel and formatting it with Access VBA
问题描述
我正在创建一个表单，根据用户选择的模型类型，法兰类型和一系列行大小来选择数据。这不是问题，我能够从表格中获得正确的报告。
我的问题是我用来将报告导出到Excel的按钮。我希望按钮使用基于表单中选择的参数的文件名保存文件，然后打开excel文件，删除空列，稍稍移动表（只是使边框可见），然后添加行到让它看起来像一张漂亮的桌子。当我点击按钮时，我大部分时间都在工作。当excel已经打开时，它似乎无法正常工作，特别是如果有一个已经打开的excel文件是由此按钮创建的。真正让我感到的是它会给出一个对象''_Global''的方法范围。有时会出错，但是如果我关闭excel文件再试一次就没问题了。
请记住，对于Access和VBA来说，我是一个完整的新手 - 一切你看到我在上周教过自己了。所以随意像对待孩子一样对待我，我正在学习每一步。
我省略了表格上有关选项列表的所有代码。这只是按钮。
I am creating a form that selects data based on a user selected model type, flange type, and a range of line sizes. This hasn''t been a problem, I am able to get the correct report from the form thus far.
My issue is the button I am using to export the report to excel. I want the button to save the file with a filename based on the parameters selected in the form, then open the excel file, delete empty columns, move the table a bit (just so the borders are visible), and then add the lines to make it look like a nice table. I am at the point where this works most of the time when I click the button. It seems to be not working properly whenever excel is already open, especially if there is an excel file that is already open which was created by this button. What really gets me is that it''ll give a "Method ''Range'' of object ''_Global'' " error sometimes but if I close the excel file and try again it will work no problem.
Keep in mind that I am a complete novice when it comes to Access and VBA in general - everything you see here I have taught myself in the last week. So feel free to treat me like a child, I am learning each step of the way.
I have omitted all the code concerning the option lists on the form. This is just the button.
推荐答案
您可以创建模板 ;已完成所有格式的工作簿。然后制作工作簿的副本并将数据导入其中。这样你就不必编写所有的格式化工作代码，并且可以在以后的路上更容易地更改格式。
You could create a "template" workbook with all your formatting already done. Then make a copy of the workbook and import the data into it. That way you don''t have to code all the formatting work and makes it easier to change formatting later down the road.
问题是我不会是访问这个的唯一perosn数据库。因此代码需要从任何具有Excel和Access的计算机上以正确的格式生成Excel工作表。因此，如果我没有弄错的话，Access VBA需要完成整个任务。
The issue is that I will not be the only perosn accessing this database. So the code needs to produce the Excel sheet in the correct format from any computer that has Excel and Access on it. So the Access VBA needs to complete the entire task if I''m not mistaken.
你能不能将excel模板放在每个人都可以访问的中央网络位置？或者在每个人的Access数据库副本中包含excel模板？
Can you not put the excel template in a central network location that everyone has access to? Or include the excel template with each person''s copy of the Access database?
这篇关于将报表导出到Excel并使用Access VBA格式化它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
