Sql запрос к листу excel vba: SQL Update в Excel — В операции должен использоваться обновляемый запрос

И снова про MS Excel / Хабр

Однажды в моем инстаграме появились сразу несколько блоков рекламы об ознакомительных презентациях нескольких Российских онлайн-школ на тему «Аналитика данных на Python для чайников». Подумав о том, что можно с пользой для себя провести несколько часов и прокачать свои навыки по анализу, я записался и посетил данные курсы. Самое забавное, что практически все что я видел начиналось словами: «Давайте выбросим MS Excel (далее с вашего позволения просто — «эксель») и начнем работать на Python». Возмущению моему не было предела. В разумных рамках. Пройдя свой путь от разработчика (до черти как это назвать) и постоянно работая с данными, могу только привести в ответ фразу знаменитого персонажа Alf — «Вы просто не умеете их готовить».

картинка взята с keddr.com

Да, да, да. Можно хранить в «экселе» миллионы строк, пытаясь использовать его вместо базы данных (к слову говоря в одной из организаций поначалу мне пришлось воспользоваться этой «фичей»). Можно строить сложные вычисления на формулах (один из проектов доставшихся мне высчитывал Scheme Fee процентовку на «экселе»). Можно даже не изобретая «ардуино проекты» — написать код на VBA для эмуляции движения мыши. А можно просто использовать его там где он силен — в представлении табличных данных для анализа человеком. Об этом я и попытаюсь вкратце (в очередной 100500 раз) рассказать.

Прежде всего хочу начать с простого описания самих проблем когда приходится брать в руки швейцарские часы и забивать ими гвозди:

  • Ограниченность программных продуктов на рабочем месте

  • Необходимость для аналитиков в изучении новых продуктов (Power BI, Tableau и т.д.)

  • Простота использования — Нажми на кнопку и получишь результат (С) Технология

В тех страховых и финансовых компаниях которых я работал, не существовало возможности установить дополнительно программное обеспечение на компьютер или тонкий клиент. Вообще. Совсем. На последнем месте было невозможно запустить даже cmd. Но везде и всегда был установлен нелюбимый многими  M$ Офис. И тут напрашивается старый анекдот для женщин полицейских:

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

Если бы мне раньше сказали что после Delphi/C#/Java/JavaScript я скачусь до VBA, то возможно я бы плюнул человеку в глаза. Но жизнь расставила фишечки по-другому. Перейдя в сферу анализа данных, волшебных пузырьков отчетов после работы с Jasper Reports/Crystal reports/Business Objects и еще другими подобными продуктами я понял как устал выбирать пиксели рисуя красивые отчеты, чтобы будучи экспортированными в Excel у таблиц в буквальном смысле не «плыла крыша». Попав в одно из подразделений страхового брокера AON, мне предстояло анализировать ежемесячные выгрузки филиальных данных, на которых впоследствии должен быть построен дашборд и продан как продукт другим страховщикам. Нас было трое. 4 потока новых ежемесячных страховых полисов с 4-х разных стран, использующих абсолютно разные программные продукты, нам нужно было проанализировать до следующего экстракта и после причесывания данных (ETL) загрузить в корпоративное хранилище. Были выданы «ложка-вилка-нож» в виде SQL Server Management Studio и оно понеслось. Начав разгребать данные, у меня со временем появились свои наработки в виде SQL скриптов, но задалбывало копировать результаты выполнения в «эксельку» для анализа. В итоге начали появляться наброски кода на VBA + SQL для анализа. Уходя из компании через 2 года и оставляя законченный продукт в виде XLSM, 4 аналитика уже «шерстили» 28 ежемесячных выгрузок данных за 2 недели путем нажатия на кнопку и внимательного лицезрения таблиц с агрегацией и графиков сравнения с предыдущими месяцами.

