Excel 2007-1004运行时错误刷新查询表 [英] Excel 2007 - 1004 Run-time error Refresh Query Table

问题描述

此错误似乎太普遍了，以至于许多现有解决方案都解决了不同的问题.

It appears that this error is so generic that many of the existing solutions address different issues.

就我而言，我创建了一个在Excel 2010中可以使用但在Excel 2007中不可以使用的宏.

In my case I have created a macro that works in Excel 2010 and does not work in Excel 2007.

以下是代码摘要:

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

此代码在Excel 2010中可以完美执行，但在Excel 2007中会出错.

This code executes perfectly in Excel 2010, but errors out in Excel 2007.

我是否需要更改2007年的连接字符串结构，还是有其他问题在起作用?

Do I need to change the connection string structure for 2007, or is there another issue at work?

推荐答案

解决方案:

我与Excel 2010一起使用的所有计算机都将"SQL Server Native Client 10.0"作为ODBC数据源的可能驱动程序.装有Excel 2007的计算机仅具有"SQL Server".

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".

我将连接字符串更改为:

I changed my connection string to be:

cn = "ODBC;Driver=SQL Server;Server=serverName;Database=dbName;Trusted_Connection=yes;"

它就像一种魅力.

感谢那些做出回应的人.

Thanks to those that responded.

这篇关于Excel 2007-1004运行时错误刷新查询表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！