Импорт данных из базы данных SQL в Excel 2003 VS Excel 2010 (VBA)

Итак, моя компания наконец-то перешла на MS Office 2010. До сих пор я работал в 2003 году. Я программист SQL, и я постоянно создаю отчеты в Excel, которые извлекают данные из нашей базы данных. Большую часть времени я буду создавать макросы, которые будут принимать параметры, которые пользователи будут вводить в определенные ячейки, изменять запрос, а затем обновлять его в соответствии с параметрами.

Вот действительно простой пример:

  1. В Excel 2003 я бы открыл новую рабочую книгу.
  2. Нажмите "Данные", затем "Импорт внешних данных", затем "Новый запрос к базе данных".
  3. Затем вам будет предложено выбрать источник данных, поэтому я бы выбрал базу данных, к которой хотел обратиться (которая уже была настроена с помощью соединения ODBC).

  4. Затем я отменяю работу из окон мастера запросов, а затем, когда я нахожусь в редакторе запросов Microsoft, я просто ввожу свой запрос.

    • Для простоты я буду выбирать * из таблицы под названием Agents это просто список агентов, которые работают на компанию и их EmployeeIds,

    • select * from Agents

  5. Затем я "х" из редактора запросов, и появляется окно под названием "Импорт данных", где он спрашивает, где вы хотите поместить данные: в существующую таблицу? новый лист? и т. д. Я просто хочу вернуть данные в существующую таблицу, начиная с ячейки A2

Затем я пишу этот простой макрос в редакторе Visual Basic в модуле для рабочей книги:

Sub Refresh()

Dim oQuery as QueryTable
Dim oAgent as String

set oQuery = Sheet1.QueryTables(1)

oAgent = Sheet1.Range("A1")

oQuery.CommandText = "select * from Agents where Agent = '"+oAgent+"'"
oQuery.Refresh

End Sub

Я создаю кнопку, которая запускает этот макрос, и вставляю его в B1, Таким образом, пользователь открывает отчет, вводит имя в A1, нажимает на кнопку, и этот агент и их Id появляется в таблице ниже. Действительно просто верно? Но я не могу заставить это работать в Excel 2010.

Вот мои шаги и следующая ошибка:

  1. Я открываю Excel 2010 и захожу на вкладку "Данные".
  2. В разделе "Получить внешние данные" я нажимаю "Из других источников" и выбираю "Из запроса Mircrosoft" из выпадающего списка.
  3. Затем появляется окно выбора источника данных, и оно в основном совпадает с шагами 3,4 и 5 выше.

Затем я пишу тот же макрос, создаю кнопку и назначаю ее марко, но когда я нажимаю кнопку, я получаю следующую ошибку:

Run-time error '9':
Subscript out of range

Я нажимаю отладку, и отладчик выделяет эту строку

Set oQuery = Sheet1.QueryTables(1)

Я попытался сделать эту строку более конкретной, например:

Set oQuery = WorkBooks("Book 1").Sheets("Sheet 1").QueryTables(1)

Но я просто получаю ту же ошибку.

Поэтому в основном мне нужно знать, как делать подобные вещи в Excel 2010. Но вот интересная заметка: если я создаю этот отчет в Excel 2003, сохраняю его как.xls, а затем открываю в 2010 году, он будет Работа. Я даже могу сохранить копию в формате.xlsm, а затем открыть ее, и она будет работать с этим же макросом. Только когда я создаю отчет в 2010 году, я не могу заставить его работать. По какой-то причине кажется, что он просто не может найти запрос, чтобы изменить текст своей команды и затем обновить. Пожалуйста, помогите, я застрял на этом в течение нескольких дней!

Источник

1 ответ

Решение

В XL2007 и 2010 таблицы запросов содержатся в "ListObject" на листе, поэтому вам просто нужно настроить свой код так:

Set oQuery = WorkBooks("Book 1").Sheets("Sheet 1").ListObjects(1).QueryTables(1)

http://msdn.microsoft.com/en-us/library/ff841237.aspx

Тим

Источник
Другие вопросы по тегам