Следующим новым этапом в компании (глобальный платежный провайдер) была автоматизация загрузок счетов от Visa & MasterCard. Представьте что в вашей компании имеются 40 банковских счетов. по каждому счету каждый день MasterCard выставляет счет. Специальная «тётенька» с высшим образованием в начале каждого месяца заходила на сайт компании и путем задания фильтра по счетам и различных кликов мышой скачивала 1200 документов. Которые впоследствии нужно было агрегировать в один документ. И в данном случае снова на помощь пришел «эксель». Пока компания решала какой из серверов БД будет использоваться, пришлось сначала сливать и делать анализ в самих «экселевских» файлах, а затем был найден чудесным образом установленный на тонком клиенте — SQL CE. Впоследствии все перекочевало на нормальный SQL Server 2008R2 с небольшой доработкой VBA кода. А далее был продлен контракт с очень интересным для меня проектом.

Обрабатывая многомиллионные ежедневные транзакционные данные, нужно было посчитать Scheme Fee (микроплатежи в пользу Visa/MasterCard начисляемые по различным параметрам. Как пример — за каждые 1000 платежей по магнитной полоске, бесконтактно или Chip&Pin. За использование «заморской» карты в европейском регионе и т. д.) и по результатам месячный активности выставить счета потребителям услуг (H&M, Tesco, Emirates Airlines, Apple, AliExpress etc). Вся забава начиналась тогда, когда тот или иной клиент запрашивал дополнительные данные по месячному срезу — например разбивку по типам карт или по дневному трафику. Вот тогда пошли в дело шаблоны на «эксельках». Для каждого клиента рисовался отдельный шаблон, который заполнялся из нужного датасета в процессе обработке на первой неделе каждого месяца. Хотите график? Без проблем! Что мы имели на выходе? XLS или PDF файл который направлялся менеджеру по работе с клиентом после предварительного просмотра девчатами из нашего отдела. Все что требовалось от них в случае изменения «% ставки клиента» — выгрузить таблицу в «эксельку», подправить данные, загрузить обратно и сгенерить новый счет нажав большую красивую кнопку и указав часть имени клиента по которому были произведены изменения. Все.

К чему такой длинный короткий опус? Когда мне говорят, что нужно «выбросить эксельку и взяться за анализ данных на Python купив наш курс» — я начинаю рыдать. Тащить миллионы строк данных на Python нет смысла — лучше обрабатывать это все на самом сервере (об этом кстати может быть отдельный опус с примерами «как я пытался перенести и оптимизировать расчет Scheme Fee с SQL Server на Python»). А презентацию конечных данных лучше производить уже в тот, живой формат с которым будут работать пользователи — XLS

Можно пытаться долго и красиво описать что я делал, но все это наверняка будет не совсем понятно без живого примера. Недавно я начал воссоздавать старый код в библиотеку «для сэбэ» для коммерческого использования. В качестве же подопытного кролика я решил взять библиотеку sp_Blitz написанную Brent Ozar и со товарищи. Библиотека представляет набор хранимых процедур для SQL Server DBA. Меня начало напрягать — запускать хранимку и вытаскивать из кучи датасетов нужное. Поэтому в двух видео ниже показано как красиво (а для кого-то, возможно, и не очень) можно представлять данные по клику, используя «эксельку».

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

Update: Пост не о том что эксель [не] умеет обрабатывать большие данные и это нужно делать. Пост о том как можно красиво вытаскивать подготовленные данные и представлять их клиенту чтобы он не растерялся и знал куда смотреть. В первом видео возможно сумбурно, но показан вариант применения.

Как ссылаться на отдельную ячейку таблицы в Power Query?

Как ссылаться на отдельную
ячейку таблицы в Power Query?

Адаптированный перевод текста Referencing Individual Cell Values From Tables In Power Query, автор Chris Webb (Крис Вебб).

Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И мы представляем первую статью.

