Страницы: Пред. 1 2 3 4 5 6 7 8 9 След.
RSS
Excel и Access могут облегчить друг другу жизнь?, как можно совмещать их способности
 
и замечена одна неприятность в Excel:
если сохранить файл под др именем и обновить Подключения (делаю, как по линку из #72) - то ListObject, который длинный, не обновляется почему-то, причём с остальными в книге всё ок... а этот большой, возможно не успевает... пробовала различные предложения отсюда... но помогает только если сохранить файл - закрыть его - открыть заново - Обновить всё... возможно, я что-то не принимаю во внимание...
НО если кто-то сталкивался с этим неудобством, - возможно, есть выход не закрывать-открывать лишний раз?
=====
сохранила файл под новым именем, обновила макросом строку подключения на новый путь с новым именем файла, даже обновила всё руками - длинный зарос(возможно, кстати, что и сам запрос не совсем оптимальный) потерял все данные и не обновил ничего... никакие манипуляции не помогли, кроме как сохранить,закрыть файл, открыть его заново и снова обновить руками - только так всё работает... пришлось вспомнить
Цитата
Андрей VG написал:  такую проблему файловых БД, как зависимость всех ваших строк подключения для файловых БД от местоположения файла.
... а, возможно, это и просто особенности то ли "движка", то ли драйвера Access-ного при использовании в Excel... способ жить с этим описала на всякий случай, как побороть не знаю - хотя так жить не совсем удобно... пока что так (похоже, проблема, отмеченная Андреем, не из приятных)
Изменено: JeyCi - 14.09.2015 18:10:40
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Может файл покажете?
Неизлечимых болезней нет, есть неизлечимые люди.
 
TheBestOfTheBest , он рабочий - долго пыталась его уменьшить, чтобы осталась лишь репрезентация проблемы - так у меня это и не получилось... но причина такого явления вскрыта
Цитата
JeyCi написал: если сохранить файл под др именем и обновить Подключения (делаю, как по линку из #72) - то ListObject, который длинный, не обновляется почему-то, причём с остальными в книге всё ок...
- чтобы избавиться от такого - надо написать ДРУГОЙ запрос... исходила из двух предпосылок:
1) "логически интерполируя" :) пример из #79
Цитата
Андрей VG написал: только помнить о декартовом произведении
2) цитата от Gustav на excelworld (там же линк первоисточника)
Цитата
о том что не стоит писать конструкции типа where xxx in (select xxx from yyy  where ...) а нужно их джоинить знают уже наверное все
переписала
Скрытый текст
на
Скрытый текст
по сути, эти два запроса дают идентичные выборки (больше уменьшить не смогла, чтобы лучше была видна структура, просто условий много) -  но суть оптимизации в комментах...
проблема решена: сохранение под новым именем, обновление строк Подключения и далее обновление самих запросов в этом же файле - происходит без проблем (без его закрытия)
p.s.
пошла переносить логику в рабочий файл...  8)
Изменено: JeyCi - 14.09.2015 18:37:38
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
мои 3 копейки:
в powerpivot 2010 действительно надо заходить в сам PP (даже не жать обновление, а зайти и выйти) а потом в пивот построенный по его данным чтобы появились обновлённые данные. В 2013 это исправили: обновление из пивота на листе вызавает обновление кэша PP без открытия надстройки.

про макро-рекордер в access: есть зато готовые кнопки (в 2003 точно). Рисуем на форме (больше негде :)) кнопку и назначаем ей действие из списка, потом читаем и правим код. Метод №2: аналогично вызываются все стандартные (наверное и пользовательские?) менюшки из кода. Вот вам и имитация макро-рекордера. При отсутствии интернета часто спасало :)
Программист - человек, который решает задачу, о которой никто не знает, таким способом, который никто не понимает.
 
