Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Oracle -> ODBC -> Excel / 13 сообщений из 13, страница 1 из 1
19.05.2010, 23:15
    #36637997
Вадим Мулявка
Вадим Мулявка
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
Доброго всем времени суток.

Вводная: Имеется файл Excel, БД на Oracle Database 10 и настроенное на него ODBC соединение.

Возникла необходимость в написании макроса для Microsoft Excel(2003), который должен по нажатию кнопки на листе Excel выполнять запрос к БД(Oracle DataBase 10). результат(несколько строк с несколькими столбцами) запроса должен выводиться на лист Excel.

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

Плюс, так сказать на закуску, хотелось бы чтобы можно было использовать файл для различных БД - формирование Connection String с помощью параметров(например имя базы и пароль извлкетать из ячеек расположенных на листе вызова макроса).

P.S.: сам от Excel и VB очень далек, так что не обесудьте, если запрос бредовый и непонятный.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.05.2010, 09:51
    #36638373
Вадим Мулявка
Вадим Мулявка
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
Или может кто подскажет почему возникает вот такая ситуация.

Описал макрос и прицепил его на кнопку:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Sub Button1_Click()
Dim qt As QueryTable


sqlstring = "select * from v$session where schemaname = 'T_KERNDBA'and terminal like 'VCS%';"
connstring = "ODBC;DRIVER=Oracle in Oracle10_Home; DSN=complex1;UID=CTEST;PASS=usrofctest"

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
     .Refresh
End With
    
End Sub
при первом нажатии все отрабатывает, но при повторном вызове выдает ошибку - method 'Refresh' for object '_QueryTable' faild и далее ошибку 1004

И далее запустить можно только после переоткрытия файла Excel.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.05.2010, 11:45
    #36638511
СавсемГость
СавсемГость
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
Вадим Мулявка,
похоже на то, что Excel не может создать QueryTable в том-же диапазоне.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
Sub Button1_Click()
'Dim qt As QueryTable

'sqlstring = "select * from v$session where schemaname = 'T_KERNDBA'and terminal like 'VCS%';"
'connstring = "ODBC;DRIVER=Oracle in Oracle10_Home; DSN=complex1;UID=CTEST;PASS=usrofctest"


'With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
'.Refresh
'End With

' если запрос существует - обновить, если не существует, - создать и обновить
If ActiveSheet.QueryTables.Count> 0  Then
    ActiveSheet.QueryTables( 1 ).Refresh
Else
    sqlstring = "select * from v$session where schemaname = 'T_KERNDBA'and terminal like 'VCS%';"
    connstring = "ODBC;DRIVER=Oracle in Oracle10_Home; DSN=complex1;UID=CTEST;PASS=usrofctest"
    With ActiveSheet.QueryTables.Add(Connection:=connstring, _
               Destination:=Range("A1"), Sql:=sqlstring)
        .Refresh
    End With
End If

End Sub
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.05.2010, 13:14
    #36638871
Вадим Мулявка
Вадим Мулявка
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
СавсемГость,

к сожалению, такой же ошибкой сейчас ругается на строчку:
ActiveSheet.QueryTables(1).Refresh
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.05.2010, 19:19
    #36640103
СавсемГость
СавсемГость
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
Вадим Мулявка,
повторил, почти, Ваш код у себя. Run-time error 1004 получил уже при первом обращении к источнику данных. Убрал из строки соединения указание на драйвер, изменил имя параметра PASS на PWD, - заработало.
Код: plaintext
1.
2.
3.
...
'connstring = "ODBC;DRIVER=Oracle in Oracle10_Home; DSN=complex1;UID=CTEST;PASS=usrofctest"
connstring = "ODBC;DSN=complex1;UID=CTEST;PWD=usrofctest"
...
Тестировалось на: Oracle client 8.1.7, Excel 2003 (11.8105.8107) SP2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2010, 09:00
    #36640686
Вадим Мулявка
Вадим Мулявка
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
СавсемГость,

Да, с параметрами коннекты был косяк, но сейчас поправил - и один раз всё отрабатывает, но потом опять ругается на Refresh.
Посмотрел в debug-режиме - всегда заходит в if, т.е. считает, что QueryTable уже существует на листе, даже при первом проходе.

Возможно как-то надо явно объявить переменную с QueryTable.

Вид макроса сейчас вот такой:




Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
Sub script_1()
Dim inst, sqlt, dsnt, pwdt, connstt As String

If ActiveSheet.QueryTables.Count >  0  Then
   ActiveSheet.QueryTables( 1 ).Refresh
