Excel sql запрос к листу vba: Как сформировать SQL запросы в Excel? — Разработка на vc.ru

Как сформировать SQL запросы в Excel? — Разработка на vc.ru

Представьте себе ситуацию, Вы получили целевую выборку из одной базы данных, но для полноты картины, как всегда, нужны дополнительные данные. Проблема может быть в том, что нужная информация хранится в другой базе данных и возможности создать на ней свою таблицу нет, подключиться используя link тоже нельзя, да и количество элементов, по которым нужно получить данные, несколько больше, чем допустимое на данном источнике. Вот и получается, что возможность написать SQL запрос и получить нужные данные есть, но написать придется не один запрос, а потом потратить время на объединение полученных данных.

22 752
просмотров

Выйти из подобной ситуации поможет Excel.

Уверен, что ни для кого не секрет, что MS Excel имеет встроенный модуль VBA и надстройки, позволяющие подключаться к внешним источникам данных, то есть по сути является мощным инструментом для аналитики, а значит идеально подходит для решения подобных задач.

Для того чтобы обойти проблему, нам потребуется таблица с целевой выборкой, в которой содержатся идентификаторы, по которым можно достаточно корректно получить недостающую информацию (это может быть уникальный идентификатор, назовем его ID, или набор из данных, находящихся в разных столбцах), ПК с установленным MS Excel, и доступом к БД с недостающей информацией и, конечно, желание получить ту самую информацию.

Создаем в MS Excel книгу, на листе которой размещаем таблицу с идентификаторами, по которым будем в дальнейшем формировать запрос (если у нас есть уникальный идентификатор, для обеспечения максимальной скорости обработки таблицу лучше представить в виде одного столбца), сохраняем книгу в формате *.xlsm, после чего приступаем к созданию макроса.

Через меню «Разработчик» открываем встроенный VBA редактор и начинаем творить.

Sub job_sql() — Пусть наш макрос называется job_sql.

Пропишем переменные для подключения к БД, записи данных и запроса:

Dim cn As ADODB. Connection
Dim rs As ADODB.Recordset
Dim sql As String

Опишем параметры подключения:

sql = «Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=Storoge.company.ru\ Storoge.»

Объявим процедуру свойства, для присвоения значения:

Set cn = New ADODB.Connection
cn.Provider = » SQLOLEDB.1″
cn.ConnectionString = sql
cn.ConnectionTimeout = 0
cn.Open

Вот теперь можно приступать непосредственно к делу.

Организуем цикл:

For i = 2 To 1000

Как вы уже поняли конечное значение i=1000 здесь только для примера, а в реальности конечное значение соответствует количеству строк в Вашей таблице. В целях унификации можно использовать автоматический способ подсчета количества строк, например, вот такую конструкцию:

Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count

Тогда открытие цикла будет выглядеть так:

For i = 2 To LastRow

Как я уже говорил выше MS Excel является мощным инструментом для аналитики, и возможности Excel VBA не заканчиваются на простом переборе значений или комбинаций значений. При наличии известных Вам закономерностей можно ограничить объем выгружаемой из БД информации путем добавления в макрос простых условий, например:

If Cells(i, 2) = «Ваше условие» Then

Итак, мы определились с объемом и условиями выборки, организовали подключение к БД и готовы формировать запрос. Предположим, что нам нужно получить информацию о размере ежемесячного платежа [Ежемесячный платеж] из таблицы [payments].[refinans_credit], но только по тем случаям, когда размер ежемесячного платежа больше 0

sql = «select [Ежемесячный платеж] from [PAYMENTS].[refinans_credit] » & _
«where [Ежемесячный платеж]>0 and [Номер заявки] ='» & Cells(i, 1) & «‘ «

Если значений для формирования запроса несколько, соответственно прописываем их в запросе:

«where [Ежемесячный платеж]>0 and [Номер заявки] = ‘» & Cells(i, 1) & «‘ » & _
» and [Дата платежа]='» & Cells(i, 2) & «‘»

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

Cells(i, 3) = sql

в третьем столбце записываются запросы.

Выполняем SQL запрос:

Set rs = cn.Execute(sql)

А чтобы хоть как-то наблюдать за выполнением макроса выведем изменение i в статус-бар

Application.StatusBar = «Execute script …» & i
Application.ScreenUpdating = False

Теперь нам нужно записать полученные результаты. Для этого будем использовать оператор Do While:

j = 0
Do While Not rs.EOF
For ii = 0 To rs.Fields.Count — 1
Cells(i, 4 + j + ii) = rs.Fields(0 + ii) ‘& «;»

Указываем ячейки для вставки полученных данных (4 в примере это номер столбца с которого начинаем запись результатов)

