Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Эксель и SQL Server. Найти соответствия - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Эксель и SQL Server. Найти соответствия (Иное/Other)
Эксель и SQL Server. Найти соответствия
Aumi Дата: Вторник, 19.12.2017, 15:23 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте,

Требуется написать макрос.

На листе есть столбец "ID" "Наличие". А на sql server таблица тоже с "ID".
Если id из экселя есть на сервере, то в столбец "Наличие" писать "да". В противном случае "нет".

решила начать с простого-попытаться хоть что нибудь вывести. Простое действие и то не получается. Сразу ругань на неопределенный тип ADODB.Connection

[vba]
Код
Public Sub Photo()

Dim Conn As String
Dim strsql As String
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn = "Data Source=nameserver;Initial Catalog=MyDB;Integrated Security=True"

cn.ConnectionString = Conn
cn.Open

rst.Open "SELECT TOP 10 * FROM tbl_book_foto", cn
ActiveSheet.Range("T2").CopyFromRecordset rst rst.Close
cn.Close
Set cn = Nothing End Sub
[/vba]
 
Ответить
СообщениеЗдравствуйте,

Требуется написать макрос.

На листе есть столбец "ID" "Наличие". А на sql server таблица тоже с "ID".
Если id из экселя есть на сервере, то в столбец "Наличие" писать "да". В противном случае "нет".

решила начать с простого-попытаться хоть что нибудь вывести. Простое действие и то не получается. Сразу ругань на неопределенный тип ADODB.Connection

[vba]
Код
Public Sub Photo()

Dim Conn As String
Dim strsql As String
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn = "Data Source=nameserver;Initial Catalog=MyDB;Integrated Security=True"

cn.ConnectionString = Conn
cn.Open

rst.Open "SELECT TOP 10 * FROM tbl_book_foto", cn
ActiveSheet.Range("T2").CopyFromRecordset rst rst.Close
cn.Close
Set cn = Nothing End Sub
[/vba]

Автор - Aumi
Дата добавления - 19.12.2017 в 15:23
Aumi Дата: Вторник, 19.12.2017, 15:35 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Нашлась ошибка с подключением-не подключила ссылку на Active X
 
Ответить
СообщениеНашлась ошибка с подключением-не подключила ссылку на Active X

Автор - Aumi
Дата добавления - 19.12.2017 в 15:35
Aumi Дата: Вторник, 19.12.2017, 16:12 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Я тут запрос придумала, а с циклом никак
[vba]
Код
Dim iCell As Range
Dim tmp As String
rst.Open "SELECT id" & tmp & "FROM tbl1 where id=" & iCell.Value, cn

if (id is null)
то писать "Нет"
else да
[/vba]
Т е если A2 существует в БД, то в Т2 писать "ДА"

Вот как это реализовать???
 
Ответить
СообщениеЯ тут запрос придумала, а с циклом никак
[vba]
Код
Dim iCell As Range
Dim tmp As String
rst.Open "SELECT id" & tmp & "FROM tbl1 where id=" & iCell.Value, cn

if (id is null)
то писать "Нет"
else да
[/vba]
Т е если A2 существует в БД, то в Т2 писать "ДА"

Вот как это реализовать???

Автор - Aumi
Дата добавления - 19.12.2017 в 16:12
Aumi Дата: Вторник, 19.12.2017, 18:37 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
[moder]Соединил две темы в одну[/moder]

Требуется макрос, чтоб находить соответствия столбца А в таблице Бд. Я часть сделала, но не могу получить результат запроса select. Если select что то вывел, то соответствует ячейка и в столбце Т написать слово "да" иначе "нет"

В коде я просто тестировала хоть какой нибудь вывод в ячейки. А так в if должно быть да или нет

[vba]
Код
   Public Sub Photo1()

Dim Conn As String

Dim cn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim iCell As Range

Dim iArr As Variant, iRow&

Dim iTrr As Variant