Цитата
A.nod написал: Вот вам и...
спасибо  :)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
очень интересные 2 способа (счёта по порядку (для нумерации строк)
- в ветке Автоматизация заполнения накладных (#5 через SQL )
Код
Select (Select Count(*)+1 
      From [Лист1$] As t2 
      Where (t2.[Наименование товара]<t1.[Наименование товара]) And (t2.[№ накладной]=?)
      )  As [№ пп],
[Наименование товара],[Цена],[Количество],[Цена]*[Количество] As [Сумма]
From [Лист1$] As t1
Where [№ накладной]=?
Order By [Наименование товара]
(и 2-й ещё проще - там же)
Цитата
Встать на таблицу->Конструктор->(Свойства) Размер таблицы->включить левый столбец в таблицу
--->поместить туда формулу... например,  =СТРОКА()-СТРОКА($H$3)
- всё нумеруется автоматически (спасибо всем, поучаствовавшим в решении данного вопроса  :) )
Изменено: JeyCi - 06.10.2015 16:50:10
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
С помощью ADO можно получить список листов и именованных диапазонов закрытой книги (ZVI)
Изменено: JeyCi - 07.10.2015 10:51:53
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
примеры Запросов (Access) на:
1) Создание Таблицы
2) Добавление в Таблицу
(# 13 - в теме: Производительность итоговых функций SQL через ADODB.Recordset, или лучше циклами - от Михаил Лебедев)
----------------
1) программно я делаю так
Скрытый текст

2) программно пример от Андрей VG - #6 (Insert Into), #16 (AddNew) в теме: импорт excel -access. VBA
Изменено: JeyCi - 26.10.2015 09:43:23
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
ArrayToRecordset (#5) - на всякий случай (Excel)...
Изменено: JeyCi - 26.10.2015 09:43:38
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
окошко для отправки запросов к БД (на примере MySQL) - вылилось в надстройку  :) у fed_lviv (спасибо и ему)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
вот только об одном иногда начинаю мечтать... (может, кто-нибудь знает?) - можно ли как-то в самом SQL-запросе в Excel делать комментарии? (как в коде vba)... чтобы удобнее вспоминать было, когда он длинный получается... ??
Изменено: JeyCi - 26.10.2015 10:15:49
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
можно ли как-то в самом SQL-запросе в Excel делать комментарии?
Конэчно можно! Только синтаксис может различаться для разных серверов. В MySLQ это:
Код
SELECT 1+1;     # Этот комментарий продолжается до конца строки
SELECT 1+1;     -- Этот комментарий продолжается до конца строки
SELECT 1 /* Это комментарий в строке */ + 1;
SELECT 1+
/*
Это многострочный
комментарий
*/
1;
Неизлечимых болезней нет, есть неизлечимые люди.
 
Цитата
TheBestOfTheBest написал: Конэчно можно!
к сожалению в Access (полагаю, и в Excel - т.к. одинаковый движок) такие варианты не проходят... но буду иметь ввиду для др... спасибо
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
грядущее будущее: встраивают в xl2016 и Power Query - пока надстройкой есть для xl2013, xl2010_SP1... напомнила мне сегодня PQ отдельные способности Access'a (хотя язык другой - M) - впечатления очертила здесь в #16 (в ветке: Выборка повторяющихся наборов данных)...

и ещё впечатления

и ещё впечатления

примеры использования Power Query:

user-friendly момент PQ:

а) "с помощью PQ можно читать файлы из одной папки, прописывая доп. условия в Fields и Conditions - при добавлении нового файла в папку, запрос автоматом будет его обрабатывать по общему признаку (что очень удобно, если источники появляются по ходу - тогда складировать их в одно общее место и обрабатывать с помощью PQ)"
б) + работа с JSON-файлами и др. возможа без рукописного парсинга, по слухам...
подборка линков, которые мне понравились

user-friendly момент PP:
"если данных много, то запрос можно/нужно включать в Модель данных, а не выводить на лист,
и получать ответ в виде Сводной на Лист"
примеры использования Power Pivot

