SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги / Visual Basic

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

18 сообщений из 18, страница 1 из 1

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38065713

Тверд

Гость

Есть исходная таблица Excel. Необходимо в новую таблицу положить отфильтрованные данные из исходной.
В приложенном файле необходимо заполнить лист ВОЗВРАТ теми данными из листа ДДС где значение ячейки в столбце Статья = ВОЗВРАТ.
Реализовал через цикл... Это долго.
Хотелось бы попробовать через SQL запрос, но не хватает знаний. Примерно понимаю, что здесь наверное будут коннекты и рекордсеты, но собрать работоспособный код из того, что нашел в сети - не получается.
Если не трудно, покажите код для моего примера.

...

Рейтинг:

0 / 0

05.12.2012, 10:57

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38065863

VSVLAD

Участник

Откуда: Краснодар

Сообщения: 1 379

Рейтинг: 0 / 0

Тверд,

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

...

Рейтинг:

0 / 0

05.12.2012, 11:56

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38065898

Тверд

Гость

VSVLADТверд,

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

Ну да, выбрать только видимое, вставить... можно и так, но я хочу раскурить как это делается SQL'лем

...

Рейтинг:

0 / 0

05.12.2012, 12:10

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38066000

VSVLAD

Участник

Откуда: Краснодар

Сообщения: 1 379

Рейтинг: 0 / 0

Сначала примерно так открываете свою книгу, можно из самого себя. Далее примерно так получаете запросом что нужно:

Код: 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, 12:43

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38066221

Тверд

Гость

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, 13:57

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38066251

Тверд

Гость

Вот так заработало:

Код: 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

05.12.2012, 14:08

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38067473

Тверд

Гость

Лиха беда начало.
Вот, решил "ВПР" заменить 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, 10:58

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38067835

VSVLAD

Участник

Откуда: Краснодар

Сообщения: 1 379

Рейтинг: 0 / 0

Тверд,

INNER JOIN после сета пишется + Лист "Карта" мы не видим, сложно оценить что вы хотите + "А:А" ну это слишком... тут обычный SQL "аксессовый" грубо говоря, он не поймёт что массив значений это. Столбцы A и B имеют же имена?

...

Рейтинг:

0 / 0

06.12.2012, 13:09

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38068290

Тверд

Гость

VSVLAD,

Код: vbnet

1.
2.

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

Столбцы я обозвал, INNER JOIN переставил. Не работает.

...

Рейтинг:

0 / 0

06.12.2012, 16:42

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38068395

VSVLAD

Участник

Откуда: Краснодар

Сообщения: 1 379

Рейтинг: 0 / 0

Тверд,

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

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

СтатьяЗамена0.3.3.3ТЕКСТ_0.3.3.30.4.6.6ТЕКСТ_0.4.6.6
Запрос такой:

Код: vbnet

SQLConn.Execute "UPDATE [ДДС$] INNER JOIN [КАРТА$]  ON [ДДС$].[Статья] = [КАРТА$].[Статья] SET [ДДС$].[Статья] = [КАРТА$].[Замена]"

...

Рейтинг:

0 / 0

06.12.2012, 17:22

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38068477

Тверд

Гость

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, 18:04

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38068682

VSVLAD

Участник

Откуда: Краснодар

Сообщения: 1 379

Рейтинг: 0 / 0

Тверд,

Ваш код отработал правильно. У меня Excel 2010. Сохранил ваши данные в файл формата Excel 2003. ADODB 2.8 версия. Какие ваши параметры, и соберите тестовый файл.

...

Рейтинг:

0 / 0

06.12.2012, 20:28

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38068977

Тверд

Гость

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, 08:57

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38068990

VSVLAD

Участник

Откуда: Краснодар

Сообщения: 1 379

Рейтинг: 0 / 0

Тверд,

Такое бывает, когда выгружаешь данные не с тем типом. Например с SQL сливал данные типа VARCHAR, а в ячейки цифры и данные выравнивались по левому краю. Любая формула с ячейкой выдавала ошибки. Если в ячейку перейти как в режим редактирования и закрепить Enter'ом - т.е. эксель преобразовывал его в число. Тоже самое можно было использовать ячейку так = A1 + 0 - т.е. прибавляя ноль. Возможно у вас такая же проблема, что тип в ячейке отличается?...
Ну надо посмотреть на данные которые есть. Откуда данные получаете, вручную вносите или с сервера?... Ну и тестовый проект выложите

...

Рейтинг:

0 / 0

07.12.2012, 09:14

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38069035

Тверд

Гость

VSVLAD,

Точно, на КАРТЕ (создан вручную) VarType = 5, а на ДДС (выгружен) VarType = 8. Сейчас я с этим разберусь.
Подскажите, как корректно ADODB.Connection выгрузить, а то у меня в Project Exsplorer'е четыре раза висит книга с которой я коннектился. Set = Nohing, .Close?

...

Рейтинг:

0 / 0

07.12.2012, 09:55

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38069100

VSVLAD

Участник

Откуда: Краснодар

Сообщения: 1 379

Рейтинг: 0 / 0

Тверд,

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

...

Рейтинг:

0 / 0

07.12.2012, 10:41

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38069146

Тверд

Гость

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

Set Workbook = Nothing

машина крутит пальцем у виска.

...

Рейтинг:

0 / 0

07.12.2012, 11:06

| Ответить | Цитировать | Написать

SQL запрос на заполнение листа Excel данными из соседнего листа этой же книги

#38069150

Тверд

Гость

Виноват, выполняется вот этот код

Код: 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

07.12.2012, 11:08

| Ответить | Цитировать | Написать

18 сообщений из 18, страница 1 из 1

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=60&fpage=66&tid=2157281]:	0ms
get settings:	10ms
get forum list:	12ms
check forum access:	4ms
check topic access:	4ms
track hit:	34ms
get topic data:	11ms
get forum data:	3ms
get page messages:	49ms
get tp. blocked users:	1ms
others:	14ms

total:	142ms