Dim tmp As String

Set cn = New ADODB.Connection

Set rst = New ADODB.Recordset

Conn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=db_spravka;Data Source=spravka\SRVSQLNKC;"

cn.ConnectionString = Conn

cn.Open

  'select @op=[PT_SHORTNAME] from [db_Partners]..[Partner] where [PT_ID]=@ptid

'    Application.ScreenUpdating = False

   

     

   For Each ACell In Range("A2", Cells(Rows.Count, 1).End(xlUp))

rst.Open "SELECT bookid FROM tbl_book_foto where bookid=" & ACell.Value, cn

If ACell.Value = ACell.Value Then

     Cells(ACell.Row, 20).Value = ACell.Value

End If

    

    Next

    Application.ScreenUpdating = True

rst.Close

cn.Close

Set cn = Nothing

End Sub
[/vba]


Сообщение отредактировал SLAVICK - Среда, 20.12.2017, 10:56
 
Ответить
Сообщение[moder]Соединил две темы в одну[/moder]

Требуется макрос, чтоб находить соответствия столбца А в таблице Бд. Я часть сделала, но не могу получить результат запроса select. Если select что то вывел, то соответствует ячейка и в столбце Т написать слово "да" иначе "нет"

В коде я просто тестировала хоть какой нибудь вывод в ячейки. А так в if должно быть да или нет

[vba]
Код
   Public Sub Photo1()

Dim Conn As String

Dim cn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim iCell As Range

Dim iArr As Variant, iRow&

Dim iTrr As Variant

Dim tmp As String

Set cn = New ADODB.Connection

Set rst = New ADODB.Recordset

Conn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=db_spravka;Data Source=spravka\SRVSQLNKC;"

cn.ConnectionString = Conn

cn.Open

  'select @op=[PT_SHORTNAME] from [db_Partners]..[Partner] where [PT_ID]=@ptid

'    Application.ScreenUpdating = False

   

     

   For Each ACell In Range("A2", Cells(Rows.Count, 1).End(xlUp))

rst.Open "SELECT bookid FROM tbl_book_foto where bookid=" & ACell.Value, cn

If ACell.Value = ACell.Value Then

     Cells(ACell.Row, 20).Value = ACell.Value

End If

    

    Next

    Application.ScreenUpdating = True

rst.Close

cn.Close

Set cn = Nothing

End Sub
[/vba]

Автор - Aumi
Дата добавления - 19.12.2017 в 18:37
bmv98rus Дата: Вторник, 19.12.2017, 18:57 | Сообщение № 5
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 0% ±

Excel 2013/2016
Вернул или нет проше проверить посмотрев у рекордсета recordscount. Если нет, то 0. Но вот так дергать запрос на каждую ячейку это мрак.
 
Ответить
СообщениеВернул или нет проше проверить посмотрев у рекордсета recordscount. Если нет, то 0. Но вот так дергать запрос на каждую ячейку это мрак.

Автор - bmv98rus
Дата добавления - 19.12.2017 в 18:57
Aumi Дата: Вторник, 19.12.2017, 19:02 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
bmv98rus, если честно не поняла вас. Типо не нужно крутить цикл по ячейкам? А как же тогда?
 
Ответить
Сообщениеbmv98rus, если честно не поняла вас. Типо не нужно крутить цикл по ячейкам? А как же тогда?

Автор - Aumi
Дата добавления - 19.12.2017 в 19:02
bmv98rus Дата: Вторник, 19.12.2017, 21:08 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 0% ±

Excel 2013/2016
Aumi, Я только стратегию вырабатываю :-) С мобильного писал, было не удобно подробне.

1. вот это [vba]
Код
If ACell.Value = ACell.Value Then
    Cells(ACell.Row, 20).Value = ACell.Value
End If
[/vba]зачем?
Можно заменить на
[vba]
Код
If rst.recordscount > 0 Then  Cells(ACell.Row, 20).Value = ACell.Value
[/vba]

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