много примеров здесь
Изменено: JeyCi - 22.08.2017 18:14:21
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
раздел Access  Query: Tips and Techniques (видео на англ.) - показано, как делать в Access... потом можно просто брать запрос для использования его в xl
p.s. элегантный Get Details and Totals in one Query... наверно, и остальные
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Данные из Аксес в Форме, Запрос с условием
спасибо, Ronin751
Изменено: JeyCi - 03.04.2016 05:06:54
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
спасибо, Ronin751
Да там же не мне, а The Prist говорить спасибо нужно.))
В свою очередь хотел бы и Вам выразить благодарность за чудную тему. Много можно черпнуть полезного.
Давиче нашел обучалку (не помню точно где) . Залил сюда.  
 
Цитата
Ronin751 написал: Залил сюда.
да, Мартина Грабера многие советуют... sql.ru - тоже её опубликовали - Понимание SQL... всем спасибо)
Изменено: JeyCi - 03.04.2016 05:07:15
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Выгрузка данных из Акссесс на основании данных Листбокс
Скрытый текст
Изменено: JeyCi - 03.04.2016 05:10:46
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
CrazyRabbit написал: Я хочу создать Excel.xlsb, который объединит новые файлы, откроет Access. Тот при открытии вытянет из Excel.xlsb все данные. Далее Access закрывается.
Открыть Access средствами VBA Excel
Спасибо, Doober
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
не нашла по ветке линк (может людям пригодится)
- моё знакомство с SQL-запросами начиналось примерно в те времена (посты в районе тех дат): Получение данных из SQL по условию в ячейках... жаль картинки уже не сохранились, но B.Key описал словами получение Параметрического запроса... непараметрический - ещё проще (первых 2 пункта)... - к вопросу: откуда начиналось моё знакомство с SQL?...
теория - Использование Microsoft Query ... --> ОСНОВЫ смотрела здесь: SQL за 10 минут...
практика бОльшей частью от TheBestOfTheBest и Андрей VG (особенно в части программирования выполнения запросов, хотя если их настроить - то и код в принципе не нужен - но иногда, полезно и кодом)...  ориентировалась на Поиск  ;)  - кто захочет знакомства с SQL с нуля - много ответов уже не раз озвучены на форуме (и ветки посещённые мной в те времена  :oops:) - кажется побывала везде (спасибо всем, кто делился опытом)
P.S.
файлы с запросами см. по конкретному пути файла - обычно использую файл в папке C:\1 (т.к. в запросе прописывается путь файла), сам запрос в файле см. Данные - Подключения - Свойства - Определения --> см. SQL-запрос... строку подключения можно изменить там же... удобнее всего запросы выполнять вкладка Данные - Получение внешних данных - из Microsoft Query... обновляется по ПКМ на таблице - Обновить
(на этот пост буду отправлять всех с вопросами что и где)
Изменено: JeyCi - 10.03.2017 21:54:01
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
~ 6 Правил Нормализации (для возможности оперирования данными по принципу Баз Данных)
p.s. и тема в приёмах Редизайнер таблиц и на форуме (redesigner Формулой - по мотивам из комментов в приёмах )- для приведения таблиц в плоский вид
Изменено: JeyCi - 05.06.2016 07:31:44
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
пример SQL: TRANSFORM (...) PIVOT я оставила здесь (#12) - Расчет остатка на счету через PowerPivot или сводную таблицу

PQ and PP

P.S. также пример в ветке Накопительный итог Power Pivot/Power Query
Изменено: JeyCi - 23.10.2016 15:36:19
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Использование библиотеки ADO (Microsoft ActiveX Data Object)

Вывод данных из Аксес в таблицу Эксель, Позиционирование
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Подсчёт количества стран, которые одновременно экспортируют два разных товара
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 Трансформация (консолидация из папок, консолидация из файлов, из сводной в плоскую, создание функции PP...) данных с помощью PowerPivot от Сергея Ожиганова на xl2016 (видео).
Изменено: TheBestOfTheBest - 14.09.2016 23:06:13
Неизлечимых болезней нет, есть неизлечимые люди.
 
TheBestOfTheBest спасибо, что не забываете... действительно, новые возможности Excel, реализованные в PP и PQ, - дают хорошие возможности очень комфортно себя чувствовать при работе с данными, структурированными, как нормальная БД...
И ДАЖЕ с новым видом текстовых файлов, таких, как JSON-файлы, которые приходят на смену csv и txt (а может просто входят в моду - ввиду какого-то особого их удобства для вэб-дизайнеров для отображения инфо из них на сайтах)...
anvg уже и с JSON через PQ познакомился  8)   (спасибо ему за пример) - Transforming json with power query... действительно, может быть удобно - взять PQ и потом их всех одним махом запихать в PP... спасибо за линк #116...
и оставлю ещё на один блог - Specifying JSON Query in Power Query  – Erik Svensen -  
... в Access я пока не видела возможности работать с JSON'ами... хотя , может, в 2016 что-нибудь когда-нибудь найдётся (но у меня пока 2010)
Изменено: JeyCi - 17.09.2016 12:51:12
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
ZVI написал: Как в  MS Query реализовать Full Outer Join показано здесь:
Description of the usage of joins in Microsoft Query
ZVI, спасибо за линк на эти нюансы и возможности
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
сижу вот, оптимизирую по-тихонечку  :oops:
вижу функцию Get_Exec #2 - от Doober - ветка: Создание sql запроса, получить данные из MSSQL в Excel
вижу функцию GetRs  #1 - от B.Key - ветка: Как с .VBS создать .exe
вижу сайт https://www.connectionstrings.com/
вижу ветку Строка подключения в публичной константе
... и написала свою функцию в 2х вариациях (для подключения книги саму на себя)...
Для Подключения (книги на саму себя) используется публичная константа
Public Const ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$1;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
передаётся в функцию только SQL-запрос - результат - массив из рекордсета: a = Get_SQL(sSql)
Do...Loop

