Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги / 18 сообщений из 18, страница 1 из 1
05.12.2012, 10:57
    #38065713
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Есть исходная таблица Excel. Необходимо в новую таблицу положить отфильтрованные данные из исходной.
В приложенном файле необходимо заполнить лист ВОЗВРАТ теми данными из листа ДДС где значение ячейки в столбце Статья = ВОЗВРАТ.
Реализовал через цикл... Это долго.
Хотелось бы попробовать через SQL запрос, но не хватает знаний. Примерно понимаю, что здесь наверное будут коннекты и рекордсеты, но собрать работоспособный код из того, что нашел в сети - не получается.
Если не трудно, покажите код для моего примера.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.12.2012, 11:56
    #38065863
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Тверд,

Автофильтр. Критерий для статьи выбрать "возврат". В чём проблема?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.12.2012, 12:10
    #38065898
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
VSVLADТверд,

Автофильтр. Критерий для статьи выбрать "возврат". В чём проблема?

Ну да, выбрать только видимое, вставить... можно и так, но я хочу раскурить как это делается SQL'лем
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.12.2012, 12:43
    #38066000
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Сначала примерно так открываете свою книгу, можно из самого себя. Далее примерно так получаете запросом что нужно:
Код: vbnet
1.
2.
3.
4.
5.
Dim SQLConn As New ADODB.Connection

SQLConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=""Excel 8.0; HDR=Yes;""; Data Source=" & ThisWorkbook.FullName
Лист4.Range("A1").CopyFromRecordset _
            SQLConn.Execute("SELECT [Статья], [Сумма платежа] FROM [ДДС$] WHERE [Статья] = 'ВОЗВРАТ'")
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.12.2012, 13:57
    #38066221
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
VSVLADСначала примерно так открываете свою книгу, можно из самого себя. Далее примерно так получаете запросом что нужно:
Код: vbnet
1.
2.
3.
4.
5.
Dim SQLConn As New ADODB.Connection

SQLConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=""Excel 8.0; HDR=Yes;""; Data Source=" & ThisWorkbook.FullName
Лист4.Range("A1").CopyFromRecordset _
            SQLConn.Execute("SELECT [Статья], [Сумма платежа] FROM [ДДС$] WHERE [Статья] = 'ВОЗВРАТ'")



Ругается:
"Недопустимое имя 'ДДС$'..."
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.12.2012, 14:08
    #38066251
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Вот так заработало:

Код: vbnet
1.
2.
3.
4.
5.
Dim SQLConn As New ADODB.Connection

SQLConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=""Excel 8.0; HDR=Yes;""; Data Source=" & ActiveWorkbook.FullName
Worksheets("ВОЗВРАТ").Range("A1").CopyFromRecordset _
            SQLConn.Execute("SELECT [Статья], [Сумма платежа] FROM [ДДС$] WHERE [Статья] = 'ВОЗВРАТ'")



Большое спасибо!

ПС: На ThisWorkbook.FullName машина давала полное имя PERSONAL.XLSB
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.12.2012, 10:58
    #38067473
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Лиха беда начало.
Вот, решил "ВПР" заменить SQL запросом, но с синтаксисом засада. И на счёт "CopyFromRecordset" не уверен.
По идее, если значение столбца [Финансовая позиция] на листе [ДДС$] = значению столбца ["A:A"] на листе [КАРТА$], тогда [ДДС$].[Статья] = [КАРТА$].["B:B"]. Подскажите как правильно.


Код: vbnet
1.
2.
Worksheets("ДДС").Range("B2").CopyFromRecordset _
SQLConn.Execute("UPDATE ДДС INNER JOIN [КАРТА$] ON [ДДС$].[Финансовая позиция] = [КАРТА$].["A:A"] SET [ДДС$].[Статья] = [КАРТА$].["B:B"]")
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.12.2012, 13:09
    #38067835
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Тверд,

INNER JOIN после сета пишется + Лист "Карта" мы не видим, сложно оценить что вы хотите + "А:А" ну это слишком... тут обычный SQL "аксессовый" грубо говоря, он не поймёт что массив значений это. Столбцы A и B имеют же имена?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.12.2012, 16:42
    #38068290
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
VSVLAD,

Код: vbnet
1.
2.
Worksheets("ДДС").Range("B2").CopyFromRecordset _
SQLConn.Execute("UPDATE [ДДС$] SET [ДДС$].[Статья] = [КАРТА$].[СБ] INNER JOIN [КАРТА$] ON [ДДС$].[Финансовая позиция] = [КАРТА$].[ФП]")



Столбцы я обозвал, INNER JOIN переставил. Не работает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.12.2012, 17:22
    #38068395
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Тверд,

Я ошибся с иннер джойн, верните его как раньше. А вот "Worksheets("ДДС").Range("B2").CopyFromRecordset _" уберите, так как у нас апдейт, а не выборка. Код проверил на тестовом запросе и тестовых данных. Отработал

Лист Карта у меня:

