从Access（97& 2K）自动编写Excel电子表格无法发布ExcelExcel [英] Auto writing Excel spreadsheets from Access (97 & 2K) fails to releaseExcel
问题描述
大家好，
我的代码可以成功打开，写入，格式化并保存几个
工作表工作簿然后按给定名称保存，关闭并退出excel。
我的问题是，如果我再次尝试再做，Excel会挂起。或者，如果我在关闭Access之前再次打开
Excel（比如从桌面图标），Excel
挂起。 （97和2000都发生了这种情况）
我当然认为我不能正确卸载变量。
但是我会显示我的结束代码：
设置wbknew =没什么
设置wksnew =没什么
ActiveWorkbook .SaveAs Filename：= strDestination，_
FileFormat：= xlNormal，密码：=""，WriteResPassword：=""，
ReadOnlyRecommended：= False， CreateBackup：= False
ActiveWorkbook.Close
appExcel.Quit
设置appExcel = Nothing
以下是我如何调暗这些变量：
Dim wbknew作为Excel.Workbook，wksnew As Excel.Worksheet
Dim appExcel As New Excel.Application
我看不出是什么原因导致Access以这样的方式保留Excel，以便让Excel挂起。
另一个线索：当我第二次运行代码时，它特别是
挂起时e代码尝试格式化第二个工作表。当
代码触及这行代码时：
列（1）.ColumnWidth = 1
I收到此消息：
运行时错误''1004''
对象''_Global'的方法''列''失败
我还注意到，在我的代码执行结束后，Excel仍会出现在
任务管理器的进程选项卡中。甚至杀死这个过程
并没有解决问题。
我真的希望有人知道这个问题。几年来它一直在困扰我
，因为这意味着每次我自动生成一个
电子表格时，我必须重新启动Access来运行另一个。
TIA
史密斯先生。
Hello all,
My code can successfully open, write to, format and save several
worksheets in a workbook then save it by a given name, close and quit excel.
My problem is that if I try and do it again, Excel hangs. OR if I open
Excel again (say from a desktop icon) before I close Access, Excel
hangs. (this has happened for both 97 & 2000 for me)
I of course thought that I mustn''t be unloading a variable properly.
but I''ll show my closing code:
Set wbknew = Nothing
Set wksnew = Nothing
ActiveWorkbook.SaveAs Filename:=strDestination, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
appExcel.Quit
Set appExcel = Nothing
And the following is how I dim these variables:
Dim wbknew As Excel.Workbook, wksnew As Excel.Worksheet
Dim appExcel As New Excel.Application
I can''t see what might be causing Access to hold on to Excel in such a
way as to make Excel hang.
Another clue: When I run the code for a second time, it specifically
hangs when the code attempts to format the second work sheet. When the
code strikes this line of code:
Columns(1).ColumnWidth = 1
I get this message:
Run-time error ''1004''
Method ''Columns'' of the object ''_Global'' failed
I have also noticed that Excel still appears in the ''Processes'' tab in
Task Manager after my codes execution ends. Even killing the process
doesn''t fix the problem.
I really hope someone knows the way around this. Its been bugging me
for a few years know because it means that every time I generate a
Spreadsheet automatically, I have to restart Access to run another.
TIA
Mr. Smith.
推荐答案
在Process选项卡下的任务管理器中，如果你单击图像名称
选项卡，这将按字母顺序列出所有进程（asc或
desc）。您可能会看到，即使您关闭了Excel exe，
也可能有多个Excel exe正在运行。你需要结束所有的
他们（在那里）。
接下来，如果xlObj.Quit没有结束Excel进程（它应该，你
可能错过了某个Excel应用程序的退出
对象）您可以使用API代码来确定是否有任何Excel进程运行和运行
然后结束他们。以下是执行此操作的API代码的3个链接：
''获取正在运行的应用程序的类名
http://www.mvps.org/access/api/api0013.htm
''了解申请是否正在运行
http ：//www.mvps.org/access/api/api0007.htm
''从其他应用程序关闭应用程序
http://www.mvps.org/access/api/api0025.htm < br $>
Rich
***通过Devdex发送 http://www.devdex.com ***
不要只是参加USENET ......获得奖励！
In Task Manager under the Process tab, if you click on the Image Name
tab this will list all the processes in alphabetical order (asc or
desc). You will probably see that even though you close your Excel exe,
there are probably multiple Excel exe''s running. You need to end all of
them (been there).
Next, if xlObj.Quit is not ending the Excel process (it should, you
probably missed a Quit somewhere for one of your Excel Application
objects) you can use API code to determine if any Excel processes are
running and then End them. Here are 3 links to API code for doing this:
''Get Class names of running applications
http://www.mvps.org/access/api/api0013.htm
''Find out if an application is running
http://www.mvps.org/access/api/api0007.htm
''Closing an Application from another application
http://www.mvps.org/access/api/api0025.htm
Rich
*** Sent via Devdex http://www.devdex.com ***
Don''t just participate in USENET...get rewarded for it!
请勿在变量声明中使用New关键字。对于某些操作来说，New可以很方便，但对其他操作来说非常危险，而且你不应该将它用于Automation对象，因为它可以导致你现在遇到的问题是什么。
VBA任何As New变量的问题，就是你每次推荐时的问题
以任何方式变量，VBA检查它是否包含Nothing，并且
如果没有，则创建变量'的类型的新实例。这可能是因为这个
导致你的代码重新打开Excel。而不是新的，尝试...
昏暗的appExcel作为Excel.Application
设置appExcel =新的Excel.Application
2004年6月21日星期一20:38:34 +1000，先生。史密斯和QUOT; < Mr.Smith@Large>写道：
Try not using the New keyword in your variable declarations. As New can be
convenient for some operations, but it''s very dangerous for others, and you
should -never- use it for Automation objects because it can lead to the
problems you are having now.
The problem with the way VBA any As New variable, is that each time you refer
to the variable in any way, VBA checks to see if it contains Nothing, and
creates a new instance of the variable''s type if not. It''s possible that this
is causing your code to re-open Excel. Instead of As New, try ...
Dim appExcel As Excel.Application
Set appExcel = New Excel.Application
On Mon, 21 Jun 2004 20:38:34 +1000, "Mr. Smith" <Mr.Smith@Large> wrote:
大家好，
我的代码可以在工作簿中成功打开，写入，格式化和保存几个
工作表然后用给定的名字保存它，关闭并退出excel。
我的问题是，如果我再试一次，Excel会挂起。或者，如果我在关闭Access之前再次打开Excel（例如从桌面图标中说出来），Excel将挂起。 （对于我而言，97和2000都发生了这种情况）
我当然认为我不能正确地卸载变量。
但我会显示我的结束代码：
设置wbknew = Nothing
设置wksnew = Nothing
ActiveWorkbook.SaveAs文件名：= strDestination，_
FileFormat：= xlNormal，密码：= ""，WriteResPassword：=""，
ReadOnlyRecommended：= False，CreateBackup：= False
ActiveWorkbook.Close
appExcel.Quit
设置appExcel = Nothing
以下是我如何调暗这些变量：
Dim wbknew作为Excel.Workbook，wksnew As Excel.Worksheet
Dim appExcel作为新的Excel.Application
我看不出是什么原因导致Access以这样的方式保留Excel以使Excel挂起。
另一条线索：当我第二次运行代码时，它会在代码尝试格式化secon时挂起d工作表。当
代码触及这行代码时：
列（1）.ColumnWidth = 1
我收到此消息：
运行时错误''1004''
对象''_Global'的方法''列''失败
我也注意到Excel仍出现在''进程'中我的代码执行结束后，任务管理器中的选项卡。甚至杀死这个过程
并没有解决问题。
我真的希望有人知道这方面的方法。它一直困扰着我
几年，因为这意味着每次我自动生成一个电子表格，我都必须重新启动Access来运行另一个。
TIA 先生。史密斯。
Hello all,
My code can successfully open, write to, format and save several
worksheets in a workbook then save it by a given name, close and quit excel.
My problem is that if I try and do it again, Excel hangs. OR if I open
Excel again (say from a desktop icon) before I close Access, Excel
hangs. (this has happened for both 97 & 2000 for me)
I of course thought that I mustn''t be unloading a variable properly.
but I''ll show my closing code:
Set wbknew = Nothing
Set wksnew = Nothing
ActiveWorkbook.SaveAs Filename:=strDestination, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
appExcel.Quit
Set appExcel = Nothing
And the following is how I dim these variables:
Dim wbknew As Excel.Workbook, wksnew As Excel.Worksheet
Dim appExcel As New Excel.Application
I can''t see what might be causing Access to hold on to Excel in such a
way as to make Excel hang.
Another clue: When I run the code for a second time, it specifically
hangs when the code attempts to format the second work sheet. When the
code strikes this line of code:
Columns(1).ColumnWidth = 1
I get this message:
Run-time error ''1004''
Method ''Columns'' of the object ''_Global'' failed
I have also noticed that Excel still appears in the ''Processes'' tab in
Task Manager after my codes execution ends. Even killing the process
doesn''t fix the problem.
I really hope someone knows the way around this. Its been bugging me
for a few years know because it means that every time I generate a
Spreadsheet automatically, I have to restart Access to run another.
TIA
Mr. Smith.
与史蒂夫的推理一致我想我会分享我的方式
从Access设置Excel对象（因为这是我日常工作中的重要部分）。我声明一个Excel对象变量（引用
到相应的Excel对象库，Excel 8.0,9.0,10 ......）
Dim xlObj As Excel.Application，wkbk As Excel.WorkBook
Dim sht As Excel.WorkSheet，rng As Excel.Range
...
设置xlObj = CreateObject（Excel.Applicaion）
史蒂夫对于VBA中新关键字的细微性是正确的。我没想到这个因为我一直在为VBA for Office Applications使用New
（尽管New在
中工作得很好）
托管环境，如VB.Net或C＃ - 如果你声明错误的话，编译器会立即打你的手b / b
，但是我使用New保留
预留用于实例化Access中的自定义对象
（使用类模块创建的对象），因为在这里你不必担心运行外部进程。这是Access的内部。
Rich
***通过Devdex发送 http://www.devdex.com ***
不要只是参加USENET ......获得奖励为它！
Going along with Steve''s line of reasoning I thought I would share how I
set up Excel objects from Access (since this is a significant portion of
my daily routine). I declare an Excel object variable (with a reference
to the respective Excel Object Library, Excel 8.0, 9.0, 10...)
Dim xlObj As Excel.Application, wkbk As Excel.WorkBook
Dim sht As Excel.WorkSheet, rng As Excel.Range
...
Set xlObj = CreateObject("Excel.Applicaion")
Steve is correct about the delicateness of the New Keyword in VBA. I
didn''t think about that because I have been steering away from using New
in VBA for Office Applications (although New works quite well in a
managed environment like VB.Net or C# - where the compiler will slap
your hand right away if you declare something incorrectly), but I
reserve using New for instantiating custom objects within Access
(objects created with a class module) because here you don''t have to
worry about running an external process. It is internal to Access.
Rich
*** Sent via Devdex http://www.devdex.com ***
Don''t just participate in USENET...get rewarded for it!
这篇关于从Access（97& 2K）自动编写Excel电子表格无法发布ExcelExcel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