или [vba]
Код
RST.movelast
[/vba]

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

ну и конечно кросс


Сообщение отредактировал bmv98rus - Среда, 20.12.2017, 11:05
 
Ответить
СообщениеAumi, Я только стратегию вырабатываю :-) С мобильного писал, было не удобно подробне.

1. вот это [vba]
Код
If ACell.Value = ACell.Value Then
    Cells(ACell.Row, 20).Value = ACell.Value
End If
[/vba]зачем?
Можно заменить на
[vba]
Код
If rst.recordscount > 0 Then  Cells(ACell.Row, 20).Value = ACell.Value
[/vba]

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


или [vba]
Код
RST.movelast
[/vba]

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

ну и конечно кросс

Автор - bmv98rus
Дата добавления - 19.12.2017 в 21:08
SLAVICK Дата: Среда, 20.12.2017, 10:38 | Сообщение № 8
Группа: Модераторы
Ранг: Старожил
Сообщений: 2246
Репутация: 745 ±
Замечаний: 0% ±

2007,2010,2013,2016
Это самый универсальный подход

я использую для таких случаев UDF(пользовательскую функцию) например такую:
[vba]
Код
Public Function SQLto2CellSimple(sSQL As String, Optional ByVal ServerName$ = "MyServer", Optional ByVal DatabaseName$ = "MyBase", Optional ByVal UserID$ = "", Optional ByVal Password = "")
    'simple Function to get results SQL request in cell.
    '"Microsoft ActiveX Data Objects ***" is require
    Dim oRecordset As ADODB.Recordset ' Object
    Dim oConnection As ADODB.Connection ' Object

    Set oConnection = New ADODB.Connection 'CreateObject("ADODB.Connection") '
    Set oRecordset = New ADODB.Recordset 'CreateObject("ADODB.Recordset") '
    If InStr(1, sSQL, "set nocount on", vbTextCompare) = 0 Then sSQL = "set nocount on; " & vbLf & sSQL

    oConnection.Open "Driver={SQL Server};Server=" & ServerName$ & ";Database=" & DatabaseName$ & ";Uid=" & UserID$ & ";Pwd=;" & Password & ""

    oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic

    If oRecordset.EOF Then SQLto2CellSimple = "n/a" Else SQLto2CellSimple = oRecordset.GetRows

    Set oConnection = Nothing
    Set oRecordset = Nothing

End Function
[/vba]
и потом формула
[vba]
Код
=SQLto2CellSimple("SELECT count([EXT_ID]) FROM [DimProduct] where [EXT_ID] = "&A3)
[/vba]
и формат ячейки
Код
"Да";;"Нет"

PS
вместо MyServer и MyBase(в ЮДФ) и EXT_ID и DimProduct(в формуле) - подставьте свои данные
К сообщению приложен файл: _2.xlsm(16.8 Kb)


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
Это самый универсальный подход

я использую для таких случаев UDF(пользовательскую функцию) например такую:
[vba]
Код
Public Function SQLto2CellSimple(sSQL As String, Optional ByVal ServerName$ = "MyServer", Optional ByVal DatabaseName$ = "MyBase", Optional ByVal UserID$ = "", Optional ByVal Password = "")
    'simple Function to get results SQL request in cell.
    '"Microsoft ActiveX Data Objects ***" is require
    Dim oRecordset As ADODB.Recordset ' Object
    Dim oConnection As ADODB.Connection ' Object

    Set oConnection = New ADODB.Connection 'CreateObject("ADODB.Connection") '
    Set oRecordset = New ADODB.Recordset 'CreateObject("ADODB.Recordset") '
    If InStr(1, sSQL, "set nocount on", vbTextCompare) = 0 Then sSQL = "set nocount on; " & vbLf & sSQL

    oConnection.Open "Driver={SQL Server};Server=" & ServerName$ & ";Database=" & DatabaseName$ & ";Uid=" & UserID$ & ";Pwd=;" & Password & ""

    oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic

    If oRecordset.EOF Then SQLto2CellSimple = "n/a" Else SQLto2CellSimple = oRecordset.GetRows

    Set oConnection = Nothing
    Set oRecordset = Nothing

