使用VBA创建许多连接的VBA中的Excel 2010中的查询表（QueryTables） [英] Query Tables (QueryTables) in Excel 2010 with VBA with VBA creating many connections

问题描述

我正在追踪我在另一个网站上发现的代码。以下是我的代码的基础知识：

  Dim SQL As String 
 Dim connString As String 
 
 connString =ODBC; DSN = DB01; UID =; PWD =;数据库= MyDatabase
 SQL =从SomeTable选择*
 
带有工作表（Received）。添加（Connection：= connString，Destination：= Worksheets（Received）。Range（A5），SQL：= SQL）
 .Refresh 
 
 End with 
 
 End Sub 
  

这样做的问题是每一次他们点击分配给这个按钮的按钮它创建了一个新的连接，似乎没有放弃它。测试后我打开电子表格，连接下列出的连接有很多版本。
连接
Connection1
Connection2

我似乎找不到关闭或删除连接的方法。如果在.Refresh之后添加.delete，我会收到一个1004错误。由于数据在后台刷新，因此无法进行此操作。

任何想法如何关闭或删除连接？

解决方案

您可能会问自己为什么每次在代码中创建一个QueryTable。有理由这样做，但通常没有必要。

QueryTables更通常是设计时对象。也就是说，您创建一次QueryTable（通过代码或UI），并刷新QueryTable以获取更新的数据。

如果需要更改底层SQL语句你有一些选择。您可以设置提示值或从单元格获取值的参数。另一个更改SQL的选项是在现有QueryTable的代码中进行更改。

  Sheet1.QueryTables（1）.CommandText =选择* FROM ....
 Sheet1.QueryTables（1）.Refresh 
  

你可以通过更改CommandText来选择不同的列，甚至不同的表。如果它是一个不同的数据库，那么你需要一个新的连接，但这很少见。

我知道直接不回答你的问题，你真的需要添加QueryTable，每次都是第一步。

有关参数的更多信息，请参阅 http://www.dicks-clicks.com/excel/ExternalData6.htm 是2003年，所以与以后的版本几乎不一致。基础知识是一样的，如果您使用的是2007或更高版本，您可能需要了解ListObject对象。

I'm following code I found on another site. Here's the basics of my code:

Dim SQL As String
Dim connString As String

connString = "ODBC;DSN=DB01;UID=;PWD=;Database=MyDatabase"
SQL = "Select * from SomeTable"

With Worksheets("Received").QueryTables.Add(Connection:=connString, Destination:=Worksheets("Received").Range("A5"), SQL:=SQL)
.Refresh

End With

End Sub

The problem with doing this is every single time they hit the button assigned to this it creates a new connection and doesn't ever seem to drop it. I open the spreadsheet after testing and there are many versions of the connection listed under Connections. Connection Connection1 Connection2

I can't seem to find a way to close or delete the connections either. If I add ".delete" after ".Refresh" I get a 1004 error. This operation cannot be done because the data is refreshing in the background.

Any ideas how to close or delete the connection?

解决方案

You might ask yourself why you're creating a QueryTable every time in your code. There are reasons to do it, but it usually isn't necessary.

QueryTables are more typically design-time objects. That is, you create your QueryTable once (through code or the UI) and the you Refresh the QueryTable to get updated data.

If you need to change the underlying SQL statement, you have some options. You could set up Parameters that prompt for a value or get it from a cell. Another option for changing the SQL is changing it in code for the existing QueryTable.

Sheet1.QueryTables(1).CommandText = "Select * FROM ...."
Sheet1.QueryTables(1).Refresh

You can select different columns or even different tables by changing CommandText. If it's a different database, you'll need a new connection, but that's pretty rare.

I know that doesn't answer your question directly, but I think determining whether you really need to add the QueryTable each time is the first step.

For more on Parameters, see http://www.dicks-clicks.com/excel/ExternalData6.htm It's for 2003, so there are few inconsistencies with later versions. The basics are the same, you just may need to learn about the ListObject object if you're using 2007 or later.

这篇关于使用VBA创建许多连接的VBA中的Excel 2010中的查询表（QueryTables）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！