Данная статья относится к надстройке Power Query в Excel 2010/2013, к группе Скачать и преобразовать вкладки Данные в Excel 2016, и к экрану Get Data в Power BI Desktop. Термин «Power Query» используется в том же контексте, что и в предыдущих статьях.

Иногда, при работе с данными таблиц Power Query возникает необходимость получить значение из одной ячейки таблицы. В статье показано, как это сделать через Редактор запросов и Редактор кода. Также подробно обсуждаются дополнительные возможности, доступные в последнем. Кстати, эта тема частично раскрыта в главе М книги Power Query за авторством Криса Вебба, но к настоящему моменту она несколько устарела.

Ссылка на значение ячейки в Редакторе запросов

Предположим, что источник данных – таблица Excel, подобная такой:

Импортируем её в Power Query. Чтобы получить данные из ячейки второго столбца второй строки щелкаем по ней ПКМ и выбираем пункт Детализация углублением:

Готово, мы получили 5 в ответе:

Обратите внимание, что это значение 5 не то же самое, что и в ячейке таблицы. Запрос Power Query может вернуть любой тип данных. В данном случае будет возвращено значение целочисленного типа, а не значение типа таблица. Если вывести результаты этого запроса на лист Excel, то мы увидим отформатированную таблицу. Но если использовать результаты этого запроса в качестве входных данных для другого (например, как фильтр в SQL-запросе), то иметь данные целочисленного типа удобнее, чем таблицу из одной строки и столбца.

Ссылка на значение ячейки в Редакторе кода

Вот код для действий на скриншотах выше. Вероятно, вы догадались как он работает.

let  
Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
"Измененный тип" = Table.TransformColumnTypes(  
Источник,  
{{"poleA", Int64.Type},  
{"poleB", Int64.Type},  
{"poleC", Int64.Type}}  
),  
poleB = #"Измененный тип"{1}[poleB] in poleB

Рассмотрим эти три шага: – Источник – получаем данные из таблицы Excel
– «Измененный тип» – устанавливаем тип данных для трех столбцов в целочисленный
– poleB – возвращает значение ячейки из второй строки столбца В (строки начинаются с 0).

Код позволяет ссылаться на отдельные ячейки таблицы, используя систему координат из имени столбца и номера строки, еще раз повторим, что нумерация строк начинается с нуля. Поэтому выражение:

«Измененный тип»{1}[poleB]

вернёт значение ячейки из второй строки столбца poleB., т.е 5. Аналогично, выражение

«Измененный тип»{0}[poleC]

вернёт значение 3, соответствующее первой строке столбца poleC.

Отметим, что ссылки на столбец и строку могут идти в любом порядке, и выражение #»Измененный тип»{1}[poleB] вернёт то же самое, что и

«Измененный тип»[poleB]{1}

Но в некоторых случаях, как вы скоро увидите, порядок строк и столбцов может быть важен.

Ссылка на отсутствующие строку или столбец

Что произойдёт если использовать ссылку на отсутствующий столбец и/или строку? Конечно, мы получим сообщение об ошибке. Вернёмся к нашему примеру и запишем:

«Измененный тип»{4)[poleB]

и

«Измененный тип»{1)[poleD]

Оба выражения вернут ошибку, т. к. в таблице нет 5-ой строки и столбца poleD.

Однако вместо ошибки можно получить значение NULL, используя оператор «?» после ссылки. Например, выражение

«Измененный тип»{1}[poleD]?

вернёт null вместо сообщения об ошибке:

Но будьте осторожны! Выражение

«Измененный тип»{4}?[poleB]

по-прежнему возвращает ошибку, но не потому что отсутствует пятая строка, а потому что ссылка на пятую строку вернёт null, а у него нет столбца poleB.

Решением может быть изменение порядка ссылок:

«Измененный тип»[poleB]{4}?

или применение оператора «?» для обеих ссылок:

«Измененный тип»{4}?[poleB]?

К сожалению, применение оператора «?» не позволит избежать ошибок, если использовать отрицательные значения в ссылках строк.