End Function
[/vba]
и потом формула
[vba]
Код
=SQLto2CellSimple("SELECT count([EXT_ID]) FROM [DimProduct] where [EXT_ID] = "&A3)
[/vba]
и формат ячейки
Код
"Да";;"Нет"

PS
вместо MyServer и MyBase(в ЮДФ) и EXT_ID и DimProduct(в формуле) - подставьте свои данные

Автор - SLAVICK
Дата добавления - 20.12.2017 в 10:38
bmv98rus Дата: Среда, 20.12.2017, 11:50 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 0% ±

Excel 2013/2016
SLAVICK, Не спорю, при единичных использованиях такое имеет место, разве что летучести надо добавить из расчета на динамичность данных в базе. Но если надо проверить сотни, то эта дерготня может сильно вредить быстродействию.

А вот это -oRecordset.EOF и правда лучше чем count.


Сообщение отредактировал bmv98rus - Среда, 20.12.2017, 11:54
 
Ответить
СообщениеSLAVICK, Не спорю, при единичных использованиях такое имеет место, разве что летучести надо добавить из расчета на динамичность данных в базе. Но если надо проверить сотни, то эта дерготня может сильно вредить быстродействию.

А вот это -oRecordset.EOF и правда лучше чем count.

Автор - bmv98rus
Дата добавления - 20.12.2017 в 11:50
SLAVICK Дата: Среда, 20.12.2017, 12:42 | Сообщение № 10
Группа: Модераторы
Ранг: Старожил
Сообщений: 2246
Репутация: 745 ±
Замечаний: 0% ±

2007,2010,2013,2016
Но если надо проверить сотни,

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

Я такие расчеты в основном использую для просчитал - вставил как значения. Зачем при каждом чихе базу тревожить?


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
Но если надо проверить сотни,

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

Я такие расчеты в основном использую для просчитал - вставил как значения. Зачем при каждом чихе базу тревожить?

Автор - SLAVICK
Дата добавления - 20.12.2017 в 12:42
Aumi Дата: Четверг, 21.12.2017, 10:05 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
SLAVICK, Проверяла работу функции на 800 строках. Просто подождать до окончания процесса нужно не более 5 сек. Так что не вижу минусов в этом. Притом пользователи будут в по одной новой записи добавлять в файл раз в день. Так что думаю, им будет норм пользоваться функцией, просто тянув вниз.
SLAVICK, bmv98rus, спасибо вам за помощь!
 
Ответить
СообщениеSLAVICK, Проверяла работу функции на 800 строках. Просто подождать до окончания процесса нужно не более 5 сек. Так что не вижу минусов в этом. Притом пользователи будут в по одной новой записи добавлять в файл раз в день. Так что думаю, им будет норм пользоваться функцией, просто тянув вниз.
SLAVICK, bmv98rus, спасибо вам за помощь!

Автор - Aumi
Дата добавления - 21.12.2017 в 10:05
bmv98rus Дата: Четверг, 21.12.2017, 10:14 | Сообщение № 12
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 0% ±

Excel 2013/2016
Aumi, Только имейте в виду, за счет "нелетучести" функции , если вчера внесли строчку и она показала отсутствие, а сегодня в базе появилась нужная запись (или пропала), то значением не изменится . Даже если запустить перерасчет. Но все зависит от того , что вы контролируете этим.
 
Ответить
СообщениеAumi, Только имейте в виду, за счет "нелетучести" функции , если вчера внесли строчку и она показала отсутствие, а сегодня в базе появилась нужная запись (или пропала), то значением не изменится . Даже если запустить перерасчет. Но все зависит от того , что вы контролируете этим.

