ReSQL.ru
     
powered by simpleCommunicator - 2.0.39     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / VBA Excel и ADODB.Connection
25 сообщений из 28, страница 1 из 2
  1  все
VBA Excel и ADODB.Connection
    #38765397
Максим323
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Максим323
Гость
Подскажите, пожалуйста.

Есть код в VBA для получения данных из другого файла Excel посредством SQL-запроса:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\Users\file_1.xlsx";Extended Properties=""Excel 12.0;HDR=YES"";"
cn.Open

iSQL = "select * from `C:\Users\file_1.xlsx`.`data$` " 

Set rs = New ADODB.Recordset: rs.Open iSQL, cn
Sheets(shName).Cells(2, 1).CopyFromRecordset rs



Код работает. Данные выгружаются.

Можно ли как-то создать подключение/запрос, чтобы совмещать два разных файла Excel?
Что-то вроде двух разных таблиц в одном запросе. Т.е. чтобы sql мог быть вроде:

Код: sql
1.
2.
3.
4.
5.
select a.MY_NAME, b.MY_DATE
from
	(select MY_NAME, MY_ID from `C:\Users\file_1.xlsx`.`data$`) as a,
	(select MY_DATE, `MY_ID from `C:\Users\file_2.xlsx`.`data$`) as b
where a.MY_ID = b.MY_ID



Могу предположить, что надо создать два отдельных подключения на каждый excel-файл, но как их потом склеить в один запрос?
...
Рейтинг: 0 / 0
02.10.2014, 21:12
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38765401
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
А что, этот вариант не работает? (если убрать, конечно, лишнюю кавычку)
...
Рейтинг: 0 / 0
02.10.2014, 21:19
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38765413
Максим323
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Максим323
Гость
С кавычкой просто моя опечатка на форуме.
У меня код не срабатывает:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
iFile = "C:\Users\file_1.xlsx"

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & iFile & """;Extended Properties=""Excel 12.0;HDR=YES"";"
cn.Open

iSQL = "" & vbCrLf & _
	"select a.MY_NAME, b.MY_DATE " & vbCrLf & _
	"from " & vbCrLf & _
		"(select MY_NAME, MY_ID from `C:\Users\file_1.xlsx`.`data$`) as a, " & vbCrLf & _
		"(select MY_DATE, MY_ID from `C:\Users\file_2.xlsx`.`data$`) as b " & vbCrLf & _
	"where a.MY_ID = b.MY_ID "

Set rs = New ADODB.Recordset: rs.Open iSQL, cn
Sheets(shName).Cells(2, 1).CopyFromRecordset rs



Предполагаю, потому что я создал подключение "cn", которое свазано только с первым файлом (file_1.xlsx).
А т.к. в запросе я использую еще и другой файл (file_2.xlsx), на к-ый не создано подключения, то и возникает ошибка.

Как правильно сделать, не понимаю..
...
Рейтинг: 0 / 0
02.10.2014, 21:41
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38765455
iMrTidy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iMrTidy
Участник
Максим323,

Попробутей так:

Код: vbnet
1.
2.
3.
iSQL = "select a.MY_NAME, b.MY_DATE " & _
       "from ((select MY_NAME, MY_ID from `C:\Users\file_1.xlsx`.`data$`) as a " & _
       "inner join (select MY_DATE, MY_ID from `C:\Users\file_2.xlsx`.`data$`) as b on a.MY_ID = b.MY_ID)"
...
Рейтинг: 0 / 0
02.10.2014, 22:53
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38765613
Максим323
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Максим323
Гость
Та же ошибка.
Дело здесь не в синтаксисе (join или where), а в подключении к файлу.
Если бы я работал с базой данных, то проблем не было. А в данном случае это два разных файла excel. При этом коннект создается только на первый файл и он же используется в дальнейшем.

Я могу создать хоть два и более различных коннекта. Но как их использовать в одном запросе? Вот проблема.
Ведь в конечном итоге я иницирую выполнение запроса командой rs.Open iSQL, cn . А здесь уже конкретная привязка к ранее созданному подлючению на первый файл.

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
iFile = "C:\Users\file_1.xlsx"

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & iFile & """;Extended Properties=""Excel 12.0;HDR=YES"";"
cn.Open

Set rs = New ADODB.Recordset
rs.Open iSQL, cn
...
Рейтинг: 0 / 0
03.10.2014, 09:33
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38765872
f
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
f
Участник
Пиши в промежуточную таблицу.
...
Рейтинг: 0 / 0
03.10.2014, 12:12
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38766309
f
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
f
Участник
Вроде, должно работать.
...
Рейтинг: 0 / 0
03.10.2014, 16:09
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38766438
скукотища
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
скукотища
Гость
Максим323,

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
' ...
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\file_1.xlsx';Extended Properties='Excel 12.0;HDR=YES';"
cn.Open

REM Подключение к file_1.xlsx
REM "Таблицы" из файла file_1.xlsx дополнительного описания не требуют
REM "Таблицы" из других файлов описываются полным путём к источнику и типом источника

query = _
"select a.MY_NAME, b.MY_DATE" & _
" from" & _
"    (select MY_NAME, MY_ID from [data$]) as a," & _
"    (select MY_DATE, `MY_ID from [data$] in 'C:\Users\file_2.xlsx' 'Excel 12.0;HDR=YES') as b" & _
" where a.MY_ID = b.MY_ID"