Next ii
j = j + rs.Fields.Count
s.MoveNext
Loop
rs.Close
End If

— закрываем цикл If, если вводили дополнительные условия

Next i
cn.Close
Application.StatusBar = «Готово»
End Sub

— закрываем макрос.

В дополнение хочу отметить, что данный макрос позволяет обращаться как к БД на MS SQL так и к БД Oracle, разница будет только в параметрах подключения и собственно в синтаксисе SQL запроса.

В приведенном примере для авторизации при подключении к БД используется доменная аутентификация.

А как быть если для аутентификации необходимо ввести логин и пароль? Ничего невозможного нет. Изменим часть макроса, которая отвечает за подключение к БД следующим образом:

sql = «Provider= SQLOLEDB.1;Password=********;User ID=********;Data Source= Storoge.company.ru\ Storoge;APP=SFM»

Но в этом случае при использовании макроса возникает риск компрометации Ваших учетных данных. Поэтому лучше программно удалять учетные данные после выполнения макроса. Разместим поля для ввода пароля и логина на листе и изменим макрос следующим образом:

sql = «Provider= SQLOLEDB.1;Password=» & Sheets(«Лист аутентификации»).TextBox1.Value & «;UserЛист аутентификации «).TextBox2.Value & «;Data Source= Storoge.company.ru\ Storoge;APP=SFM»

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

Sheets(«Выгрузка»).TextBox1.Value = «« Sheets(»Выгрузка«).TextBox2.Value = »»

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

Вот такое вполне жизнеспособное решение, позволяющее сократить трудозатраты при получении и обработке данных, я использую. Надеюсь мой опыт применения SQL запросов в Excel будет полезен и вам в решении текущих задач.

Excel VBA. SQL-запросы в подключениях — Эффективная работа в MS Office

UPDATE 21.10.15 Добавил «обратный» макрос — VBA в SQL и макрос для доступа к строке запроса SQL

Некоторое время назад я прошел несколько курсов по SQL. И мне было очень интересно — какую часть из мощного инструмента под названием T-SQL можно применять без использования SQL-Server (не дают мне сервачек под мои нужды, хнык-хнык).

Итак… Начнем с простого — подключение через Query Table в VBA. Можно записать через макрорекордер — для этого нужно создать подключение через Microsoft Query.

Выбираем Excel Files, указываем путь к файлу (пытаясь при этом не ругать разработчиков за интерфейс из 90х годов), фильтруем как-угодно поля. Нам сейчас это не важно — главное получить код, который дальше можно будет корректировать.

Должно получится что-то вроде этого:


Sub Макрос1()
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=D:\Dropbox\Excel\тест excel_SQL-2015.xlsx;DefaultDir=D:\Dropbox\Excel;DriverId=1046;MaxBufferSize=2048;Page" _
        ), Array("Timeout=5;")), Destination:=Range("$A$1")).QueryTable
        .CommandType = 0
        .CommandText = Array( _
        "SELECT Продажи.F2, Продажи.F3" & Chr(13) & "FROM `D:\Dropbox\Excel\тест excel_SQL-2015.xlsx`.Продажи Продажи" _
        )
        . RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица_Запрос_из_Excel_Files"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Строчка .CommandText = «SELECT…» — отвечает за SQL запрос. Если хотя бы немного почитать поисковую выдачу google по запросу QueryTable можно упростить код до следующего:


Sub CopyFromRecordset_To_Range()
  
  DBPath = "C:\InputData.xlsx"
  sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
  Conn.Open sconnect
  sSQLSting = "SELECT * FROM [Sheet1$]"

  rs. Open sSQLSting, Conn
  Set QT1 = ActiveSheet.QueryTables.Add(rs, Range("A1"))
  QT1.Refresh

  rs.Close
  Conn.Close

End Sub

Теперь начинаем копаться глубже — какого уровня запросы можно строить из VBA. Самые-самые базовые, основные конструкции — все работает, все ок.

Заполнение нового столбца одинаковым значением


SELECT  'YTikhonov', * 
FROM    [Sheet1$]

Переименование столбцов


SELECT  [Advertiser] AS 'Рекламодатель', [Quantity] AS 'Количество'
FROM    [Sheet1$]

Фильтрация записей


SELECT  * 
FROM    [Sheet1$]
WHERE   [Year] = 2014

Сортировка


SELECT   * 
FROM     [Sheet1$]
ORDER BY [Advertiser] DESC

Агрегация записей


SELECT   [Advertiser], Sum([Cost]) 
FROM     [Sheet1$]
GROUP BY [Advertiser]

Работа с датой

Дату можно впрямую через конструкцию


