VBA Несколько SQL-запросов к Excel. Excel sql запрос к листу vba
Решение: Sql запрос к листам excell
SQL VBA Excel
Работа с внешними источниками данных Материалы по работе с внешними источниками данных на примере Excel и SQL. Рассмотрим способы передачи данных между Excel и внешней базой данной на SQL сервере с помощью ADO.
Задача первая. Подключаемся к внешней базе данных.
Для начала надо подключиться к внешней базе данных. Подключение возможно если на компьютере установлен драйвер. Список установленных драйверов для подключения к базам данных на компьютере под управлением Windows: Панель управления\Все элементы панели управления\Администрирование\Источники данных (ODBC) Проверить подключение к базе данных можно простым способом. Создаем пустой файл (например, "текстовый документ.txt"), затем изменяем имя и расширение на .udl (например, "connect.udl"). Двойной клик мышкой по новому файлу, далее приступаете к настройке и проверке подключения к базе данных. После того, как удалось настроить корректное подключение к базе данных, сохраняем файл "connect.udl". Открываем файл "connect.udl" обычным текстовым редактором (например, блокнотом), и видим в строке подключения все необходимые параметры. Про подключение к внешним базам данных можно посмотреть на ресурсе ConnectionStrings . Теперь возвращаемся к нашему VBA для Excel. В редакторе VBA подключаем последнюю версию библиотеки: Microsoft ActiveX Data Objects Library Пример кода: Sub TestConnection()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "" 'Параметры строки подключения
cn.Open 'Открываем подключение
cn.Close 'Закрываем подключение
Set cn = Nothing 'Стираем объект из памяти
End Sub
Задача вторая. Загружаем данные из внешней базы данных на SQL сервере в Excel.
После того, как мы установили подключение к внешней базе данных можно приступать к чтению данных и выводу в Excel. Здесь потребуется знание языка запросов SQL. В результате выполнения SQL запроса к нам возвращается некая таблица с данными в объект RecordSet. Далее из объекта RecordSet можно выгружать данные непосредственно на лист или в сводную таблицу. Пример кода простой процедуры: Sub LoadData()
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cn = New ADODB.Connection
Set rst = New ADODB.Recordset
cn.ConnectionString = "" 'Параметры строки подключения
cn.Open
rst.Open "SELECT TOP 10 * FROM <таблица>", cn 'SQL-запрос, подключение
ActiveSheet.Range("A1").CopyFromRecordset rst 'Извлекаем данные на лист
rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing
End Sub Для удобства работы. Предлагаю создать собственный класс "tSQL" для работы с базой данных. У класса будет одно свойство: Public ConnectionSring As String Для чтения данных напишем метод SelectFrom с параметрами TableName и ws. TableName - это имя таблицы, откуда будем считывать данные и ws - лист Excel, куда будем записывать данные. Public Sub SelectFrom(TableName As String, ws As Worksheet)
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim SQLstring As String
Dim i As Long
Set cn = New ADODB.Connection
Set rst = New ADODB.Recordset
SQLstring = "SELECT * FROM " & TableName
ws.Cells.Clear
cn.ConnectionString = ConnectionSring
cn.Open
rst.Open SQLstring, cn
For i = 1 To rst.Fields.Count
ws.Cells(1, i) = rst.Fields(i - 1).Name
Next i
ws.Range("A2").CopyFromRecordset rst
rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing
SQLstring = Empty
i = Empty
End Sub Пример использования класса tSQL в процедуре Sub mySQL()
Dim ts As tSQL
Set ts = New tSQL
ts.ConnectionSring = '<Строка подключения>
ts.SelectFrom "Название таблицы", ActiveSheet
Set ts = Nothing
End Sub
Задача третья. Загружаем данные из Excel во внешнюю базу данных.
Для записи данных напишем метод InsertInto с параметрами TableName. rHead и rData. TableName - это имя таблицы, куда будем добавлять данные; rHead - диапазон ячеек, с указанием полей; rData - диапазон ячеек с данными, которые будем добавлять. Public Sub InsertInto(TableName As String, rHead As Range, rData As Range)
Dim cn As ADODB.Connection
Dim SQLstring As String
Dim SQLstringH As String
Dim SQLstringV As String
Dim i As Long
Dim j As Long
Dim arrHead()
Dim arrData()
arrHead = rHead.Value
arrData = rData.Value
Set cn = New ADODB.Connection
cn.ConnectionString = ConnectionSring
cn.Open
SQLstringH = "INSERT INTO " & TableName & "("
For j = LBound(arrHead, 2) To UBound(arrHead, 2)
SQLstringH = SQLstringH & " " & arrHead(1, j)
If j < UBound(arrHead, 2) Then
SQLstringH = SQLstringH & ","
Else
SQLstringH = SQLstringH & ")"
End If
Next j
SQLstringH = SQLstringH & " VALUES("
For i = LBound(arrData, 1) To UBound(arrData, 1)
For j = LBound(arrData, 2) To UBound(arrData, 2)
SQLstringV = SQLstringV & " " & arrData(i, j)
If j < UBound(arrHead, 2) Then
SQLstringV = SQLstringV & ","
Else
SQLstringV = SQLstringV & ") "
End If
Next j
SQLstring = SQLstringH & SQLstringV
SQLstringV = Empty
cn.Execute SQLstring
Next i
cn.Close
Set cn = Nothing
SQLstring = Empty
i = Empty
j = Empty
SQLstring = Empty
SQLstringH = Empty
SQLstringV = Empty
Erase arrHead
Erase arrData
End Sub Пример использования класса tSQL в процедуре Sub mySQL()
Dim ts As tSQL
Set ts = New tSQL
ts.ConnectionSring = '<Строка подключения>
ts.InsertInto "Название таблицы", Range("B1:D1"), Range("B8:D300")
Set ts = Nothing
End Sub
Задача четвертая. Управляем внешней базой данных из Excel
Рекомендую использовать запросы в основном для чтения данных из внешней БД. Можно записывать данные в таблицы внешней БД. Но крайне не желательно использовать Excel для управления внешней базой данных, лучше использовать стандартные средства разработки.
Полезные ссылки:
Data from Excel to SQL http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm www.timpust.ru
Изменение формата даты с помощью запроса VBA к SQL MS Excel онлайн
У меня есть вопрос, на который я не смог ответить далеко, моя проблема в том, что у меня есть макрос, который использует SQL-поиск для доступа к базе данных SQL и получения информации. В пределах excel он всегда форсирует даты до DD / MM / YYYY (вы можете форсировать форматирование, но когда он передает его на SQL-стороне вещей, он все равно выдается как DD / MM / YYYY, даже если форматирование визуально выглядит правильно).
Я пробовал ряд изменений в моем коде, чтобы попытаться «заставить» его, но мне не повезло, что я слишком усложняю его или это гораздо сложнее сделать, чем это должно быть? лол.
Я предоставил свой код VB и «Свойства» для того, как выглядит «соединение», которое сделано из использования макроса.
В качестве примечания, формат должен быть YYYY-MM-DD, так как он хранится в базе данных. В настоящее время единственный способ обойти это – использовать «'» infront даты, например, «2013-12-01», чтобы заставить его, или иначе он дойдет до 01/12/2013.
Есть идеи? Я слишком долго ломаю голову.
С уважением, Джейми
Сервер является сервером SQLEXPRESS, если эта информация необходима.
Код ниже:
Sub CustomisedSQLQuery() ' ' SQL Query to allow user customisation. ' ' Dim FileName As String Dim User As String Dim StartDate As String Dim EndDate As String Dim Category As String Dim Confirm As Variant Confirm = MsgBox("Have you made sure that at least one of the search criteria's is populated? If so your excel may crash or you may kill the database.", vbQuestion + vbYesNo, "Wait....") If Confirm = vbNo Then ActiveWorkbook.Sheets("Input Sheet").Activate If Confirm = vbNo Then Exit Sub FileName = Worksheets("Input Sheet").Cells(10, 1) User = Worksheets("Master DATA").Cells(1, 1) StartDate = Worksheets("Input Sheet").Cells(10, 3) EndDate = Worksheets("Input Sheet").Cells(10, 4) Category = Worksheets("Master DATA").Cells(1, 5) MyStr = Format(StartDate, "yyyy/mm/dd") MyStr = Format(EndDate, "yyyy/mm/dd") Sheets("Output Sheet").Select Cells.Select Selection.ClearContents Range("A1").Select With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "ODBC;DRIVER=SQL Server;SERVER=SERVERADDRESS;UID=USERNAME;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=ID" _ , Destination:=Range("$A$1")).QueryTable .CommandText = Array( _ "SELECT DocumentsRead.userID, DocumentsRead.fileName, DocumentsRead.category, DocumentsRead.downloadedByUser, DocumentsRead.timeDownloaded, DocumentsRead.timeR" _ , _ "ead" & Chr(13) & "" & Chr(10) & "FROM EndUsers.dbo.DocumentsRead DocumentsRead" & Chr(13) & "" & Chr(10) & "WHERE (DocumentsRead.fileName Like '" & FileName & "') AND (DocumentsRead.category='" & Category & "') AND (DocumentsRead.timeRead Is Null) " _ , "AND (DocumentsRead.timeDownloaded Between {ts '" & StartDate & " 00:00:01'} An" _ , "d {ts '" & EndDate & " 00:00:01'})") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Input Sheet").Select End SubВходной лист выглядит следующим образом:
Solutions Collecting From Web of "Изменение формата даты с помощью запроса VBA к SQL"
Похоже, ваша проблема заключается в форматировании StartDate / EndDate. Вот ваш код:
MyStr = Format(StartDate, "yyyy/mm/dd") MyStr = Format(EndDate, "yyyy/mm/dd")Вот что я предполагаю, что вы хотите делать:
StartDate = Format(StartDate, "yyyy-mm-dd") EndDate = Format(EndDate, "yyyy-mm-dd")Я считаю, что вы также можете указать, что доступ к строке – это дата, завернув ее в #. Такие как
"and DocumentsRead.timeDownloaded >= #" & StartDate & "#" & vbcrlf & _ "and DocumentsRead.timeDownloaded < #" & EndDate & "#"excel.bilee.com
sql - VBA Несколько SQL-запросов к Excel
Мне нужна помощь.
У меня есть следующий рабочий VBA для импорта данных в Excel для хранимой процедуры.
Задача состоит в том, как изменить код для запуска нескольких хранимых процедур и вставить его на разные страницы.
Пожалуйста помоги.
Sub Macro1() ' Create a connection object. Dim cnPubs As ADODB.Connection Set cnPubs = New ADODB.Connection ' Provide the connection string. Dim strConn As String 'Use the SQL Server OLE DB Provider. strConn = "PROVIDER=SQLOLEDB;" 'Connect to the Pubs database on the local server. strConn = strConn & "DATA SOURCE=PC\SQL2014;INITIAL CATALOG=Option Database;" 'Use an integrated login. strConn = strConn & " INTEGRATED SECURITY=sspi;" 'Now open the connection. cnPubs.Open strConn ' Create a recordset object. Dim rsPubs As ADODB.Recordset Set rsPubs = New ADODB.Recordset With rsPubs ' Assign the Connection object. .ActiveConnection = cnPubs ' Extract the required records. .Open "EXEC sp_Week_Option1_01_Export" ' Copy the records into cell A1 on Sheet1. Sheet4.Range("A2").CopyFromRecordset rsPubs For intColIndex = 0 To rsPubs.Fields.Count - 1 Range("A1").Offset(0, intColIndex).Value = rsPubs.Fields(intColIndex).Name Next ' Tidy up .Close End With cnPubs.Close Set rsPubs = Nothing Set cnPubs = Nothing ' End SubЯ изменил это до следующего, но я считаю, что это не самый эффективный способ. Я думаю о создании цикла. Пожалуйста помоги:
Sub Macro1() ' Create a connection object. Dim cnPubs As ADODB.Connection Set cnPubs = New ADODB.Connection ' Provide the connection string. Dim strConn As String 'Use the SQL Server OLE DB Provider. strConn = "PROVIDER=SQLOLEDB;" 'Connect to the Pubs database on the local server. strConn = strConn & "DATA SOURCE=PC\SQL2014;INITIAL CATALOG=Option Database;" 'Use an integrated login. strConn = strConn & " INTEGRATED SECURITY=sspi;" 'Now open the connection. cnPubs.Open strConn ' Create a recordset object. Dim rsPubs As ADODB.Recordset Dim rsPubs2 As ADODB.Recordset Set rsPubs = New ADODB.Recordset Set rsPubs2 = New ADODB.Recordset With rsPubs ' Assign the Connection object. .ActiveConnection = cnPubs ' Extract the required records. .Open "EXEC sp_Week_Option1_01_Export" ' Copy the records into cell A1 on Sheet1. Sheet4.Range("A2").CopyFromRecordset rsPubs For intColIndex = 0 To rsPubs.Fields.Count - 1 Sheet4.Range("A1").Offset(0, intColIndex).Value = rsPubs.Fields(intColIndex).Name Next ' Tidy up .Close End With With rsPubs2 ' Assign the Connection object. .ActiveConnection = cnPubs ' Extract the required records. .Open "sp_Week_Option1_01_Export_Crosstab" ' Copy the records into cell A1 on Sheet1. Sheet9.Range("A2").CopyFromRecordset rsPubs2 For intColIndex = 0 To rsPubs2.Fields.Count - 1 Sheet9.Range("A1").Offset(0, intColIndex).Value = rsPubs2.Fields(intColIndex).Name Next ' Tidy up .Close End With cnPubs.Close Set rsPubs = Nothing Set cnPubs = Nothing ' End Sub задан Ben 15 мая '15 в 20:50 источник поделитьсяqaru.site
VBA Несколько SQL-запросов к Excel MS Excel онлайн
Мне нужна помощь.
У меня есть следующий рабочий VBA для импорта данных в Excel для хранимой процедуры.
Задача состоит в том, как изменить код для запуска нескольких хранимых процедур и вставить его на разные страницы.
Пожалуйста помоги.
Sub Macro1() ' Create a connection object. Dim cnPubs As ADODB.Connection Set cnPubs = New ADODB.Connection ' Provide the connection string. Dim strConn As String 'Use the SQL Server OLE DB Provider. strConn = "PROVIDER=SQLOLEDB;" 'Connect to the Pubs database on the local server. strConn = strConn & "DATA SOURCE=PC\SQL2014;INITIAL CATALOG=Option Database;" 'Use an integrated login. strConn = strConn & " INTEGRATED SECURITY=sspi;" 'Now open the connection. cnPubs.Open strConn ' Create a recordset object. Dim rsPubs As ADODB.Recordset Set rsPubs = New ADODB.Recordset With rsPubs ' Assign the Connection object. .ActiveConnection = cnPubs ' Extract the required records. .Open "EXEC sp_Week_Option1_01_Export" ' Copy the records into cell A1 on Sheet1. Sheet4.Range("A2").CopyFromRecordset rsPubs For intColIndex = 0 To rsPubs.Fields.Count - 1 Range("A1").Offset(0, intColIndex).Value = rsPubs.Fields(intColIndex).Name Next ' Tidy up .Close End With cnPubs.Close Set rsPubs = Nothing Set cnPubs = Nothing ' End SubЯ изменился до следующего, но я считаю, что это не самый эффективный способ. Я думаю о создании цикла. Пожалуйста помоги:
Sub Macro1() ' Create a connection object. Dim cnPubs As ADODB.Connection Set cnPubs = New ADODB.Connection ' Provide the connection string. Dim strConn As String 'Use the SQL Server OLE DB Provider. strConn = "PROVIDER=SQLOLEDB;" 'Connect to the Pubs database on the local server. strConn = strConn & "DATA SOURCE=PC\SQL2014;INITIAL CATALOG=Option Database;" 'Use an integrated login. strConn = strConn & " INTEGRATED SECURITY=sspi;" 'Now open the connection. cnPubs.Open strConn ' Create a recordset object. Dim rsPubs As ADODB.Recordset Dim rsPubs2 As ADODB.Recordset Set rsPubs = New ADODB.Recordset Set rsPubs2 = New ADODB.Recordset With rsPubs ' Assign the Connection object. .ActiveConnection = cnPubs ' Extract the required records. .Open "EXEC sp_Week_Option1_01_Export" ' Copy the records into cell A1 on Sheet1. Sheet4.Range("A2").CopyFromRecordset rsPubs For intColIndex = 0 To rsPubs.Fields.Count - 1 Sheet4.Range("A1").Offset(0, intColIndex).Value = rsPubs.Fields(intColIndex).Name Next ' Tidy up .Close End With With rsPubs2 ' Assign the Connection object. .ActiveConnection = cnPubs ' Extract the required records. .Open "sp_Week_Option1_01_Export_Crosstab" ' Copy the records into cell A1 on Sheet1. Sheet9.Range("A2").CopyFromRecordset rsPubs2 For intColIndex = 0 To rsPubs2.Fields.Count - 1 Sheet9.Range("A1").Offset(0, intColIndex).Value = rsPubs2.Fields(intColIndex).Name Next ' Tidy up .Close End With cnPubs.Close Set rsPubs = Nothing Set cnPubs = Nothing ' End SubSolutions Collecting From Web of "VBA Несколько SQL-запросов к Excel"
Похоже, что это должна быть его собственная подпрограмма. Вы можете вызывать Sub каждый раз, когда вам это нужно, передавая выполняемую процедуру и рабочий лист для включения результатов.
Public Sub Macro1(byval storedProc as string, byval ws as worksheet) ' Create a connection object. Dim cnPubs As ADODB.Connection Set cnPubs = New ADODB.Connection ' Provide the connection string. Dim strConn As String 'Use the SQL Server OLE DB Provider. strConn = "PROVIDER=SQLOLEDB;" 'Connect to the Pubs database on the local server. strConn = strConn & "DATA SOURCE=PC\SQL2014;INITIAL CATALOG=Option Database;" 'Use an integrated login. strConn = strConn & " INTEGRATED SECURITY=sspi;" 'Now open the connection. cnPubs.Open strConn ' Create a recordset object. Dim rsPubs As ADODB.Recordset Set rsPubs = New ADODB.Recordset With rsPubs ' Assign the Connection object. .ActiveConnection = cnPubs ' Extract the required records. .Open storedProc ' Copy the records into cell A1 on Sheet1. ws.Range("A2").CopyFromRecordset rsPubs For intColIndex = 0 To rsPubs.Fields.Count - 1 ws.Range("A1").Offset(0, intColIndex).Value = rsPubs.Fields(intColIndex).Name Next ' Tidy up .Close End With cnPubs.Close Set rsPubs = Nothing Set cnPubs = Nothing ' End Subexcel.bilee.com
