ADO - класс для работы с данными Excel при помощи SQL - Страница 4 - Мир MS Excel

Регистрация

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

= Мир MS Excel/ADO - класс для работы с данными Excel при помощи SQL - Страница 4 - Мир MS Excel

Старая форма входа

Страница 4 из 4 « 1 2 3 4
Модератор форума: _Boroda_, китин

Мир MS Excel » Вопросы и решения » Готовые решения » ADO - класс для работы с данными Excel при помощи SQL

ADO - класс для работы с данными Excel при помощи SQL

Дата: Вторник, 15.09.2015, 09:45 | Сообщение № 61

Группа: Пользователи

Ранг: Новичок

Сообщений: 44

Репутация: -10 ±

Замечаний: 80% ±

Excel 2013

Pelena, первые 2-ва поста- это были вопросы и просьбы к готовым решениям. А вот с 3-го Поста уже было описание отдельной проблеммы и просьба о помощи уже по делу.....

Ответить

Дата: Вторник, 15.09.2015, 11:31 | Сообщение № 62

Группа: Редакторы

Ранг: Обитатель

Сообщений: 431

Репутация: 193 ±

Замечаний: 0% ±

Цитата Pelena, 15.09.2015 в 09:37, в сообщении № 60

Неужели? Одна просьба на 13 постов?

она еще мне на почту написала

1. спасибы принимаются)
2. не понятно только, чего от меня хотят. Или думают, что "прилетит к нам волшебник в голубом вертолете и бесплатно покажет кино"?

"Экселем я больше не занимаюсь". У меня его даже на компьютере сейчас нет.
Последний раз видел его год назад или больше.

Удачи

Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук

YM 41001156540584 / WM WMR R21924176233

https://github.com/nervgh/vba

Ответить

Дата: Вторник, 15.09.2015, 12:21 | Сообщение № 63

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2739

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата SvetaS, 15.09.2015 в 07:07, в сообщении № 58

не выгружает все данные с листа.
А именно на листе 156 000 строк , а он в массив грузит лишь 65536 методом ADO.ToArray() girl_sad
т.е.

Dim ADO As New ADO
Dim Arr As Variant
ADO.DataSource = "d:\ÀÒÁ àâãóñò 2015_ðûáà_649.xlsm"
ADO.Query ("SELECT * FROM [лист1$A:AC]")

Arr = ADO.ToArray()

т.е. в окне отладчика "Locals"
Arr(65536) - максимальное число
А нужно чтобы обрабатывались данные размером хотя бы до 200 000 - 350 000 строк.

Погуглил по строке: "ado.recordset excel 65536" (можно повторить самостоятельно). Показалось, что одна из ссылок (вторая в выборке) может помочь: http://forum.chandoo.org/threads....e.12492

Как я понял, при выборке с листа Excel ограничение 65536 срабатывает, если в качестве источника указан диапазон, т.е. после имени листа (после $) указаны колонки или угловые ячейки. Если же указано только имя листа (с заключительным $), то должны вернуться строки свыше 65536 - до стандартного миллионного лимита в версиях 2007+.

Т.е вместо:
[vba]

Код

ADO.Query ("SELECT * FROM [лист1$A:AC]")

[/vba]
напишите:
[vba]

Код

ADO.Query ("SELECT * FROM [лист1$]")

[/vba]

P.S. Ну, и не стесняемся использовать Google самостоятельно!

МОИ: Ник, Tip box: 41001663842605

Ответить

Дата: Вторник, 15.09.2015, 14:19 | Сообщение № 64

Группа: Пользователи

Ранг: Новичок

Сообщений: 44

Репутация: -10 ±

Замечаний: 80% ±

Excel 2013

Gustav, огромное спасибо!! respect

respect

respect

Ура хотябы класс заработал......... girl_dance

girl_dance

girl_dance

Ответить

Дата: Воскресенье, 27.09.2015, 15:06 | Сообщение № 65

Группа: Пользователи

Ранг: Новичок

Сообщений: 44

Репутация: -10 ±