[SomeDateField] = {ts '2015-01-01 00:00:00'}

Но я люблю отталкиваться от текущей даты. За пару текущая дата-время отвечает функция SYSDATETIME() и она может вернуть в том числе текущий день. Для этого нужна еще одна функция  — CONVERT(type,value)


SELECT CONVERT(date,SYSDATETIME())

С функцией DATEFROMPARTS строка запроса в Excel почему-то не дружит, поэтому придется использовать костыли функцию DATEADD:


DATEADD(minute, 59, DATEADD(hour, 23, DATEADD(month, MONTH(SYSDATETIME())+1, DATEADD(year, YEAR(SYSDATETIME()) - 1900, 0))))-1

Эта строчка в любой день октября 2015 вернет значение — 30.11.15 23:59

А теперь — немного best practice!

Объединение + Агрегация + Join + Подзапросы. И самое интересное — подключение к нескольким источникам:


SELECT [Year], O.Numbers, SCost, SVolume, SQuantity FROM
 (
  SELECT [Year], Month, SUM([Cost RUB]) AS SCost, SUM(Volume) AS SVolume, SUM(Quantity) AS SQuantity FROM
   (
     SELECT Advertiser, 2013 as [Year], Month, [Cost RUB], Quantity, Volume
     FROM [N:\GK\Radio\Маркетинг\Служебный\2013. xlsb].[Мониторинг$]
      UNION
     SELECT Advertiser, 2014 as [Year], Month, [Cost RUB], Quantity, Volume
     FROM [N:\GK\Radio\Маркетинг\Служебный\2014.xlsb].[Мониторинг$]
       UNION
     SELECT Advertiser, 2015 as [Year], Month, [Cost RUB], Quantity, Volume
     FROM [N:\GK\Radio\Маркетинг\Служебный\2015.xlsb].[Мониторинг$]
   )
   WHERE [Advertiser] = 'METRO GROUP'
   GROUP BY [Year], Month
 ) as T INNER JOIN [C:\test\Month.xlsb].[Test$] AS O
ON T.[Month] = O.[Month]

Одна проблема — если осуществлять такого вида запрос для соединения нескольких Excel-файлов, он будет выполняться достаточно медленно. У меня вышло порядка 2 минут. Но не стоит думать что это бесполезно — если подобные запросы выполнять при подключении к SQL-серверу, то время обработки будет 1-2 секунды (само собой, все зависит от сложности запроса, базы, и прочие прочие факторы).

Бонусы

Формировать более-менее сложный запрос SQL вручную в VBA мягко говоря неудобно.  Поэтому я написал мини-макрос, который берет информацию из буфера обмена, и возвращает туда строчки для вставки в VBE.


'работа с буфером обмена http://excelvba.ru/code/clipboard
Private Function ClipboardText() ' чтение из буфера обмена
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        ClipboardText = .GetText
    End With
End Function
 
Private Sub SetClipboardText(ByVal txt$) ' запись в буфер обмена
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText txt$
        .PutInClipboard
    End With
End Sub

Public Sub SQL_String_To_VBA()
  
    Dim sInput As String, sOut As String
    Dim ArrInput, i As Integer
    
    Dim cIdent As Integer: cIdent = 1 'Count of tabs
    Dim sVar As String: sVar = "strSQL" 'Name of variable
      
    sInput = ClipboardText()
    
    ArrInput = Split(sInput, Chr(13))
    
    For i = LBound(ArrInput) To UBound(ArrInput)
        sOut = sOut & sVar & " = " & sVar & " & " & Chr(34)
        sOut = sOut & String(cIdent, Chr(9))
        sOut = sOut & Replace(ArrInput(i), Chr(10), "")
        sOut = sOut & Chr(34) & "& chr(10)" & Chr(10)
    Next i
    
    SetClipboardText (sOut)

End Sub

Public Sub VBA_String_To_SQL()
 
    Dim sInput As String, sOut As String
    Dim ArrInput, i As Integer, sTemp
 
    sInput = ClipboardText()
 
    ArrInput = Split(sInput, Chr(10))
 
    For i = LBound(ArrInput) To UBound(ArrInput)
      sTemp = Replace(ArrInput(i), "& chr(10)", "")
      If Right(sTemp, 1) = " " Then sTemp = Left(sTemp, Len(sTemp) - 1)
      If Right(sTemp, 1) = Chr(34) Then sTemp = Left(sTemp, Len(sTemp) - 1)
 
      If Len(sTemp) > 0 Then
        sTemp = Right(sTemp, Len(sTemp) - InStr(1, sTemp, Chr(34)))
        sOut = sOut & Chr(10) & sTemp
      End If
    Next i
 
    SetClipboardText (sOut)

