Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
SQL-запросы данным из листа(ов) книги excel из VBA - из макроса в той же книге, работа внутри одной книги excel с макросами
 
#1
21.02.2017 13:53:57
Здравствуйте!
Задача состоит в работе с данными на листах книги excel, с помощью SQL-запросов, без установки чего-либо, кроме самого MS Office. Как я понял, "ADODB.Connection" - единственный вариант, с ним и воюем.
Много где приводятся примеры запросов SELECT, но нигде не обсуждаются UPDATE или INSERT. И уж совсем удачей было обнаружить, что DELETE - в принципе не поддерживается. Непросто откопать, что надо подключить библиотеку "Microsoft ActiveX Data Objects", ещё интереснее - отыскивать какая версия этой библиотеки для чего годится; не очевидно, с какой версии офиса начинается библиотека версии 6.1: если нужна совместимость с несколькими версиями офиса - начинаешь сомневаться.

Вопрос в следующем: при попытке переделать рабочий код с SELECT'ом в код с INSERT'ом - получил: "должен использоваться обновляемый запрос". Может кто-то предложить простой, но полный (не вырванный из контекста) пример рабочего кода для INSERT, а заодно и для UPDATE? Если ещё опишете нюансы, в несколько строчек - вообще замечательно.
 
 
 
#2
21.02.2017 14:30:34
тут
Мой канал
 
 
 
#3
21.02.2017 14:34:30
Доброе время суток.
Не только там, VBA Excel и ADODB.Connection :)
Успехов.
 
 
 
#4
22.02.2017 07:52:36
Код
Option Explicit
Public Sub InsertToXlTable()
    Const sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$1;Extended Properties='Excel 12.0;HDR=YES'"
    Dim cn As New ADODB.Connection
    cn.Mode = adModeShareDenyNone: cn.Open Replace$(sConn, "$1", ThisWorkbook.FullName)
    cn.Execute "Insert Into [Table$] Values (3,'name3', #2010-11-30 13:58:15#)"
    cn.Execute "Update [Table$] Set fname='long name' Where fid=1"
    cn.Close
End Sub
Один из рабочих вариантов (благодарю skais675 и Андрей VG). Я, вроде бы, сам себе отвечаю, но это не так: я пишу для тех, кто наткнётся на эту ветку и сразу найдёт вариант без затрат времени.
В этом примере:
"Table" - название листа книги
"fid", "fname" и "fdate" - названия полей (заголовки - в верхней строке листа "Table", соответственно в A1, B1 и C1)
Поправил в примере значение типа "дата": не перевариваю американский формат - он неоднозначный, "ГГГГ-ММ-ДД" (со временем: "ГГГГ-ММ-ДД чч:мм:сс") - логичнее и однозначнее, чем "ММ/ДД/ГГГГ".

Для лучшего понимания, найти бы ещё книжку или online-документацию, где подобные моменты описаны с точки зрения теории.
 
 
 
#5
22.02.2017 09:53:24
Всё это работает, только если у данных есть "образец": должна быть хотя бы одна строка с данными, по которой excel "определит" типы данных в столбцах.
Задание "формата ячеек" - не влияет ни на что.
Если под заголовком пусто - вышеописанный код вылетит с ошибкой о несовпадении типов данных. Это происходит потому, что значение в первый столбец ("fid") запишется виде текста: там будет не 3, а "'3"!

Кто-нибудь знает, что с этим делать :qstn: Если по условиям задачи (а оно так и есть) надо записывать таблицу с нуля - формировать заголовок и начинать писать данные, то всё рушится  :excl:

практически тот же вопрос: ссылка
Изменено: Rainman0 - 22.02.2017 10:23:42
 
 
 
#6
22.02.2017 10:33:34
Rainman0, как бы Excel никогда Microsoft не позиционировался как база данных. Что вам собственно мешает использовать базу Access?
Обычно же файлы Excel со структурированными таблицами используют как источник данных. Если уж так нужно начинать с пустой таблицы, для примера.
 
 
 
#7
22.02.2017 10:46:37

Андрей, Ассеss в Pro и в Pro+ входит, может если правильная  компания, то имеет только стандарт и как результат только Excel, хотя в большинстве случаях или компания "не правильная" и все есть  или админы не ставят полный пакет. И конечно полностью поддерживаю подход, одни плюсы, от скорости, до удобства

Цитата
Rainman0 написал:
какие недоумки писали ТЗ для MS Excel...
Надеюсь это относилось к рализации конкретной задачи, а не к самому Excel?

Изменено: БМВ - 22.02.2017 10:47:03
По вопросам из тем форума, личку не читаю.
 
 
 