и для разнообразия, чтобы отобразить нюансы размерностей при передаче из RS в массив - 2й вариант:
Redim a
обе отработали при тестировании на 2-х разных запрсах..
предыстория вопроса:
проблема возникла при получении через подключение к файлу .xlsx из РекордСета arr(0, 0 to 27775) через .GetRows - и невозможности одним движением сделать arr=Application.Transpose(rs.GetRows) в функции почему-то вдруг... Option Base 1 не помогло... поэтому решила разобрать по косточкам  8) - надёжно и универсально (для 2007, 2010 точно)...
p.s.
если где будет замечено исчезновение последней строки, чуть что, исправлю потом... пока реальные данные не предоставляют возможность заметить это, а тестить уже пока не хочется...
- удобно для использования на все запросы по всему проекту
p.p.s
хотя, конечно, несколько запросов по модулю можно сделать и по примеру, любезно предоставленному Smiley, - пост #27 - ветка: Фильтр в Excel по выбранным параметрам, фильтрс помощью формул...  
****
всем, кого заметила в связи с данным нюансом - спасибо!.. функции Get_SQL под спойлерами - на всякий случай
;) в Америке сегодня День Колумба - с чем всех и поздравляю  :)    
Изменено: JeyCi - 22.10.2016 20:06:40
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
- пусть будет под рукой и возможность перенастроить подключение по текущему пути файла - Выполнить можно из Immediate - совет от Андрей VG
Для "Умной таблицы"
ActiveSheet.ListObjects(1).QueryTable.Connection = Replace$("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$1;Extended Properties='Excel 12.0;HDR=Yes';", "$1", ThisWorkbook.FullName)
Для сводной, основанной на подключении
ActiveSheet.PivotTables(1).PivotCache.Connection = Replace$("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$1;Extended Properties='Excel 12.0;HDR=Yes';", "$1", ThisWorkbook.FullName)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Страницы: Пред. 1 2 3 4 5 6 7 8 9 След.
Наверх