Замечаний: 80% ±

Excel 2013

Gustav, Добрый День! Можно ещё один вопрос , по данной теме.
Подскажите, когда не знаешь имя листа при работе с закрытой книгой (когда имя книги берём через диалог), как тогда можно загрузить данные - вернее как тогда нужно изменить строку [vba]

Код

ADO.Query ("SELECT * FROM [лист1$]")

[/vba]
Помогите, пожалуйста.
Заранее огромное спасибо.... pray

pray

:pray:

pray

:pray:

pray

[moder] SvetaS, прочитайте уже наконец Правила форума! Новый вопрос - новая тема. Не вынуждайте закрывать тему![/moder]

Сообщение отредактировал Pelena - Воскресенье, 27.09.2015, 15:13

Ответить

Дата: Воскресенье, 27.09.2015, 15:22 | Сообщение № 66

Группа: Пользователи

Ранг: Новичок

Сообщений: 44

Репутация: -10 ±

Замечаний: 80% ±

Excel 2013

Модератор: - это вопрос по данной теме "ADO - класс для работы с данными Excel при помощи SQL", я ничего не меняла . Не понимаю, зачем Вам "дублировать темы"
[moder]Это - ветка ГОТОВЫЕ РЕШЕНИЯ. Вопросы задавайте в разделе ВОПРОСЫ ПО EXCEL. За нарушение п. 5b Правил форума Вам бан на сутки для изучения Правил[/moder]

Сообщение отредактировал Pelena - Воскресенье, 27.09.2015, 15:31

Ответить

Дата: Воскресенье, 27.09.2015, 15:24 | Сообщение № 67

Группа: Пользователи

Ранг: Новичок

Сообщений: 44

Репутация: -10 ±

Замечаний: 80% ±

Excel 2013

Pelena, и более того вопрос к то й же самой строчки кода (которая тут и обсуждается)......

Ответить

Дата: Воскресенье, 27.09.2015, 15:28 | Сообщение № 68

Группа: Пользователи

Ранг: Новичок

Сообщений: 44

Репутация: -10 ±

Замечаний: 80% ±

Excel 2013

Pelena

Цитата

Неужели нельзя решать вопрос в одном месте, а не разрываться на части.

?

Ответить

Дата: Пятница, 02.10.2015, 16:31 | Сообщение № 69

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2739

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата SvetaS, 27.09.2015 в 15:06, в сообщении № 65

...когда не знаешь имя листа при работе с закрытой книгой...

В закрытой книге Excel при помощи ADO и ADOX можно получить общий список (единый) рабочих листов и именованных диапазонов рабочей книги:
[vba]

Код

Sub io()

       Dim cnn As Object 'New ADODB.Connection
       Dim cat As Object 'New ADOX.Catalog
       Dim tbl As Object

       Set cnn = CreateObject("ADODB.Connection")
       cnn.Open _
       "Provider=Microsoft.ACE.OLEDB.12.0;" & _
       "Data Source=" & "C:\МояПапка\МойФайл.xlsx" & ";" & _
       "Extended Properties='Excel 12.0;HDR=No'"

       Set cat = CreateObject("ADOX.Catalog")
       cat.ActiveConnection = cnn
       For Each tbl In cat.Tables
           Debug.Print tbl.Name
       Next tbl

End Sub

[/vba]
Имена рабочих листов обычно можно выделить из общего списка по знаку доллара в конце наименования, хотя никто не запрещает включать знак $ в наименование листа (хоть первым символом!). Поэтому требуется более изысканный анализ строки имени на содержание $. Но это не спасает от главной неприятности - последовательность листов и диапазонов в коллекции Tables в общем случае не соответствует последовательности листов в коллекции Worksheets. Как я понял, в ADOX.Catalog имена в коллекции Tables автоматически сортируются по алфавиту.

Поэтому если при уже известном имени рабочей книги нужно узнать имя первого рабочего листа, то следует ненадолго открыть эту книгу (например, в невидимом сеансе) и без лишних наворотов и головной боли выяснить Worksheets(1).Name. Лично я другого способа не вижу...