Else
    inst = Cells( 2 ,  2 )
    sqlt = "select sid from v$session where terminal like 'VCS%' and lower(schemaname)="
    'sqlt = "select sid from v$session where osuser='vmulyavka' and lower(schemaname)="
    
    connstt = "ODBC;"
    dsnt = Cells( 3 ,  2 )
    pwdt = Cells( 4 ,  2 )
    
    
    sqlstring = sqlt + "'" + inst + "'"
    connstring = connstt + "DSN=" + dsnt + ";UID=" + inst + ";PWD=" + pwdt + ";"
    
    'sqlstring = "select sid from v$session where  osuser='vmulyavka';"
    'connstring = "ODBC;DSN=COMPLEX1;UID=T_KERNDBA;PWD=USROFT_KERNDBA;"
    '
       
    With ActiveSheet.QueryTables.Add(Connection:=connstring, _
                        Destination:=Range("D9"), Sql:=sqlstring)
         '.CommandText = sqlstring
         .BackgroundQuery = False
         .Refresh
    End With
End If

If Cells( 10 ,  4 ) >  0  Then
 Cells( 10 ,  4 ).Interior.Color = RGB( 255 ,  0 ,  0 )
Else
 Cells( 10 ,  4 ).Interior.Color = RGB( 168 ,  168 ,  168 )
End If
Range("D9:D14").ColumnWidth =  17 
   
'Cells(2, 2).Font.Italic = True
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2010, 10:23
    #36640808
Вадим Мулявка
Вадим Мулявка
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
А подскажите, пожалуйста, как удалить созданный QueryTable с листа?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2010, 15:12
    #36641801
Вадим Мулявка
Вадим Мулявка
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
В своих изысканиях дошел до ошибкис кодом -2147417848(80010108), возникающей при повторном нажатии на кнопку. Насколькоя понял, эта ошибка означае, что система не может восстановить связь с БД.

Возможно, кто-нибудь знаешь в чем причина и как это вылечить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2010, 15:34
    #36641885
Вадим Мулявка
Вадим Мулявка
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
В своих изысканиях дошел до ошибкис кодом -2147417848(80010108), возникающей при повторном нажатии на кнопку. Насколькоя понял, эта ошибка означае, что система не может восстановить связь с БД.

Возможно, кто-нибудь знаешь в чем причина и как это вылечить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2010, 15:40
    #36641903
f
f
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
Вадим Мулявка, поищи рецепты по форуму. Подобные вопросы здесь неоднократно обсуждались. Мало кто постоянно использует QueryTable, чтобы с ходу разобраться.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2010, 17:53
    #36642317
СавсемГость
СавсемГость
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
Вадим Мулявка,
Вадим МулявкаА подскажите, пожалуйста, как удалить созданный QueryTable с листа?
 
удаление QueryTable
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
'удалить запрос
ActiveSheet.QueryTables(Index).Delete
'(!)или
'удалить запрос и полученные данные
'ActiveSheet.QueryTables(Index).ResultRange.Delete

'удалить все запросы с листа
'Dim k%
'For k = ActiveSheet.QueryTables.Count To 1 Step -1
'    ActiveSheet.QueryTables(k).Delete
'Next k
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2010, 20:03
    #36642523
zcvv
zcvv
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
Вадим Мулявка

Вам нужно продвигаться в эту сторону:
У Вас должна быть форма с элементом управления TextBox с MultiLine=True. В него Вы будете вводить текст запроса.
У Вас должна быть процедура ВыполнитьЗапрос (например) с параметром sqlstr (тоже например).
В эту процедуру Вы будете передавать в качестве фактического параметра TextBox.Text
У Вас должна быть на форме кнопка Выполнить - на нее повесите выполнение этой процедуры.
При выполнении этой процедуры в ЭтуКнигу должен добавляться лист. В ячейку А2 методом
CopyFromRecordset скопируете данные из полученного рекордсета. Все не расскажешь.
Вам придется почитать кое-что, если Вы действительно далеки, а не прибедняетесь.
Я лишь показываю Вам, куда двигаться, если нужна гибкость относительно текста запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2010, 20:09
    #36642530
zchvv
zchvv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oracle -> ODBC -> Excel
Вадим Мулявка

Вам нужно продвигаться в эту сторону:
У Вас должна быть форма с элементом управления TextBox с MultiLine=True. В него Вы будете вводить текст запроса.
У Вас должна быть процедура ВыполнитьЗапрос (например) с параметром sqlstr (тоже например).
В эту процедуру Вы будете передавать в качестве фактического параметра TextBox.Text
У Вас должна быть на форме кнопка Выполнить - на нее повесите выполнение этой процедуры.
При выполнении этой процедуры в ЭтуКнигу должен добавляться лист. В ячейку А2 методом
CopyFromRecordset скопируете данные из полученного рекордсета. Все не расскажешь.
Вам придется почитать кое-что, если Вы действительно далеки, а не прибедняетесь.
Я лишь показываю Вам, куда двигаться, если нужна гибкость относительно текста запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Oracle -> ODBC -> Excel / 13 сообщений из 13, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 511ms
Закрыть
start [/forum/topic.php?fid=61&tablet=1&tid=2178088]:
 0ms
get settings:
 6ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 194ms
get topic data:
 8ms
get forum data:
 1ms
get page messages:
 40ms
get tp. blocked users:
 1ms
others: 241ms
total:  511ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]