Эффект первичного ключа

Знаете ли вы, что таблицы Power Query могут содержать первичный ключ (т. е. один или несколько столбцов, значения которых уникально идентифицируют каждую строку), определяемый самой надстройкой? Нет? Неудивительно, это вовсе не очевидно из пользовательского интерфейса. Однако, существует несколько ситуаций, когда Power Query определяет первичный ключ для таблицы, в том числе:

  1. Когда импортируются данные из таблицы реляционной базы данных, подобной SQL Server, и таблица уже имеет первичный ключ.
  2. Когда используется кнопка Удалить повторения чтобы убрать повторяющиеся значения из столбца или столбцов, скрытно вызывается функция Table.Distinct()
  3. Когда к таблице применяется функция Table.AddKey()

Наличие первичного ключа влияет на работу функционала пункта Детализация углублением, и даёт другой способ ссылок на отдельные ячейки.

Рассмотрим следующую таблицу Excel, в основном такую же, что приводилась выше, но с новым столбцом, который однозначно идентифицирует каждую строку.

Если вы загрузите таблицу в Power Query, щелкните ПКМ по заголовку столбца poleKey и выберете пункт Удалить повторения, то установите этот столбец первичным ключом.

(Кстати, можно использовать функцию Table.Keys(), чтобы увидеть, какие ключи определены для таблицы Power Query).

Убрав дубликаты, повторим действия с пунктом Детализация углублением. Получим следующее:

let  
Источник = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],  
"Измененный тип" = Table.TransformColumnTypes(  
Источник,  
{{"poleA", Int64.Type},  
{"poleB", Int64.Type},  
{"poleC", Int64.Type}}),  
"Удаленные дубликаты" = Table.Distinct(  
"Измененный тип",   
{"poleKey"}),  

"Строка 2" = "Удаленные дубликаты"{[poleKey="Строка 2"]}[poleB]  

in  
"Строка 2

Обратите внимание на последний шаг, это важно! Вместо ссылки по номеру строки идёт ссылка по первичному ключу.

«Удаленные дубликаты»{[poleKey=»Строка 2»]}[poleB]

Можно продолжать использовать нотацию на основе номера строки, но если таблица имеет столбец с первичными ключами, то можно использовать нотацию с первичным ключом.

Замечания напоследок о производительности

Возможность ссылок на отдельные значения невероятно полезна в определенных типах запросов и расчётов. Однако стоит помнить, что зачастую существует несколько способов решения задачи, и не все они одинаково хороши.
Одно очевидное применение техники описанной в статье – запись предыдущих вычислений там, где необходимы ссылки на значения предыдущей строки таблицы. Но по опыту известно, что запись расчетов, использующих ссылки на строку/столбец не даёт осуществлять Query Folding («квэри фолдинг» — термин, означающий передачу тяжелых операций по обработке запросов на сторону сервера при работе с совместимой базой данных, на текущий момент это MS SQl, прим. пер.), и ведет к снижению производительности.
Возможно, альтернативные подходы (некоторые описаны в статьях Implementing Common Calculations In Power Query и Join Conditions in Power Query, Part 2: Events-In-Progress, Performance and Query Folding) будут лучшим выходом.

Нет каких-то общих правил, которые можно посоветовать, вы должны сами попробовать разные способы.

Выполнение SQL-запросов к таблице Excel в рабочей книге с помощью макроса VBA

Задавать вопрос

спросил

Изменено
1 год, 4 месяца назад

Просмотрено
192к раз

Я пытаюсь создать макрос Excel, который даст мне следующую функцию в Excel:

 = SQL ("ВЫБЕРИТЕ заголовок_1 ИЗ Таблицы 1, ГДЕ заголовок_2 = 'foo'")

Позволяет мне искать (и, возможно, даже вставлять) данные в таблицы моей книги с помощью SQL-запросов.