Цитата SvetaS, 27.09.2015 в 15:06, в сообщении № 65

...когда не знаешь имя листа при работе с закрытой книгой...

В закрытой книге Excel при помощи ADO и ADOX можно получить общий список (единый) рабочих листов и именованных диапазонов рабочей книги:
[vba]

Код

Sub io()

       Dim cnn As Object 'New ADODB.Connection
       Dim cat As Object 'New ADOX.Catalog
       Dim tbl As Object

       Set cnn = CreateObject("ADODB.Connection")
       cnn.Open _
       "Provider=Microsoft.ACE.OLEDB.12.0;" & _
       "Data Source=" & "C:\МояПапка\МойФайл.xlsx" & ";" & _
       "Extended Properties='Excel 12.0;HDR=No'"

       Set cat = CreateObject("ADOX.Catalog")
       cat.ActiveConnection = cnn
       For Each tbl In cat.Tables
           Debug.Print tbl.Name
       Next tbl

End Sub

[/vba]
Имена рабочих листов обычно можно выделить из общего списка по знаку доллара в конце наименования, хотя никто не запрещает включать знак $ в наименование листа (хоть первым символом!). Поэтому требуется более изысканный анализ строки имени на содержание $. Но это не спасает от главной неприятности - последовательность листов и диапазонов в коллекции Tables в общем случае не соответствует последовательности листов в коллекции Worksheets. Как я понял, в ADOX.Catalog имена в коллекции Tables автоматически сортируются по алфавиту.

Поэтому если при уже известном имени рабочей книги нужно узнать имя первого рабочего листа, то следует ненадолго открыть эту книгу (например, в невидимом сеансе) и без лишних наворотов и головной боли выяснить Worksheets(1).Name. Лично я другого способа не вижу...Gustav

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Пятница, 02.10.2015, 16:42

Ответить

Сообщение

Цитата SvetaS, 27.09.2015 в 15:06, в сообщении № 65

...когда не знаешь имя листа при работе с закрытой книгой...

В закрытой книге Excel при помощи ADO и ADOX можно получить общий список (единый) рабочих листов и именованных диапазонов рабочей книги:
[vba]

Код

Sub io()

       Dim cnn As Object 'New ADODB.Connection
       Dim cat As Object 'New ADOX.Catalog
       Dim tbl As Object

       Set cnn = CreateObject("ADODB.Connection")
       cnn.Open _
       "Provider=Microsoft.ACE.OLEDB.12.0;" & _
       "Data Source=" & "C:\МояПапка\МойФайл.xlsx" & ";" & _
       "Extended Properties='Excel 12.0;HDR=No'"

       Set cat = CreateObject("ADOX.Catalog")
       cat.ActiveConnection = cnn
       For Each tbl In cat.Tables
           Debug.Print tbl.Name
       Next tbl

End Sub

[/vba]
Имена рабочих листов обычно можно выделить из общего списка по знаку доллара в конце наименования, хотя никто не запрещает включать знак $ в наименование листа (хоть первым символом!). Поэтому требуется более изысканный анализ строки имени на содержание $. Но это не спасает от главной неприятности - последовательность листов и диапазонов в коллекции Tables в общем случае не соответствует последовательности листов в коллекции Worksheets. Как я понял, в ADOX.Catalog имена в коллекции Tables автоматически сортируются по алфавиту.

Поэтому если при уже известном имени рабочей книги нужно узнать имя первого рабочего листа, то следует ненадолго открыть эту книгу (например, в невидимом сеансе) и без лишних наворотов и головной боли выяснить Worksheets(1).Name. Лично я другого способа не вижу...

Автор - Gustav
Дата добавления - 02.10.2015 в 16:31

Дата: Пятница, 13.11.2015, 15:35 | Сообщение № 70

Группа: Проверенные

Ранг: Участник

Сообщений: 100

Репутация: 31 ±

Замечаний: 0% ±

2003