End Sub

Сами запросы просто и удобно создавать, например, используя Notepad++. Создали многострочный запрос SQL, копируете его в буфер обмена, запускаете макрос и вуаля — в буфере обмена строчки кода, готовые для вставки в ваши макросы. При желании вы можете настроить название переменной и количество табуляций.

И еще один небольшой бонус. Если у вас есть отчет по менеджерам/руководителям, построенный на запросах, то вам наверняка потребуется получать доступ к строке запроса через VBA. Сделать это можно через замечательную команду .CommandText — работает на чтение и запись. Мне для формирования отчета на 25 человек очень пригодился.


Public Sub ReplaceCommandText()
 
Dim con As WorkbookConnection
Dim sTemp As String
 
  For Each con In ActiveWorkbook.Connections
    sTemp = con.ODBCConnection.CommandText
    con.ODBCConnection.CommandText = sTemp
    con.Refresh
  Next con
 
End Sub

PS Ссылка с ответом на вопрос — как вставить данные из Excel в SQL
https://www.simple-talk. com/sql/t-sql-programming/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask/

 

Приятного использования!

Понравилось это:

Нравится Загрузка…

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

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

 =SQL("SELECT heading_1 FROM Table1 WHERE heading_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 server — Использование Excel VBA для запуска SQL-запроса

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

спросил

Изменено
2 года, 9 месяцев назад

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

Я новичок в SQL и VBA. Я написал SQL-запрос, который я хотел бы вызывать и запускать из подпрограммы VBA в книге Excel, а затем переносить результаты запроса в книгу. Я нашел некоторые подпрограммы в Интернете (stackoverflow и другие места), которые утверждают, что делают это, но у меня возникли проблемы с их пониманием, поскольку они не содержат объяснений. Например, вот саб, который я нашел в Интернете:

 Sub ConnectSqlServer()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString как строка
    ' Создайте строку подключения.
    sConnString = "Provider=SQLOLEDB;Источник данных=INSTANCE\SQLEXPRESS;" & _
                  "Исходный каталог=MyDatabaseName;" & _
                  "Интегрированная безопасность=SSPI;"
    ' Создайте объекты Connection и Recordset.
    Установить conn = новый ADODB.Connection
    Установить rs = новый ADODB.Recordset
    'Открываем соединение и выполняем.
    conn.Open sConnString
    Установите rs = conn.Execute("SELECT * FROM Table1;")
    'Проверьте, у нас есть данные. 
    Если Не rs.EOF Тогда
        ' Результат передачи.
        Листы(1).Range("A1").CopyFromRecordset rs
    ' Закрыть набор записей
        rs.Закрыть
    Еще
        MsgBox «Ошибка: записи не возвращены.», vbCritical
    Конец, если
    ' Очистить
    Если CBool(conn.State и adStateOpen), то conn.Close
    Установить соединение = Ничего
    Установить rs = ничего
Конец сабвуфера

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

Я надеюсь, что этот вопрос не слишком запутан, и я ценю вашу помощь!

  • sql
  • sql-сервер
  • vba
  • excel

10

Ниже приведен код, который я сейчас использую для извлечения данных из MS SQL Server 2008 в VBA. Вы должны убедиться, что у вас есть правильная ссылка на ADODB [ Редактор VBA->Инструменты->Ссылки ] и убедитесь, что у вас отмечена Microsoft ActiveX Data Objects 2. 8 Library , которая является второй снизу отмеченной строкой (я использую Excel 2010 в Windows 7; вы можете имеют немного другую версию ActiveX, но она все равно будет начинаться с Microsoft ActiveX):

Подмодуль для подключения к MS SQL с удаленным хостом и именем пользователя/паролем 

 Sub Download_Standard_BOM()
 'Инициализирует переменные
 Dim cnn As New ADODB.Connection
 Dim сначала как новый ADODB.Recordset
 Dim ConnectionString как строка
 Dim StrQuery как строка
 'Настройте строку подключения для доступа к базе данных MS SQL
 'Обязательно измените:
 '1: ПАРОЛЬ
 '2: ИМЯ ПОЛЬЗОВАТЕЛЯ
 '3: УДАЛЕННЫЙ_IP_АДРЕС
 '4: БАЗА ДАННЫХ
 ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;ID пользователя=USERNAME;Источник данных=REMOTE_IP_ADDRESS;Использовать процедуру для подготовки=1;Автоматический перевод=True;Размер пакета=4096;Использовать шифрование для данных=False;Тегировать с сопоставлением столбцов, когда это возможно=False;Исходный каталог=BASE DATABASE"
 'Открывает соединение с базой данных
 cnn.