и замечена одна неприятность в Excel: если сохранить файл под др именем и обновить Подключения (делаю, как по линку из #72) - то ListObject, который длинный, не обновляется почему-то, причём с остальными в книге всё ок... а этот большой, возможно не успевает... пробовала различные предложения отсюда... но помогает только если сохранить файл - закрыть его - открыть заново - Обновить всё... возможно, я что-то не принимаю во внимание... НО если кто-то сталкивался с этим неудобством, - возможно, есть выход не закрывать-открывать лишний раз? ===== сохранила файл под новым именем, обновила макросом строку подключения на новый путь с новым именем файла, даже обновила всё руками - длинный зарос(возможно, кстати, что и сам запрос не совсем оптимальный) потерял все данные и не обновил ничего... никакие манипуляции не помогли, кроме как сохранить,закрыть файл, открыть его заново и снова обновить руками - только так всё работает... пришлось вспомнить
Цитата
Андрей VG написал: такую проблему файловых БД, как зависимость всех ваших строк подключения для файловых БД от местоположения файла.
... а, возможно, это и просто особенности то ли "движка", то ли драйвера Access-ного при использовании в Excel... способ жить с этим описала на всякий случай, как побороть не знаю - хотя так жить не совсем удобно... пока что так (похоже, проблема, отмеченная Андреем, не из приятных)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
TheBestOfTheBest , он рабочий - долго пыталась его уменьшить, чтобы осталась лишь репрезентация проблемы - так у меня это и не получилось... но причина такого явления вскрыта
Цитата
JeyCi написал: если сохранить файл под др именем и обновить Подключения (делаю, как по линку из #72) - то ListObject, который длинный, не обновляется почему-то, причём с остальными в книге всё ок...
- чтобы избавиться от такого - надо написать ДРУГОЙ запрос... исходила из двух предпосылок: 1) "логически интерполируя" пример из #79
Цитата
Андрей VG написал: только помнить о декартовом произведении
о том что не стоит писать конструкции типа where xxx in (select xxx from yyy where ...) а нужно их джоинить знают уже наверное все
переписала
Скрытый текст
'acc WHERE!!! tt1.Exp=
SELECT t.underlying, t.Name, t.Style, t.Exp, t.WK, t.Type, t.STRIKE, t.[oi atSettle], tt1.SETTLE AS FutSett FROM [basenow$] AS t INNER JOIN [basenow$] AS tt1 ON t.underlying = tt1.underlying WHERE ((t.underlying Is Not Null) AND (t.Style='American' Or t.Style='European') AND (t.Type='CALL' Or t.Type='PUT') AND (t.[oi atSettle] Is Not Null) AND (tt1.Style='FUTURES') AND (tt1.Exp= ( SELECT DISTINCT ee.Exp FROM (SELECT TOP 1 ee.Exp FROM [basenow$] AS ee WHERE ((ee.Style='FUTURES') AND (ee.underlying Is Not Null) AND ((IIF(ee.DTE IS NULL, 0, CLNG(ee.DTE)))>0)) ORDER BY ee.DTE) ) ));
на
Скрытый текст
' !!! JOIN FutSett вместо WHERE...
SELECT t.underlying, t.Name, t.Style, t.Exp, t.WK, t.Type, t.STRIKE, t.[OI atSettle], FutSett.SETTLE FROM [basenow$] t LEFT JOIN ( SELECT TOP 1 ee.[Exp] AS yy, ee.SETTLE, ee.underlying FROM [basenow$] AS ee WHERE (ee.Style='Futures' AND (IIF(ee.DTE IS NULL, 0, CLNG(ee.DTE)))>0) ORDER BY ee.DTE ) FutSett ON t.underlying = FutSett.underlying WHERE (((t.underlying) Is Not Null) AND ((t.Style)='American' Or (t.Style)='European') AND ((t.Type)='CALL' Or (t.Type)='PUT') AND ((t.[OI atSettle]) Is Not Null)) ORDER BY t.underlying, t.Style, t.Exp, t.WK, t.Type, t.STRIKE DESC;
по сути, эти два запроса дают идентичные выборки (больше уменьшить не смогла, чтобы лучше была видна структура, просто условий много) - но суть оптимизации в комментах... проблема решена: сохранение под новым именем, обновление строк Подключения и далее обновление самих запросов в этом же файле - происходит без проблем (без его закрытия) p.s. пошла переносить логику в рабочий файл...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
мои 3 копейки: в powerpivot 2010 действительно надо заходить в сам PP (даже не жать обновление, а зайти и выйти) а потом в пивот построенный по его данным чтобы появились обновлённые данные. В 2013 это исправили: обновление из пивота на листе вызавает обновление кэша PP без открытия надстройки.
про макро-рекордер в access: есть зато готовые кнопки (в 2003 точно). Рисуем на форме (больше негде ) кнопку и назначаем ей действие из списка, потом читаем и правим код. Метод №2: аналогично вызываются все стандартные (наверное и пользовательские?) менюшки из кода. Вот вам и имитация макро-рекордера. При отсутствии интернета часто спасало
Программист - человек, который решает задачу, о которой никто не знает, таким способом, который никто не понимает.
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
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)
- всё нумеруется автоматически (спасибо всем, поучаствовавшим в решении данного вопроса )
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
примеры Запросов (Access) на: 1) Создание Таблицы 2) Добавление в Таблицу (# 13 - в теме: Производительность итоговых функций SQL через ADODB.Recordset, или лучше циклами - от Михаил Лебедев) ---------------- 1) программно я делаю так
Скрытый текст
(не помню откуда)
Код
Sub CreateDB()
Dim pCon As New ADODB.Connection
Dim sSQL As String
sFilename = "c:\" & "Export_" & Year(Date) & _
Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & ".accdb"
Main (sFilename)
pCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Mode=16;Data Source=" & sFilename & ""
sSQL = "CREATE TABLE `MyTable` (`Имя1` VarChar (128) NOT NULL,`Имя2` Long NOT NULL )"
pCon.Execute sSQL
pCon.Close
End Sub
Public Function Main(sFilename)
'Create an Access Database
'In contrast, when exporting to an Access database the file and table must exist already.
'This article will show you how to create an empty Access database ready to receive your data
Dim sConnectionString
Dim oCatalog
Dim oConn
Dim oFSO ' Check for existing instance of file and delete
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(sFilename) Then
oFSO.DeleteFile sFilename
End If
Set oFSO = Nothing ' Create empty MDB
sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFilename
Set oCatalog = CreateObject("ADOX.Catalog")
oCatalog.Create sConnectionString
Set oCatalog = Nothing Main = True
End Function
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
вот только об одном иногда начинаю мечтать... (может, кто-нибудь знает?) - можно ли как-то в самом SQL-запросе в Excel делать комментарии? (как в коде vba)... чтобы удобнее вспоминать было, когда он длинный получается... ??
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
JeyCi написал: можно ли как-то в самом SQL-запросе в Excel делать комментарии?
Конэчно можно! Только синтаксис может различаться для разных серверов. В MySLQ это:
Код
SELECT 1+1; # Этот комментарий продолжается до конца строки
SELECT 1+1; -- Этот комментарий продолжается до конца строки
SELECT 1 /* Это комментарий в строке */ + 1;
SELECT 1+
/*
Это многострочный
комментарий
*/
1;
к сожалению в Access (полагаю, и в Excel - т.к. одинаковый движок) такие варианты не проходят... но буду иметь ввиду для др... спасибо
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
грядущее будущее: встраивают в xl2016 и Power Query - пока надстройкой есть для xl2013, xl2010_SP1... напомнила мне сегодня PQ отдельные способности Access'a (хотя язык другой - M) - впечатления очертила здесь в #16 (в ветке: Выборка повторяющихся наборов данных)...
и ещё впечатления
и впечатления Максима (надеюсь он не против - очень познавательно - спасибо): Я: ковыряю sql (и краем мозга думаю, дают ли PowerPivot и PowerQuery какие-либо преимущества, чтобы координально менять ориентацию на другие языки - пока не знаю)... Максим: Конечно, это не заменяет SQL, но в целом, с точки зрения пользователя, сильно облегчает жизнь. Вещь очень интересная... Основное отличие от SQL - для PQ практически все равно, в каком виде исходные данные. SQL же работает только со структурированными, с таблицами, с БД. А PQ - почти с чем угодно, то есть я разгребал, например, неструктурированные вебсайты, на которых нет табличных тегов, типа Яндекс.Маркет PowerPivot и DAX - еще более мощные штуки, но у них другие задачи, это уже аналитика по модели данных, а PQ - это получить-трансформировать-выгрузить, но с очень большими возможностям по трансформации данных, встроенными функциями и возможностями создания UDF для сложных случаев.
и ещё впечатления
и впечатления Андрея: Power Query не зря Power - создание спискового значения поля записи в процессе создания запроса - очень удобный инструмент. По сути получившийся в Power Query запрос - полный аналог словаря словарей: т.к. в Power Query другой подход - отбор по равенству списков - группировка по равенству, в отличии от SQL по неравенству. P.S. PowerQuery – The power of M (линки внизу страницы) P.P.S. циклов нет - только рекурсия (здесь же линк на хабр и там на англ. версию PQ-installer)
user-friendly момент PQ: а) "с помощью PQ можно читать файлы из одной папки, прописывая доп. условия в Fields и Conditions - при добавлении нового файла в папку, запрос автоматом будет его обрабатывать по общему признаку (что очень удобно, если источники появляются по ходу - тогда складировать их в одно общее место и обрабатывать с помощью PQ)" б) + работа с JSON-файлами и др. возможа без рукописного парсинга, по слухам... подборка линков, которые мне понравились
user-friendly момент PP: "если данных много, то запрос можно/нужно включать в Модель данных, а не выводить на лист, и получать ответ в виде Сводной на Лист"
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
раздел Access Query: Tips and Techniques (видео на англ.) - показано, как делать в Access... потом можно просто брать запрос для использования его в xl p.s. элегантный Get Details and Totals in one Query... наверно, и остальные
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Да там же не мне, а The Prist говорить спасибо нужно.)) В свою очередь хотел бы и Вам выразить благодарность за чудную тему. Много можно черпнуть полезного. Давиче нашел обучалку (не помню точно где) . Залил сюда.
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
CrazyRabbit написал: Я хочу создать Excel.xlsb, который объединит новые файлы, откроет Access. Тот при открытии вытянет из Excel.xlsb все данные. Далее Access закрывается.
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
не нашла по ветке линк (может людям пригодится) - моё знакомство с SQL-запросами начиналось примерно в те времена (посты в районе тех дат): Получение данных из SQL по условию в ячейках... жаль картинки уже не сохранились, но B.Key описал словами получение Параметрического запроса... непараметрический - ещё проще (первых 2 пункта)... - к вопросу: откуда начиналось моё знакомство с SQL?... теория - Использование Microsoft Query ... --> ОСНОВЫ смотрела здесь: SQL за 10 минут... практика бОльшей частью от TheBestOfTheBest и Андрей VG (особенно в части программирования выполнения запросов, хотя если их настроить - то и код в принципе не нужен - но иногда, полезно и кодом)... ориентировалась на Поиск- кто захочет знакомства с SQL с нуля - много ответов уже не раз озвучены на форуме (и ветки посещённые мной в те времена ) - кажется побывала везде (спасибо всем, кто делился опытом) P.S. файлы с запросами см. по конкретному пути файла - обычно использую файл в папке C:\1 (т.к. в запросе прописывается путь файла), сам запрос в файле см. Данные - Подключения - Свойства - Определения --> см. SQL-запрос... строку подключения можно изменить там же... удобнее всего запросы выполнять вкладка Данные - Получение внешних данных - из Microsoft Query... обновляется по ПКМ на таблице - Обновить (на этот пост буду отправлять всех с вопросами что и где)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
~ 6 Правил Нормализации (для возможности оперирования данными по принципу Баз Данных) p.s. и тема в приёмах Редизайнер таблиц и на форуме (redesigner Формулой - по мотивам из комментов в приёмах )- для приведения таблиц в плоский вид
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
TheBestOfTheBest спасибо, что не забываете... действительно, новые возможности Excel, реализованные в PP и PQ, - дают хорошие возможности очень комфортно себя чувствовать при работе с данными, структурированными, как нормальная БД... И ДАЖЕ с новым видом текстовых файлов, таких, как JSON-файлы, которые приходят на смену csv и txt (а может просто входят в моду - ввиду какого-то особого их удобства для вэб-дизайнеров для отображения инфо из них на сайтах)... anvg уже и с JSON через PQ познакомился (спасибо ему за пример) - Transforming json with power query... действительно, может быть удобно - взять PQ и потом их всех одним махом запихать в PP... спасибо за линк #116... и оставлю ещё на один блог - Specifying JSON Query in Power Query – Erik Svensen - ... в Access я пока не видела возможности работать с JSON'ами... хотя , может, в 2016 что-нибудь когда-нибудь найдётся (но у меня пока 2010)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
сижу вот, оптимизирую по-тихонечку вижу функцию 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
Код
Public Function Get_SQL(ByVal sSql As String) As Variant
Dim rs As Object, cn As Object, Arr As Variant
sCon = Replace(ConnectionString, "$1", ThisWorkbook.FullName)
On Error GoTo CnErrorHandler
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open sCon
rs.Open sSql, sCon, adOpenStatic
ReDim Arr(1 To rs.RecordCount, 1 To rs.Fields.Count)
i = 0: j = 1
Do While Not rs.EOF
i = i + 1: j = 1
For Each fld In rs.Fields
Arr(i, j) = fld.Value
j = j + 1
Next
rs.MoveNext
Loop
rs.Close
cn.Close
Set cn = Nothing: Set rs = Nothing
Get_SQL = Arr
Exit Function
CnErrorHandler:
For Each ADOErr In cn.Errors 'Отладчик ошибок подключения
MsgBox "№ ошибки " & ADOErr.Number & Chr(10) & _
"Описание: " & ADOErr.Description & Chr(10) & _
"Источник: " & ADOErr.Source, vbCritical
Debug.Print "№ ошибки " & ADOErr.Number & Chr(10) & _
"Описание: " & ADOErr.Description & Chr(10) & _
"Источник: " & ADOErr.Source
Set cn = Nothing
Next
End Function
и для разнообразия, чтобы отобразить нюансы размерностей при передаче из RS в массив - 2й вариант:
Redim a
Код
Public Function Get_SQL(ByVal sSql As String) As Variant
Dim rs As Object, cn As Object, Arr As Variant
sCon = Replace(ConnectionString, "$1", ThisWorkbook.FullName)
On Error GoTo CnErrorHandler
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open sCon
rs.Open sSql, sCon, adOpenStatic
Arr = rs.GetRows
rs.Close
cn.Close
Set cn = Nothing: Set rs = Nothing
'=========================================
'col = UBound(a, 1): rr = UBound(a, 2)
ReDim a(1 To UBound(Arr, 2) + 1, 1 To UBound(Arr, 1) + 1)
For i = 0 To UBound(Arr, 2)
For j = 0 To UBound(Arr, 1)
a(i + 1, j + 1) = Arr(j, i)
Next
Next
'=========================================
Get_SQL = a
Exit Function
CnErrorHandler:
For Each ADOErr In cn.Errors 'Отладчик ошибок подключения
MsgBox "№ ошибки " & ADOErr.Number & Chr(10) & _
"Описание: " & ADOErr.Description & Chr(10) & _
"Источник: " & ADOErr.Source, vbCritical
Debug.Print "№ ошибки " & ADOErr.Number & Chr(10) & _
"Описание: " & ADOErr.Description & Chr(10) & _
"Источник: " & ADOErr.Source
Set cn = Nothing
Next
End Function
обе отработали при тестировании на 2-х разных запрсах.. предыстория вопроса: проблема возникла при получении через подключение к файлу .xlsx из РекордСета arr(0, 0 to 27775) через .GetRows - и невозможности одним движением сделать arr=Application.Transpose(rs.GetRows) в функции почему-то вдруг... Option Base 1 не помогло... поэтому решила разобрать по косточкам - надёжно и универсально (для 2007, 2010 точно)... p.s. если где будет замечено исчезновение последней строки, чуть что, исправлю потом... пока реальные данные не предоставляют возможность заметить это, а тестить уже пока не хочется... - удобно для использования на все запросы по всему проекту p.p.s хотя, конечно, несколько запросов по модулю можно сделать и по примеру, любезно предоставленному Smiley, - пост #27 - ветка: Фильтр в Excel по выбранным параметрам, фильтрс помощью формул... **** всем, кого заметила в связи с данным нюансом - спасибо!.. функции Get_SQL под спойлерами - на всякий случай в Америке сегодня День Колумба - с чем всех и поздравляю
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
- пусть будет под рукой и возможность перенастроить подключение по текущему пути файла - Выполнить можно из 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)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)