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

Вопрос в следующем: при попытке переделать рабочий код с SELECT'ом в код с INSERT'ом - получил: "должен использоваться обновляемый запрос". Может кто-то предложить простой, но полный (не вырванный из контекста) пример рабочего кода для INSERT, а заодно и для UPDATE? Если ещё опишете нюансы, в несколько строчек - вообще замечательно.
 
тут
 
Доброе время суток.
Не только там, VBA Excel и ADODB.Connection :)
Успехов.
 
Код
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-документацию, где подобные моменты описаны с точки зрения теории.
 
Всё это работает, только если у данных есть "образец": должна быть хотя бы одна строка с данными, по которой excel "определит" типы данных в столбцах.
Задание "формата ячеек" - не влияет ни на что.
Если под заголовком пусто - вышеописанный код вылетит с ошибкой о несовпадении типов данных. Это происходит потому, что значение в первый столбец ("fid") запишется виде текста: там будет не 3, а "'3"!

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

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

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

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

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