Вот что я сделал до сих пор:

 Sub SQL()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Расширенные свойства=""Excel 12.0;HDR=Да;IMEX=1"";"
Установите cn = CreateObject("ADODB.Connection")
Установить rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "ВЫБЕРИТЕ * ИЗ [Лист1 $ A1: G3]"
rs.Open strSQL, cn
Debug.Print rs.GetString
Конец сабвуфера

Мой скрипт прекрасно работает с жестко закодированными диапазонами, такими как во фрагменте выше. Он также очень хорошо работает со статическими именованными диапазонами.

Однако он не будет работать ни с динамическими именованными диапазонами, ни с ИМЕНАМИ ТАБЛИЦ, что для меня наиболее важно.

Самый близкий ответ, который я нашел, это этот парень, страдающий от того же недуга:
http://www.ozgrid.com/forum/showthread.php?t=72973

Кто-нибудь поможет?

Редактировать

Пока я готовил это, я могу использовать полученное имя в своих SQL-запросах. Ограничение в том, что мне нужно знать, на каком листе находятся таблицы. Можем ли мы что-то с этим сделать?

 Функция getAddress()
    мойАдрес = Заменить(Листы("Лист1").Диапазон("Таблица1").адрес, "$", "")
    мой адрес = "[Лист1$" и мой адрес & "]"
    получитьадрес = мой адрес
Конечная функция

Спасибо!

  • sql
  • vba
  • первенствовать

4

Одна вещь, которую вы можете сделать, это получить адрес динамического именованного диапазона и использовать его в качестве входных данных в вашей строке SQL. Что-то вроде:

 Sheets("shtName").range("namedRangeName").Address

Который выдаст строку адреса, что-то вроде $A$1:$A$8

Редактировать:

Как я сказал в своем комментарии ниже, вы можете динамически получить полный адрес (включая имя листа) и либо используйте его напрямую, либо проанализируйте имя листа для последующего использования:

 ActiveWorkbook. Names.Item("namedRangeName").RefersToLocal

В результате получается строка вида =Sheet1!$C$1:$C$4 . Таким образом, для вашего примера кода выше ваш оператор SQL может быть

 strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)
strSQL = "ВЫБЕРИТЕ * ИЗ [strRangeAddress]"

4

 Публичная функция GetRange(ByVal sListName As String) As String
Dim oListObject как ListObject
Dim wb как рабочая книга
Dim ws As рабочий лист
Установите wb = Эта рабочая книга
Для каждого ws в wb.Sheets
    Для каждого oListObject в ws.ListObjects
        Если oListObject.Name = sListName Тогда
            GetRange = "[" & ws.Name & "$" & Replace(oListObject.Range.Address, "$", "") & "]"
        Выход из функции
        Конец, если
    Следующий oListObject
Следующий мс
Конечная функция

В вашем SQL используйте это так:

 sSQL = "Выберите * из" & GetRange("NameOfTable") & ""

2

Основание на подпрограмме Джоан-Диего Родригеса с подходом Джорди и частью кода Яцека Котовски. Эта функция преобразует любое имя таблицы для активной рабочей книги в пригодный для использования адрес для SQL-запросов.