Set rs = cn.Execute(query)
Sheets(shName).Cells(2, 1).CopyFromRecordset rs
' ...
...
Рейтинг: 0 / 0
03.10.2014, 17:04
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38766662
ex-zel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ex-zel
Гость
скукотища,

Только CopyFromRecordset формат ячеек испортит. Лучше результат запроса занести в массив (rs.GetRows) и далее вынести данные в диапазон (myRange.Value = myArrayVariable)
...
Рейтинг: 0 / 0
03.10.2014, 22:11
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38766674
iMrTidy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iMrTidy
Участник
Максим323,

Боюсь, что Вы не правы касательно причины ошбики. В моем примере все работает как и должно, который предлагаю Вашему вниманию.
...
Рейтинг: 0 / 0
03.10.2014, 22:45
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38766688
iMrTidy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iMrTidy
Участник
Максим323,

Рискну предположить, что дело может быть в следующем:
1) Версия Office на компьютере, на которым Вы тестируете, ниже 2007.
2) Структура запрашиваемых данных не соответствует запросу.
3) Неверный путь к файлам или листам.

Будет проще помочь, если Вы дадите конкретный пример с Вашими файлами. Кстати, Вы так и не озвучили ошибку, равно как и строку, на которой она возникает.
...
Рейтинг: 0 / 0
03.10.2014, 23:08
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38776786
Максим323
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Максим323
Гость
Извиняюсь. Немного выпал из диалога.
Проверил. Я напутал в своих файлах что-то. Разбираться не стал. Поправил, исходя из примера iMrTidy и все заработало :)