СтатьяЗамена0.3.3.3ТЕКСТ_0.3.3.30.4.6.6ТЕКСТ_0.4.6.6
Запрос такой:
Код: vbnet
1.
SQLConn.Execute "UPDATE [ДДС$] INNER JOIN [КАРТА$]  ON [ДДС$].[Статья] = [КАРТА$].[Статья] SET [ДДС$].[Статья] = [КАРТА$].[Замена]"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.12.2012, 18:04
    #38068477
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
VSVLAD,

Что-то я делаю не правильно...?

Лист ДДС:

СтатьяФинансовая_позиция 2149 1395

Лист КАРТА:

ФПСБ21490.3.3.10.1013951.5.8.1

Запрос:

Код: vbnet
1.
2.
SQLConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=""Excel 8.0; HDR=Yes;""; Data Source=" & ActiveWorkbook.FullName
SQLConn.Execute "UPDATE [ДДС$] INNER JOIN [КАРТА$] ON [ДДС$].[Финансовая_позиция] = [КАРТА$].[ФП] SET [ДДС$].[Статья] = [КАРТА$].[СБ]"



Ошибка: несоответствие типа в выражении.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.12.2012, 20:28
    #38068682
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Тверд,

Ваш код отработал правильно. У меня Excel 2010. Сохранил ваши данные в файл формата Excel 2003. ADODB 2.8 версия. Какие ваши параметры, и соберите тестовый файл.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.12.2012, 08:57
    #38068977
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
VSVLAD,

Хм... SQL запрос отработал, но только после вот такой свистелки для листа [ДДС$]:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
xt = Application.WorksheetFunction.CountA(Range("A:A"))
Set xCellName = Range("B2")
    For i = 2 To xt
        xDK = xCellName.Text
        xCellName.FormulaR1C1 = xDK
        Set xCellName = xCellName.Offset(1, 0)
    Next



После отработки этого цикла, данные в столбце выравниваются по правому краю. Я, кстати, сталкивался с этим и раньше, когда применял ВПР функцию. Решал через СЦЕПИТЬ с "Ё", например с последующим удалением "Ё" через автозамену. Эффект тот же - данные выравниваются по правому краю и начинают корректно восприниматься функцией. При этом формат данных, насколько я могу судить, не меняется. Может подскажете с чем это связано.

Огромное спасибо за терпение и помощь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.12.2012, 09:14
    #38068990
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Тверд,

Такое бывает, когда выгружаешь данные не с тем типом. Например с SQL сливал данные типа VARCHAR, а в ячейки цифры и данные выравнивались по левому краю. Любая формула с ячейкой выдавала ошибки. Если в ячейку перейти как в режим редактирования и закрепить Enter'ом - т.е. эксель преобразовывал его в число. Тоже самое можно было использовать ячейку так = A1 + 0 - т.е. прибавляя ноль. Возможно у вас такая же проблема, что тип в ячейке отличается?...
Ну надо посмотреть на данные которые есть. Откуда данные получаете, вручную вносите или с сервера?... Ну и тестовый проект выложите
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.12.2012, 09:55
    #38069035
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
VSVLAD,

Точно, на КАРТЕ (создан вручную) VarType = 5, а на ДДС (выгружен) VarType = 8. Сейчас я с этим разберусь.
Подскажите, как корректно ADODB.Connection выгрузить, а то у меня в Project Exsplorer'е четыре раза висит книга с которой я коннектился. Set = Nohing, .Close?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.12.2012, 10:41
    #38069100
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Тверд,

AdoConnection.Close и можно для уверенности Nothing сделать, хотя рекордсет если локально в процедуре объявлен, сам закроется и уничтожится после выхода из процедуры. А книга висит, потому что книгу помимо wb.Close ещё и Set wb = Nothing сделать, тогда она пропадёт.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.12.2012, 11:06
    #38069146
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
VSVLADТверд,

AdoConnection.Close и можно для уверенности Nothing сделать, хотя рекордсет если локально в процедуре объявлен, сам закроется и уничтожится после выхода из процедуры. А книга висит, потому что книгу помимо wb.Close ещё и Set wb = Nothing сделать, тогда она пропадёт.

Выполняю вот этот код...
Код: vbnet
1.
2.
3.
4.
5.
SQLConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=""Excel 8.0; HDR=Yes;""; Data Source=" & ActiveWorkbook.FullName
SQLConn.Close
Set SQLConn = Nothing
ActiveWorkbook.Close
Set Workbooks = Nothing


и закрытая книга висит в Project Exsplorer'е как открытая.
wb я ни где не объявлял, на
Код: vbnet
1.
Set Workbook = Nothing

машина крутит пальцем у виска.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.12.2012, 11:08
    #38069150
Тверд
Тверд
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги
Виноват, выполняется вот этот код
Код: vbnet
1.
2.
3.
4.
SQLConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=""Excel 8.0; HDR=Yes;""; Data Source=" & ActiveWorkbook.FullName
SQLConn.Close
Set SQLConn = Nothing
ActiveWorkbook.Close
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги / 18 сообщений из 18, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 154ms
Закрыть
start [/forum/topic.php?fid=60&mobile=1&tid=2157281]:
 0ms
get settings:
 11ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 34ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 2ms
others: 16ms
total:  154ms
x
x
Закрыть


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