vba Excel 2007 - 1004 运行时错误刷新查询表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16151560/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2007 - 1004 Run-time error Refresh Query Table
提问by lance
It appears that this error is so generic that many of the existing solutions address different issues.
看来这个错误是如此普遍，以至于许多现有的解决方案解决了不同的问题。
In my case I have created a macro that works in Excel 2010 and does not work in Excel 2007.
就我而言，我创建了一个在 Excel 2010 中有效但在 Excel 2007 中无效的宏。
Here is a summary of the code:
下面是代码的摘要：
cn = "ODBC;Driver={SQL Server Native Client 10.0};Server=serverName;Database=dbName;Trusted_Connection=yes;"
sql = "select top 10 * from tableName"
Dim S As Worksheet
Set S = ActiveWorkbook.Sheets("Medical")
With S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1"))
.CommandText = sql
.Refresh BackgroundQuery:=False
End With
This code executes perfectly in Excel 2010, but errors out in Excel 2007.
此代码在 Excel 2010 中完美执行，但在 Excel 2007 中出错。
Do I need to change the connection string structure for 2007, or is there another issue at work?
我是否需要更改 2007 的连接字符串结构，或者是否存在其他问题？
回答by lance
Solution:
解决方案：
All machines I was using with Excel 2010 have "SQL Server Native Client 10.0" as a possible driver for ODBC Data Sources. The machines with Excel 2007 only have "SQL Server".
我在 Excel 2010 中使用的所有机器都有“SQL Server Native Client 10.0”作为 ODBC 数据源的可能驱动程序。装有 Excel 2007 的机器只有“SQL Server”。
I changed my connection string to be:
我将连接字符串更改为：
cn = "ODBC;Driver=SQL Server;Server=serverName;Database=dbName;Trusted_Connection=yes;"
and it worked like a charm.
它就像一个魅力。
Thanks to those that responded.
感谢那些做出回应的人。
回答by AxGryndr
Change your property .Refresh BackgroundQuery:=Falseto be .Refresh. Nothing has changed from 2007 to 2010 in regards to this so if changing the property does not fix the issue there is something else going on such as part of the workbook is corrupt.
将您的属性更改.Refresh BackgroundQuery:=False为.Refresh. 从 2007 年到 2010 年在这方面没有任何变化，因此如果更改属性不能解决问题，则会发生其他事情，例如工作簿的一部分已损坏。
回答by pkk
I had the same issue when calling stored procedure from the macro. After wasting a whole day in searching internet for the solution, I finally found a solution.
从宏调用存储过程时我遇到了同样的问题。在网上搜索解决方案浪费了一整天后，我终于找到了解决方案。
I used SET NOCOUNT ON;
我使用了 SET NOCOUNT ON;
It worked!
有效！
回答by Jordan
Unable to add a comment so this is to address the question posed above.
无法添加评论，所以这是为了解决上面提出的问题。
This answer saved me a lot of time.
这个答案为我节省了很多时间。
MSFT documentation on this: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql
MSFT 文档：https: //docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql
Below is an implementation.
下面是一个实现。
create proc yourSproc
as
set nocount on;
begin
--Contents of sproc
end
回答by peterj
I had this exact error. The debug would take me to the .refresh line as well. I worked every which way I could think of to fix that line. Nothing worked.
我有这个确切的错误。调试也会带我到 .refresh 行。我想尽一切办法修复那条线路。没有任何效果。
The "General ODBC Error" verbiage had me thinking...I went to Run >> Microsoft ODBC Administrator >> Selected my DataSource >> Clicked the Configure button >> Clicked Test Connection
“常规 ODBC 错误”的话让我想到了……我去运行 >> Microsoft ODBC 管理员 >> 选择我的数据源 >> 单击配置按钮 >> 单击测试连接
That returned an error that my password had expired and the database was set to read only. My Windows User password has not expired but it was within the window of when I "should" change my password...I was using a work machine and have mandatory user account password changes every 60-90 days or so with a 30-something day grace period. Didn't even know that.
这返回了我的密码已过期并且数据库设置为只读的错误。我的 Windows 用户密码尚未过期，但它在我“应该”更改密码的窗口之内……我使用的是工作机器，并且每 60-90 天左右强制更改用户帐户密码，大约 30 天日宽限期。甚至不知道。
Not saying this is the fix for you, but I was getting the exact same error and had tried every little trick and edit this website, and the Internet, had to offer with no luck. I logged out, reset my Windows User Account password, logged back in, opened the excel file, hit the Refresh Button macro...and it worked.
并不是说这是适合您的解决方案，但我遇到了完全相同的错误，并尝试了所有小技巧并编辑了该网站和互联网，但没有运气。我注销了，重置了我的 Windows 用户帐户密码，重新登录，打开了 excel 文件，点击了刷新按钮宏......它工作了。
Anyway, maybe this will help someone...
无论如何，也许这会帮助某人......
