Таблицы запросов (QueryTables) в Excel 2010 с VBA, где VBA создает множество соединений

Вы можете спросить себя, почему вы каждый раз создаете QueryTable в своем коде. Для этого есть причины, но обычно в этом нет необходимости.

QueryTable чаще всего являются объектами времени разработки. То есть вы создаете свою QueryTable один раз (с помощью кода или пользовательского интерфейса) и обновляете QueryTable для получения обновленных данных.

Если вам нужно изменить базовый оператор SQL, у вас есть несколько вариантов. Вы можете настроить параметры, которые запрашивают значение или получают его из ячейки. Другой вариант изменения SQL — изменение его в коде для существующей таблицы QueryTable.

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

Вы можете выбрать разные столбцы или даже разные таблицы, изменив CommandText. Если это другая база данных, вам понадобится новое подключение, но это случается довольно редко.

Я знаю, что это не дает прямого ответа на ваш вопрос, но я думаю, что определение того, действительно ли вам нужно каждый раз добавлять QueryTable, является первым шагом.

Дополнительные сведения о параметрах см. на странице http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/ Это для 2003 года, поэтому несоответствий с более поздними версиями немного. Основы те же, вам просто может понадобиться узнать об объекте ListObject, если вы используете 2007 или более позднюю версию.

Я была такая же проблема. Предыдущий ответ, хотя и определенный шаг в правильном направлении, — это PITA.

Однако это позволило мне уточнить поиск, и победителем стал...

http://msdn.microsoft.com/en-us/library/bb213491(v=office.12).aspx

то есть для вашего существующего объекта QueryTable просто сделайте следующее:

.MaintainConnection = False

Работает очень хорошо. Нет больше доступа к файлу блокировки БД после обновления данных.

Вы должны объявить соединение как отдельный объект, после чего вы сможете закрыть его после завершения запроса к базе данных.

У меня нет перед собой VBA IDE, так что извините, если есть какие-то неточности, но это должно указать вам правильное направление.

E.g.

Dim SQL As String
Dim con As connection

Set con = New connection
con.ConnectionString = "ODBC;DSN=DB01;UID=;PWD=;Database=MyDatabase"

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

con.close
set con = nothing

Я обнаружил, что по умолчанию новые соединения, созданные таким образом, называются «Соединение». Я использую этот фрагмент кода для удаления соединения, но сохраняю объект списка.

Application.DisplayAlerts = False
ActiveWorkbook.Connections("Connection").Delete
Application.DisplayAlerts = True

Его можно легко изменить, чтобы удалить последнее добавленное соединение (или если вы отслеживаете соединения по их индексу).

Application.DisplayAlerts = False
ActiveWorkbook.Connections(ActiveWorkbook.Connections.Count).Delete
Application.DisplayAlerts = True

Вместо того, чтобы добавлять другую таблицу запросов с помощью метода add, вы можете просто обновить свойство CommandText соединения. Однако вы должны знать, что при обновлении свойства CommandText соединения ODBC возникает ошибка. Если вы временно переключаетесь на соединение OLEDB, обновляете свойство CommandText, а затем переключаетесь обратно на ODBC, новое соединение не создается. Не спрашивайте меня, почему... это просто работает для меня.

Создайте новый модуль и вставьте следующий код:

Option Explicit

Sub UpdateWorkbookConnection(WorkbookConnectionObject As WorkbookConnection, Optional ByVal CommandText As String = "", Optional ByVal ConnectionString As String = "")

With WorkbookConnectionObject
    If .Type = xlConnectionTypeODBC Then
        If CommandText = "" Then CommandText = .ODBCConnection.CommandText
        If ConnectionString = "" Then ConnectionString = .ODBCConnection.Connection
        .ODBCConnection.Connection = Replace(.ODBCConnection.Connection, "ODBC;", "OLEDB;", 1, 1, vbTextCompare)
    ElseIf .Type = xlConnectionTypeOLEDB Then
        If CommandText = "" Then CommandText = .OLEDBConnection.CommandText
        If ConnectionString = "" Then ConnectionString = .OLEDBConnection.Connection
    Else
        MsgBox "Invalid connection object sent to UpdateWorkbookConnection function!", vbCritical, "Update Error"
        Exit Sub
    End If
    If StrComp(.OLEDBConnection.CommandText, CommandText, vbTextCompare) <> 0 Then
        .OLEDBConnection.CommandText = CommandText
    End If
    If StrComp(.OLEDBConnection.Connection, ConnectionString, vbTextCompare) <> 0 Then
        .OLEDBConnection.Connection = ConnectionString
    End If
    .Refresh
End With

End Sub

Эта подпрограмма UpdateWorkbookConnection работает только при обновлении соединений OLEDB или ODBC. Соединение не обязательно должно быть связано со сводной таблицей. Это также устраняет другую проблему и позволяет обновлять соединение, даже если существует несколько сводных таблиц, основанных на одном и том же соединении.

Чтобы инициировать обновление, просто вызовите функцию с объектом соединения и текстовыми параметрами команды следующим образом:

UpdateWorkbookConnection ActiveWorkbook.Connections("Connection"), "exec sp_MyAwesomeProcedure"

При желании вы также можете обновить строку подключения.

Если вы хотите удалить, если сразу после обновления, вы должны сделать обновление не в фоновом режиме (используя первый параметр -> Refresh False), чтобы у вас была правильная последовательность действий

Попробуйте установить для свойства QueryTable.MaintainConnection значение False...

«Установите для MaintenanceConnection значение True, если подключение к указанному источнику данных должно поддерживаться после обновления и до тех пор, пока рабочая книга не будет закрыта. Значение по умолчанию — True! напишите логическое значение)"

Спустя годы все еще актуальна ... борьба с той же проблемой, и это самая полезная тема. Моя ситуация является вариантом вышеизложенного, и я добавлю свое решение, когда найду его.

Я использую базу данных Access в качестве источника данных и устанавливаю таблицу запросов на новом листе. Затем я добавляю еще два новых листа и пытаюсь установить таблицу запросов, используя одно и то же соединение на каждом из них, но с другой таблицей Access. Первая таблица запросов работает просто отлично, и я использую .QueryTables(1).Delete и устанавливаю для объекта таблицы запросов значение Nothing, чтобы сделать его отключенным.

Однако на следующем листе происходит сбой при создании новой таблицы запросов с использованием того же соединения, которое не было закрыто. Я подозреваю (и добавлю решение ниже), что мне нужно разорвать соединение перед удалением таблицы запросов. Код Расмуса выше выглядит как вероятное решение.