Автор - bmv98rus
Дата добавления - 21.12.2017 в 10:14
Aumi Дата: Четверг, 21.12.2017, 10:31 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
bmv98rus,
Даже если запустить перерасчет

Типо если удалить столбец и по-новой запустить, то старые значения останутся?
 
Ответить
Сообщениеbmv98rus,
Даже если запустить перерасчет

Типо если удалить столбец и по-новой запустить, то старые значения останутся?

Автор - Aumi
Дата добавления - 21.12.2017 в 10:31
bmv98rus Дата: Четверг, 21.12.2017, 10:40 | Сообщение № 14
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 0% ±

Excel 2013/2016
например если искомое значение в A1 и в B1 формула и вчера она выдавала "нет", то открыв сегодня так "нет" и будет, даже если в базе что-то появилось и даже если пересчитать лист (F9). Для того чтоб пересчет произошел, надо изменить значение в A1 или перезаписать формулу в B1.
 
Ответить
Сообщениенапример если искомое значение в A1 и в B1 формула и вчера она выдавала "нет", то открыв сегодня так "нет" и будет, даже если в базе что-то появилось и даже если пересчитать лист (F9). Для того чтоб пересчет произошел, надо изменить значение в A1 или перезаписать формулу в B1.

Автор - bmv98rus
Дата добавления - 21.12.2017 в 10:40
Aumi Дата: Четверг, 21.12.2017, 12:14 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
bmv98rus, SLAVICK, Тут есть такой интересный случай. Записи идут с 2-194 строчке, потом пустая , потом опять записи идут дальше. И так несколько раз. Номера строк-разрывов логически не связаны.
Если это делать ф-цией, то она просто напишет в разрыве #ЗНАЧ!

А если в цикле, то считается 194 заполнятся, а на 195
[vba]
Код
For Each ACell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
[/vba]

[ODBC SQL Server Driver][SQL Server] Неправильный синтаксис около конструкции "="

Может знаете, как это решить?


Сообщение отредактировал Aumi - Четверг, 21.12.2017, 12:51
 
Ответить
Сообщениеbmv98rus, SLAVICK, Тут есть такой интересный случай. Записи идут с 2-194 строчке, потом пустая , потом опять записи идут дальше. И так несколько раз. Номера строк-разрывов логически не связаны.
Если это делать ф-цией, то она просто напишет в разрыве #ЗНАЧ!

А если в цикле, то считается 194 заполнятся, а на 195
[vba]
Код
For Each ACell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
[/vba]

[ODBC SQL Server Driver][SQL Server] Неправильный синтаксис около конструкции "="

Может знаете, как это решить?

Автор - Aumi
Дата добавления - 21.12.2017 в 12:14
bmv98rus Дата: Четверг, 21.12.2017, 13:11 | Сообщение № 16
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 0% ±

Excel 2013/2016
знаем, но вопрос уже не по этой теме, хоте если честно и да простят меня модераторы
или надо в функции просто проверят переданное значения sSQL и если оно пустое или не соотвтевует правильному синтаксису и выводить что-то .
или просто
Код
=IFERROR(SQLto2CellSimple(A1);"")

Ну и с циклом естественно проверять что значением есть и смещать строку вывода.


Сообщение отредактировал bmv98rus - Четверг, 21.12.2017, 13:14
 
Ответить
Сообщениезнаем, но вопрос уже не по этой теме, хоте если честно и да простят меня модераторы
или надо в функции просто проверят переданное значения sSQL и если оно пустое или не соотвтевует правильному синтаксису и выводить что-то .
или просто
Код
=IFERROR(SQLto2CellSimple(A1);"")

Ну и с циклом естественно проверять что значением есть и смещать строку вывода.

Автор - bmv98rus
Дата добавления - 21.12.2017 в 13:11
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Эксель и SQL Server. Найти соответствия (Иное/Other)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2018 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!