将查询导出到Excel文件 [英] Export Query to Excel File
问题描述
我似乎已经过头了，可以使用一些帮助...
我拼凑了几个互联网搜索过的代码，这些代码应该从访问权限中导出查询现有的excel文件。我还希望它在导出查询之前清除Excel工作表上的任何现有数据。它似乎有时工作，其他时候excel文件打开它有一个白色的屏幕，工具栏被冻结，但如果我关闭并重新打开它所有的数据都在那里。我的问题是，
1.代码的哪一部分可能导致屏幕问题
2.代码是否需要更多微调如果是这样的话推荐什么
3.有没有更好的方法来导出到现有文件并确保所有内容都被覆盖
感谢您提供的任何反馈
I seem to be over my head and can use some help...
I have pieced together some code from several internet searched that is supposed to export a query from access to an existing excel file. I also wanted it to clear any existing data on the excel sheet before the query is exported. It seems to work sometimes, other times when the excel file opens it has a white screen and the toolbars are frozen, but if I close and reopen it all the data is there. The question I have is,
1. Which part of the code could be causing to problem with the screen
2. Does the code need more fine tuning and if so what is recommended
3. Is there a better way to do the export to an existing file and be sure all the contents of the sheet are overwritten
Thank You for any feed back you can offer
推荐答案
还有你的代码似乎没有任何明显的问题。我怀疑问题出在RESUME.XLW文件中。当您使用Office自动化创建Excel的新实例时，它通过创建工作空间来完成，因此是RESUME.XLW文件。
如果由于任何原因您的代码被中断或如果您的文件没有正确保存，此工作区将保留上一次尝试的所有信息。然后，当你再次尝试再次运行时，它会卡住。您可以执行的检查是运行任务管理器并查看进程列表。如果您先退出桌面上所有打开的Excel文件，那么就不应该运行任何Excel.exe进程。如果有，那么它们就是没有正确关闭的实例。
我发现非常有用的一件事是使用错误处理来强制xlapp.quit代码即使代码被中断也要运行。
There doesn''t appear to be any obvious problems with your code. I suspect the problem lies with the RESUME.XLW file. When you use Office automation to create a new instance of Excel it is done with the creation of a workspace, hence the RESUME.XLW file.
If for any reason your code gets interrupted or your file doesn''t properly save, this workspace will retain all the information from the previous attempt. Then when you try to run it again it gets stuck. A check you can do is to run task manager and look at the process list. If you quit all open excel files on your desktop first, then there shouldn''t be any Excel.exe processes running. If there is then they are instances that didn''t properly close.
One thing I''ve found to be very useful is to use error handling to force the xlapp.quit code to run even if the code is interrupted.
经过我兄弟专家的许可
你可以使用这段代码
取消查看
With the permission of my brothers Experts
you can use this code
TO Un-View
@msquared
谢谢回复。我尝试了On Error GoTo Err_AnyEvent。我不确定，但我认为某处肯定存在错误，因为在代码运行后它跟随链接打开XLS文件，它仍然有时会显示白屏。我确实检查了任务管理器，并且没有显示其他Excel实例。此外，一旦关闭XLS文件并且代码应退出，它将显示错误消息91 - 对象变量或未在Anyevent中设置的块变量。该消息不响应ok。按钮和关闭它的唯一方法是任务管理器的结束任务。
@msquared
Thanks for the replys. I tried the On Error GoTo Err_AnyEvent. I am not sure but I think there must be an error somewhere because after the code runs and it follows the link to open the XLS file, it still at times displays the white screen. I did check the task manager and there is no other instance of Excel shown. Also once the XLS file is closed and the code should exit out it displays a error message of "91 - Object variable or With block variable not set in Anyevent" This message does not respond to the "ok" button and the only way to close it is the end task from the task manager.
这篇关于将查询导出到Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