#8
22.02.2017 11:03:42
Цитата
БМВ написал:
Ассеss в Pro и в Pro+ входит
Привет, Михаил.
Вам нужен сам Access или работать с базами Access? Это несколько разные вещи. Со старым форматом mdb можно работать (по крайней мере по Windows 7 включительно) даже без установленного Office. Драйвер Jet.OleDb.4.0 штатно установлен в системе. С новыми достаточно поставить Access Runtime Engine - и спокойно работать с базой.
Сам же Access как программа - это в первую очередь среда разработки, формы, отчёты, макросы и т. д.. Сами базы данных, таблицы, индексы и прочее можно использовать и без него. Клиентом может быть тот же VBScript. :)
 
 
 
#9
22.02.2017 11:44:02
Offtop
Андрей, да у нас тут с вами полное понимание в глобально вопросе, но я наверно не точно выразился ,не поняв что вы имели в виду источник данных, а не оболочку работы с ней. Предвкушал вопрос. что Access не установлен ... :-)
Цитата
Андрей VG написал:
Access Runtime Engine
Про это тоже не все к несчастью знают. Я к слову правильно помню, что если оно установлено, то и все формы и скрипты, отчеты и прочее - работают, просто недоступен режим разработчика?
Изменено: БМВ - 22.02.2017 11:44:27
По вопросам из тем форума, личку не читаю.
 
 
 
#10
22.02.2017 12:54:53
Цитата
БМВ написал:
о и все формы и скрипты, отчеты
Рабоатют в "скомпилированном" формате accde - собственно приложение-клиент баз(ы) данных. Обычная база не откроется. Возможен только такой подход.
 
 
 
#11
22.02.2017 14:19:31
Цитата
БМВ написал:
Андрей, Ассеss в Pro и в Pro+ входит
Дело не в этом: файлы рассылаются сотрудникам разных компаний, производительность их компьютеров, версии офиса и пр. параметры - неизвестны. Заявлено только, что офис старее версии 2010 - не поддерживается, так как сводные, срезы и пр. прелести.
Если использовать "подкачку" из access (без требования установки самого access - это же возможно) - возможностей намного больше, НО - распространять надо уже два файла - парой, вместо одного - для каждого отчёта из нехилого набора. Это исследование - попытка осмыслить возможность избежать "многофайловых" рассылок, средствами обработки данных непосредственно внутри файла excel.
 
 
 
#12
22.02.2017 14:58:55
Цитата
Rainman0 написал:
попытка осмыслить возможность избежать "многофайловых" рассылок
Если база будет не слишком большая, то можно её прятать в Worksheet.CustomProperties (чтение/запись байт-массива). Ну, или как вариант от Игоря..
Можете, как вариант рассмотреть и SQLite for Excel, аналогично, пряча внутрь листа.
 
 
 
#13
22.02.2017 22:24:28
Цитата
Андрей VG написал:
Можете, как вариант рассмотреть и  SQLite for Excel , аналогично, пряча внутрь листа.
Хорошее решение, хотя - уже не один файл с отчётом. SQLite - у клиента должен быть, иначе отчёт - живым не будет.
Цитата
Андрей VG написал:
вариант  от Игоря. .
  - готовая технология распространения вирусов :-) А решение - весьма интересное, особенно - если в сочетании со вторым: запихнуть SQLite внутрь файла Excel, макросом его выложить, после чего - спокойно работать с БД (не забыв предупредить пользователя, что материализовавшиеся ниоткуда файлы - не вирус)
 
 
 
#14
22.02.2017 23:13:11
Цитата
Rainman0 написал:
SQLite - у клиента должен быть, иначе отчёт - живым не будет.
Вы не внимательно читали. Предлагаемое Govert решение полностью содержит dll для работы движка SQLite как на 32, так и на 64 битных системах. Клиенту ничего ставить не надо ;)  С данным набором файлов - это встроенное решение. Чтобы клиент не заморачивался, что откуда, просто распаковывать его для работы во временную папку и использовать, поменяв пути для загрузки dll. Не смотрел эту реализацию (с SQLite работал только через Net или ODBC), но исходно SQLite базу можно размещать и просто в памяти.
Единственно, что само взаимодействие с движком сильно отличается от привычного ADODB - всё достаточно низкоуровневое. С другой стороны движок SQLite на индексах в 3-4 раза быстрее Access (у того собственно SQL часть похоже с 1997 года не менялась).
 
 
 
#15
23.02.2017 13:39:31
Автору топика точно нужна моя надстройка, там даже команда delete есть :)
http://vk.com/club72446554
Excel + SQL = Activetables
 
 
 
#16
23.02.2017 15:03:02
Цитата
PowerBoy написал:
нужна моя надстройка,
PowerBoy, реклама? ТС нужно знание по работе с книгами в SQL стиле. И что он может посмотреть в вашей надстройке без знания пароля?
 
 
 
#17
23.02.2017 17:37:50
Обработка данных внутри книги sql запросами и командами бесплатно, что и требуется автору.
Если он хочет данные с sql сервера тянуть - тогда да.
Excel + SQL = Activetables
 
 
 
Страницы: 1
Наверх