Цитата Gustav, 02.10.2015 в 16:31, в сообщении № 69

Поэтому если при уже известном имени рабочей книги нужно узнать имя первого рабочего листа,

В принципе можно обойтись вообще без имени листа.
Запрос к первому листу будет такой:

[vba]

Код

SELECT * FROM [A1:A65536]

[/vba]

Excel + SQL = ActiveTables (http://vk.com/ExcelSQL)

Ответить

Дата: Четверг, 09.02.2017, 15:17 | Сообщение № 71

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Подскажите, пожалуйста, для чего в данной строке:
"Provider='Microsoft.Jet.OLEDB.4.0';Data Source=':1';Extended Properties='Excel 4.0;HDR=:2;IMEX=1';"
используются одинарные кавычки? Спасибо.

Ответить

Дата: Четверг, 09.02.2017, 15:35 | Сообщение № 72

Группа: Проверенные

Ранг: Участник

Сообщений: 100

Репутация: 31 ±

Замечаний: 0% ±

2003

Цитата Guest2029, 09.02.2017 в 15:17, в сообщении № 71 ()

используются одинарные кавычки?

Вся строка отделена в двойные кавычки, поэтому внутри для отдельных строк используют одинарные, более того иногда еще и скобки квадратные пользуют, а иногда и фигурные.

Excel + SQL = ActiveTables (http://vk.com/ExcelSQL)

Ответить

Дата: Четверг, 09.02.2017, 15:52 | Сообщение № 73

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Цитата Gustav, 02.10.2015 в 16:31, в сообщении № 69 ()

"Provider=Microsoft.ACE.OLEDB.12.0;"

Почему они здесь не используются, например.

Ответить

Дата: Четверг, 09.02.2017, 16:01 | Сообщение № 74

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2739

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Используются.

Цитата

"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & "C:\МояПапка\МойФайл.xlsx" & ";" & _
"Extended Properties='Excel 12.0;HDR=No'"

= полная строка =
[vba]

Код

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\МояПапка\МойФайл.xlsx;Extended Properties='Excel 12.0;HDR=No'

[/vba]
P.S. Если правильно помню, они обязательны при наличии пробела внутри значения, как в 'Excel 12.0;HDR=No'. Или если в пути к файлу будут пробелы,
типа Data Source='C:\Моя Папка\Мой Файл.xlsx'. В других случаях - по желанию. В рассматриваемом выше случае с подстановочными параметрами :1 и :2 одинарные кавычки гарантируют правильность обработки, если параметры придут с пробелами.

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Четверг, 09.02.2017, 16:14

Ответить

Дата: Четверг, 09.02.2017, 16:13 | Сообщение № 75

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Gustav, Спасибо, Вам!

Ответить

Дата: Четверг, 02.11.2017, 12:45 | Сообщение № 76

Группа: Пользователи

Ранг: Прохожий

Сообщений: 1

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Доброго времени суток! А данный класс позволяет в запросе указать имя поля, если в имени поля присутствуют точки, пробелы и текст кириллицы? Со звездочкой выдает все поля... А указать явно не получается(

Ответить

Дата: Четверг, 02.11.2017, 14:10 | Сообщение № 77

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2739

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата emlen, 02.11.2017 в 12:45, в сообщении № 76 ()

если в имени поля присутствуют точки, пробелы и текст кириллицы?

Должен позволять. Попробуйте имя такого поля взять в квадратные скобки: SELECT [поле с пробелом] FROM ...

МОИ: Ник, Tip box: 41001663842605

Ответить

Дата: Четверг, 18.10.2018, 12:25 | Сообщение № 78

Группа: Друзья

Ранг: Ветеран

Сообщений: 550

Репутация: 167 ±

Замечаний: 0% ±

365

emlen,
в самом запросе, в именах полей, точки (".") должны быть заменены на решетку ("#")

Ответить

Мир MS Excel » Вопросы и решения » Готовые решения » ADO - класс для работы с данными Excel при помощи SQL

Страница 4 из 4
«
1
2
3
4

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