Примечание для MikeL: добавление «[#Все]» включает заголовки, позволяющие избежать проблем, о которых вы сообщили.

 Функция getAddress(byVal sTableName as String) as String
    С диапазоном (sTableName и "[#All]")
        getAddress= "[" & .Parent.Name & "$" & .Address(False, False) & "]"
    Конец с
Конечная функция

1

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

 Функция currAddressTest (dataRangeTest As Range) As String
    currAddressTest = ActiveSheet.Name & "$" & dataRangeTest.Address(False, False)
Конечная функция

Когда я выбираю аргумент источника данных для своей функции, он преобразуется в формат Sheet1$A1:G3. Если Excel изменит его на ссылку Table1[#All] в моей формуле, функция все еще будет работать правильно

Затем я использовал ее в вашей функции (пытался воспроизвести и добавить еще один аргумент для внедрения в WHERE. ..

 Функция SQL( dataRange как диапазон, CritA как строка)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim currAddress как строка
currAddress = ActiveSheet.Name & "$" & dataRange.Address(False, False)
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Расширенные свойства=""Excel 12.0;HDR=Да;IMEX=1"";"
Установите cn = CreateObject("ADODB.Connection")
Установить rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT * FROM [" & currAddress & "]" & _
         "ГДЕ [A] = '" & CritA & "' " & _
         "ЗАКАЗАТЬ ПО 1 АСЦ"
rs.Open strSQL, cn
SQL = rs.GetString
Конечная функция

Надеюсь, ваша функция будет развиваться дальше, я нахожу ее очень полезной. Хорошего дня!

Просто отвечая на вторую часть вашего вопроса о получении имени листа, на котором находится таблица:

 Dim name as String
имя = Диапазон ("Таблица1"). Рабочий лист. Имя

Редактировать:

Чтобы было понятнее: кто-то предложил использовать Range на объекте Sheet. В этом случае вам не нужно; Диапазон, в котором находится таблица, можно получить, используя имя таблицы; это имя доступно на протяжении всей книги. Таким образом, вызов Range в одиночку работает хорошо.

Просто хотел добавить сюда, что вы можете просто сделать свой диапазон именованным диапазоном и использовать его в своем запросе (или весь лист, если у вас есть одна таблица на листе).

Таким образом, вы можете использовать:

SELECT * FROM MyNamedRange

OR

SELECT * FROM [Sheet1$]

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

В одном из моих более сложных запросов ниже все диапазоны, на которые я ссылаюсь, являются именованными диапазонами:

 SELECT Unit_Type AS [Тип единицы], COUNT(*) AS [Количество единиц], SQFT, Депозит AS [Депозит{ $}], Rent AS [Базовая арендная плата{$}], SUM(RENT) AS [Общая базовая арендная плата{$}], SUM(MR) AS [Общая рыночная арендная плата{$}] FROM
(
    ВЫБЕРИТЕ f1. Unit_Code, Unit_Type, SQFT, Rent, Deposit, SWITCH (MR IS NULL, Rent, MR IS NOT NULL, MR) AS MR FROM
    (
        ВЫБЕРИТЕ t2.Unit_Code, t2.Unit_Type, SQFT, Аренда, Депозит ОТ
        (
            ВЫБЕРИТЕ РАЗЛИЧНЫЕ Unit_Code, Unit_Type
            ОТ Коммуникаций
            ГДЕ Unit_Code НЕ КАК "%WAIT%" И Exclude=0
        ) т2
        ЛЕВОЕ СОЕДИНЕНИЕ
        (
            ВЫБЕРИТЕ UnitType_Code, SQFT, Аренда, Депозит
            ИЗ ResUnitTypes
        ) t1 ON (t1.UnitType_Code = t2.Unit_Type)
    ) f1
    ЛЕВОЕ СОЕДИНЕНИЕ
    (
        ВЫБЕРИТЕ Unit_Code, SUM(Current_Charge) AS MR
        ОТ ResUnitAmenities
        ГДЕ Unit_Code НЕ КАК "% WAIT%"
        СГРУППИРОВАТЬ ПО Unit_Code
    ) f2 вкл (f1.Unit_Code = f2.Unit_Code)
)
СГРУППИРОВАТЬ ПО Unit_Type, SQFT, Депозит, Аренда

Привет, недавно изучил это и обнаружил проблемы со ссылкой на именованную таблицу (объект списка) в Excel

если вы поместите суффикс ‘$’ в имя таблицы, все в порядке в мире

 Sub testSQL()
    Dim cn As ADODB. Connection
    Dim rs As ADODB.Recordset
    ' Объявить переменные
    strFile = ThisWorkbook.FullName
    ' построить строку подключения
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Расширенные свойства=""Excel 12.0;HDR=Да;IMEX=1"";"
    ' создать соединение и объекты набора записей
    Установите cn = CreateObject("ADODB.Connection")
    Установить rs = CreateObject("ADODB.Recordset")
    ' открыть соединение
    cn.Open strCon
    ' построить SQL-запрос
    strSQL = "SELECT * FROM [TableName$], где [ColumnHeader] = 'wibble';"
    ' выполнить SQL-запрос
    rs.Open strSQL, cn
    Debug.Print rs.GetString
    ' тесная связь
    rs.Закрыть
    сн.Закрыть
    Установить rs = Ничего
    Установите cn = Ничего
Конец сабвуфера

3

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

Как использовать sql-запрос и получить данные с одного листа Excel на другой в VBA?

спросил

Изменено
3 года, 10 месяцев назад

Просмотрено
9к раз

У меня есть две книги Excel. У одного есть список целевых клиентов, а у другого есть таблица данных о продажах. Я хотел бы использовать vba и написать запрос sql, чтобы получить историю продаж для конкретных клиентов и переместить эту историю продаж в новый ListObject в рабочей книге целевых клиентов. Как лучше всего это сделать?

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

Это пример кода, который у меня сейчас есть.

 Общедоступная подписка GetSales()
Dim targetList как строка
'Получить список целевых клиентов
Диапазон("A2").Выбрать
Диапазон (Выделение, Выбор. Конец (xlDown)). Выберите
счетчик = Выбор.Строки.Количество
targetList = "'" & Диапазон ("A2"). Значение & "'"
Для x = 2 В противовес
    targetList = targetList + ",'" + CStr(Range("A" & CStr(3)).Value) + "'"
Следующий х
'Запрос, который я хочу выполнить
'SalesData — это ListObject в рабочей книге "Данные о продажах".
sqlQuery = "Выберите * From SalesData WHERE Customer IN" & targetList
    С ActiveWorkbook.Connections("SalesData").OLEDBConnection
        .BackgroundQuery = Истина
        .CommandText = sqlQuery
        .CommandType = кслкмдскл
        .Connection = Array(здесь что-то есть??)
        .Рефрешонфилеопен = ложь
        .SavePassword = Ложь
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = Ложь
    Конец с
'Вернуть запрошенные данные о продажах в объект списка _
'на новом листе в книге "Целевые клиенты"
ActiveWorkbook. Worksheets.Add().Name = "История продаж"
Рабочие листы («История продаж»). Активировать
С ActiveSheet.ListObjects.Add '(результаты запроса)
    .DisplayName = "История продаж"
Конец с
Конец сабвуфера
  • excel
  • vba
  • oledbconnection
  • listobject

2

Ниже показано простое подключение и запрос к другой книге.

 Sub simple_Query()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    dbpath = "ваш путь сюда"
    Установите cn = CreateObject("ADODB.Connection")
    Установить rs = CreateObject("ADODB.Recordset")
    strSQL = "ВЫБЕРИТЕ * ИЗ [Лист1$]"
    Установите vNewWB = Workbooks.Add 'или .CopyFromRecordset rs, чтобы открыть книгу
    connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & dbpath & ";Extended Properties=""Excel 12.0; HDR=YES; IMEX=1""; Mode=Read;"
    cn.Open connstr
    Установите rs = cn.Execute(CommandText:=strSQL)
    vNewWB.Sheets(1).Range("A2").CopyFromRecordset rs
    Для intcolIndex = 0 Для rs. Fields.Count - 1
        Диапазон ("A1"). Смещение (O, intcolIndex). Значение = rs.Fields (intcolIndex). Имя
    Следующий
    rs.Закрыть
    сн.Закрыть
    Установите cn = Ничего
    Установить rs = Ничего
Конец сабвуфера

1

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.