Спасибо всем большое за помощь.
...
Рейтинг: 0 / 0
15.10.2014, 01:28
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38780743
Максим323
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Максим323
Гость
Подскажите еще, пожалуйста, по коду:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
iFile = "C:\Users\file_1.xlsx"

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & iFile & """;Extended Properties=""Excel 12.0;HDR=YES"";"
cn.Open

iSQL = "" & vbCrLf & _
	"select a.MY_NAME, b.MY_DATE " & vbCrLf & _
	"from " & vbCrLf & _
		"(select MY_NAME, MY_ID from `C:\Users\file_1.xlsx`.`data$`) as a, " & vbCrLf & _
		"(select MY_DATE, MY_ID from `C:\Users\file_2.xlsx`.`data$`) as b " & vbCrLf & _
	"where a.MY_ID = b.MY_ID "

Set rs = New ADODB.Recordset: rs.Open iSQL, cn
Sheets(shName).Cells(2, 1).CopyFromRecordset rs



Сейчас в строках
Код: vbnet
1.
2.
"(select MY_NAME, MY_ID from `C:\Users\file_1.xlsx`.`data$`) as a, " & vbCrLf & _
"(select MY_DATE, MY_ID from `C:\Users\file_2.xlsx`.`data$`) as b " & vbCrLf & _


я указал названия конкретных листов в книге. В данном случае лист `data`.


Можно ли как-то задать листы номерами?
Что-то вроде: `C:\Users\file_1.xlsx`.sheets(5) и тп.
...
Рейтинг: 0 / 0
18.10.2014, 17:19
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38780772
Максим323
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Максим323
Гость
Сделал пока определение имени листа дополнительным запросом к файлу-источнику:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
With cn.OpenSchema(adSchemaTables)
	For i = 1 To .RecordCount
		debug.print .Fields("TABLE_NAME").Value
		Exit For
		.MoveNext
	Next
	.Close
End With



Если кто значет способ, как через конкретные номера листов, буду признателен.
...
Рейтинг: 0 / 0
18.10.2014, 18:47
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38934321
Bobgos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bobgos
Гость
Максим323,

Понимая, что тема скорее не жива... Но вдруг..

Подскажите пожалуйста код (точне текст sql запроса) INSERT через adodb для вставки новой строки со значениями в файл excel. Использую execute. SELECT работает, а INSERT нет.
...
Рейтинг: 0 / 0
12.04.2015, 20:53
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38934327
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
операции изменения данных не поддерживаются
...
Рейтинг: 0 / 0
12.04.2015, 21:12
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38934426
anvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
anvg
Гость
Доброе время суток
Insert и Update для файлов Excel поддерживаются. Не работает только Delete. Вставка работает, если данные строго структурированных. Если задан диапазон листа, то вставка работает только в том случае, если ниже этой области ничего нет. Иначе вставка будет заблокированна.
Bobgos
Вы бы привели пример файла, строку подключения и текст вставки, тогда было бы проще что-то советовать. Файл для примера во вложении. Естественно вставку и обновление можно делать и через Recordset
...
Рейтинг: 0 / 0
13.04.2015, 07:53
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38934427
Фотография HandKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
HandKot
Участник
Shocker.Proоперации изменения данных не поддерживаются
я что-то такого не слышал. По крайней мере, в OLEDB можно было делать UPDATE
вот к примеру Running a SQL Update Query in Excel VBA
...
Рейтинг: 0 / 0
13.04.2015, 08:04
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38934451
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shocker.Pro
Участник
HandKotShocker.Proоперации изменения данных не поддерживаются
я что-то такого не слышал. По крайней мере, в OLEDB можно было делать UPDATEгм, возможно, этого нельзя было делать в ODBC..., помню, что мы обсуждали это когда-то на форуме, тады - ой, виноват.
...
Рейтинг: 0 / 0
13.04.2015, 09:16
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38935340
Bobgos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bobgos
Гость
anvgДоброе время суток
Insert и Update для файлов Excel поддерживаются. Не работает только Delete. Вставка работает, если данные строго структурированных. Если задан диапазон листа, то вставка работает только в том случае, если ниже этой области ничего нет. Иначе вставка будет заблокированна.
Bobgos
Вы бы привели пример файла, строку подключения и текст вставки, тогда было бы проще что-то советовать. Файл для примера во вложении. Естественно вставку и обновление можно делать и через Recordset

anvg,спасибо.
оказывается забыл в квадратные скобки имя таблицы вставить. :-)
файл в жутком беспорядке - не хотел и выкладывать. но раз просите...
?
А для чего:
Код: plsql
1.
Update [Table$] Set fname='long name' Where fid=1


?????

и может заодно для удаления записи из таблицы подскажете решение?
...
Рейтинг: 0 / 0
13.04.2015, 22:39
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38935371
Bobgos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bobgos
Гость
HandKotShocker.Proоперации изменения данных не поддерживаются
я что-то такого не слышал. По крайней мере, в OLEDB можно было делать UPDATE
вот к примеру Running a SQL Update Query in Excel VBA

а если OLEDB не вариант и нужно использовать ADO?
как обновлять?

если обновлять рекордсет появляется сообщение об ошибке. -прилагаю
...
Рейтинг: 0 / 0
13.04.2015, 23:23
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38935376
Bobgos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bobgos
Гость
И еще вопросы:
1. в vba можно ли рекордсет положить на форму и связать с listbox, а лучше listview?
2. как заполнить Listview из рекордсета. Можно ли как-то оперативно, не добавляя построчно?
3. все же как обновлять записи.
по кнопке удалить - даже если не удалять, а делать неактивным
по кнопке сохранить - обновить по текущим данным полей
по кнопке добавить - спасибо будем разбираться дальше разберемся, но хотелось бы не переделывать запросы, если в таблицу добавится несколько полей.

посоветуете?

файлик прилагаю...
...
Рейтинг: 0 / 0
13.04.2015, 23:33
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38935432
anvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
anvg
Гость
Доброе время суток
Bobgosфайл в жутком беспорядке - не хотел и выкладывать. но раз просите...
Да, в этом случае проще самому пример написать;)
Разбирайте код. Не забудьте посмотреть в "Имена" на листе Table.
Успехов
...
Рейтинг: 0 / 0
14.04.2015, 03:29
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38935447
Bobgos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bobgos
Гость
anvg,
то что надо. спасибо большое.
...
Рейтинг: 0 / 0
14.04.2015, 06:03
| Ответить | Цитировать | Написать  
VBA Excel и ADODB.Connection
    #38935494
Фотография HandKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
HandKot
Участник
BobgosHandKotпропущено...

я что-то такого не слышал. По крайней мере, в OLEDB можно было делать UPDATE
вот к примеру Running a SQL Update Query in Excel VBA

а если OLEDB не вариант и нужно использовать ADO?
как обновлять?

если обновлять рекордсет появляется сообщение об ошибке. -прилагаю
OLEDB - это драйвер доступа
ADO - технология доступа, который использует различные драйвера от ODBC до ...

по ошибке, у Вас не обновляемый рекордсет. Тут надо смотреть почему
...
Рейтинг: 0 / 0
14.04.2015, 08:27
| Ответить | Цитировать | Написать  
25 сообщений из 28, страница 1 из 2
  1  все
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / VBA Excel и ADODB.Connection
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&msg=38780772&tid=2173538]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 5ms
check topic access:
 5ms
track hit:
 32ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 57ms
get tp. blocked users:
 1ms
others: 14ms
total:  150ms
созд. / загр.: 150ms
x
x
Закрыть


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