Сегодня столкнулся с такой проблемой.
У человека есть сводная таблица, построенная на запросе объединении 2х таблиц в одну, около 1,6млн строк. Запросы оформлены в Power Query
При просмотре детализации цифры сводной таблицы, показывает по умолчанию только 1000 записей.
В аналогичном файле, только построенным на OLAP кубе, в настройках подключения, можно изменить параметр показа максимального количества записей по умолчанию. В данном случае, этот параметр не доступен для редактирования, он пустой и серый.

I. Было найдено решение в интернете. Подходит для Excel 2016:
1) поместите мышь вне сводной таблицы
2) пройдите по меню Данные -> Подключения
3) в диалоговом окне Подключения к книге выберите строку ThisWorkbookDataModel
4) нажмите кнопку Свойства
5) на вкладке Использование в поле Максимальное число извлекаемых записей введите количество строк, которые вы хотите получить
6) нажмите Ok

II. Но, у человека оказался Excel 2019.
Там нет меню Данные -> Подключения.
2) меню Данные -> Запросы и подключения.
3) Справа открывается окно, на вкладке Подключения выбираешь ThisWorkbookDataModel.
остальные шаги аналогичные.

НО! Excel 2019 не видит это подключение, там пусто.
III.Методом тыка, было найдено решение:
1) Открываем файл. Ни в коем случае не открываем Подключения, даже если просто посмотреть.
2) Пишем пустой макрос. (тем кому лень писать руками sub и т.д.)
3) Выбираем макрос и редактируем его.
4) Вставляем следующие 2 строки
   MsgBox (ThisWorkbook.Connections.Count)
   MsgBox (ThisWorkbook.Connections.Item(1).Name)
5) Выполняем макрос. Всплывающие окна закрываем.
6) Теперь повторяем шаги описанные параграфом выше, и во вкладке Подключения выбираешь ThisWorkbookDataModel.


Спасибо за внимание.

Есть Excel файл.
Я создал данные из других источников (С сервера SQL Server). Создалась таблица с привязкой этого подключения.
Далее я удаляю это подключение "Данные" - "Подключения" - "Удалить".
Затем я хочу создать удаленное подключение снова и обновить таблицу.
Создание подключения вроде должно создаваться через (...Connections.Add2)
Не получается, выдает 91 ошибку.
сам код:
Сейчас у меня подключения не разрываются, а в текущих подключениях книги я меняю строки подключения, все обновляю, потом удаляю или разрываю подключения.
Но хочется чтобы файл изначально был без подключений, после запуска макроса он создает подключения, обновляет таблицы, удаляет подключения.

Вот как сделать новое подключение с 0, не могу разобраться. Прошу вашей помощи.
Вот  некоторые ссылки которые я находил с материалом, не помогло.
1 , 2 , 3

Есть Excel 2010. Есть условное форматирование из набора значков. Есть один набор, в котором есть зеленый круг с галочкой, желтый восклицательный круг, красный круг с крестиком.

Мне надо чтобы было ТРИ смайлика:
Красный грустный.
Желтый нейтральный.
Зеленый улыбающийся.

В шрифте Wingdings они есть.
Как добавить в набор свои смайлики и чтобы они работали на любом компе?

При импорте данных из SQL Server 2008 R2 в Excel 2010, порядок столбцов не соответствует порядку в SQL запросе.
1) Например, есть запрос :
SELECT [Pole3],[Pole6] FROM [BUG]

В Excel 2010 в таблице столбцы выглядит так:
[Pole3] [Pole6]

2) Добавил в запрос еще одно поле для вывода, и поставил его в первую очередь.
SELECT [Pole1],[Pole3],[Pole6] FROM [BUG]

А В Excel 2010 в таблице столбцы выглядит так:
[Pole3] [Pole6] [Pole1]

Столбы пробовал перетащить, так он не хочет вставить между столбцами, перетаскиваемый столбец, а пытается заменить существующий на месте перетаскивания.

Не могу победить его.

Пробовал подписаться на канал. Выдает ошибку.
Пробовал и в хроме и Е.

Создал спарклайн. Когда скрываю диапазон на который он ссылается спарклайн исчезает. Именование диапазона не помогает.

День добрый коллеги :)  
 
Создаю Надпись, через макрос.  
В свойствах есть опция отображения текста за пределами рамки.  
-= Excel 2010  
-= Надпись    
-= Свойства Фигуры...  
-= Надпись  
-= разрешить переполнение фигуры текстом  
Ставим галочку, и текст выходит за границы надписи, если он не помещается в надпись.  
 
НО! Как сделать это программно???  
================================================КОД  
Set myDocument = Worksheets(1)  
     
   'создание текстбокса  
   'присвоение имени  
   'вставка текста  
   'смена цвета шрифта  
   'убирает заливку  
   'убираем границы  
   'отключаем перенос текста с новой строки в областьях границы  
   'отступ внутреннее поле слева до текста  
   'отступ внутреннее поле справа до текста  
   'отступ внутреннее поле сверху до текста  
   'отступ внутреннее поле снизу до текста  
With myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50)  
   .Name = "stroka1"  
   .TextFrame.Characters.Text = "Test Box " & Date & " " & Time()  
   .TextFrame2.TextRange.Font.Fill.ForeColor.ObjectThemeColor = msoThemeColorText2  
   .Fill.Visible = msoFalse  
   .Line.Visible = msoFalse  
   .TextFrame2.WordWrap = msoFalse  
   .TextFrame2.MarginLeft = 0  
   .TextFrame2.MarginRight = 0  
   .TextFrame2.MarginTop = 0  
   .TextFrame2.MarginBottom = 0  
   .TextFrame2.AutoSize = msoAutoSizeNone  
End With  
 
End Sub  
===============================================  
24220

КОД найден на просторах инета.    
впоследствие найдены следы применения здест на сайте - Author: KL  
 
Данный код использует листы в текущей книге, как таблицы с данными, которые можно обрабатывать при помощи SQL запроса.  
 
Работает быстро, и не слишком сложный.  
 
Если много запросов, то я переделывал в параметр сам SQL запрос.  
Надо будет поменять название    
Private Sub GenerateReportSQL(strSQL)  
 
закомментировать 2 строки    
Dim strSQL As String  
strSQL = "SELECT * FROM [OLD$]"
 
-----------------------------------------------------------------------------  
Private Sub GenerateReportSQL()  
Dim ws As Worksheet  
Dim qry As QueryTable  
Dim strPath As String  
Dim strName As String  
Dim strCon As String  
Dim strSQL As String  
Dim strPosition As String  
Dim strRng As String  
Dim tm As Double  
 
   With ThisWorkbook  
       On Error Resume Next  
       Set ws = .Worksheets("Отчет")  
       On Error GoTo 0  
       If ws Is Nothing Then Set ws = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))          
       strName = .FullName  
       strPath = .Path  
       strRng = "A2:U"  
       strCon = "ODBC;DSN=Excel Files;" & _  
                "DBQ=" & strName & ";" & _  
                "DefaultDir=" & strPath & ";" & _  
                "DriverId=1046;" & _  
                "MaxBufferSize=2048;" & _  
                "Page Timeout=5;"  
strSQL = "SELECT * FROM [OLD$]"
       With ws  
           If Val(Application.Version) > 11 Then DeleteConnections_12  
           .Cells.Clear  
           .Name = "Отчет"  
           Set qry = .QueryTables.Add(strCon, .Range("A1"), strSQL)  
           With qry  
               .BackgroundQuery = False  
               .Refresh  
               .Delete  
           End With  
       End With  
   End With  
End Sub  
 
Private Sub DeleteConnections_12()  
' This line won't work and wouldn't be necessary  
' in the versions older than 2007  
'*****************************************************************************  
On Error Resume Next: ThisWorkbook.Connections(1).Delete: On Error GoTo 0  
'*****************************************************************************  
End Sub  
---------------------------------------------------------------------------------  
 
Теперь попробую описать нюансы.  
 
Листы служат табличными источниками данным. Писать в запросе надо обязательно с $    
Т.е. для объявления Листа с данными под названием "OLD" в SQL запросе надо писать [OLD$]
 
Первая строка в листе служит названием полей. Для объявления поля необходиомо писать имя столбца.  
Например, поле называется "Название" на листе "OLD". в SQL пишем так [OLD$].Название
 
Еще ньюанс. Если вы хотите отбирать часть текста, то вместо * надо писать %  
Например, необходимо чтобы в Название содержалось слово "оскв", в SQL надо писать like `%оскв%`  
 
Да и еще, в SQL запросе под VBA, при выборе всякий условий, не пишут знак кавычки, пишите (апостраф вроде) `  
Например WHERE [OLD$].Название = `Москва`

В Excel 2003 была форма, которая использут календарь (mscal.ocx). В 2007 и 2010 версии, эту библиотеку перестали вкладывать. Сейчас народ переводят с 2003 на 2010 версию офиса, соответственно перестала у них работать форма. У меня на 2010 версии решилось просто, я скопировал файл mscal.ocx в папку C:\Program Files\Microsoft Office\Office14. Запустил и все пошло. У народа не катит. Хочу на Винде 7 , зарегистрировать эту библиотеку в реестре. На ХР ранее было так (regsrv32 "C:\Program Files\Microsoft Office\OFFICE14\MSCAL.OCX"), на семерке так не катит. ....  
 
пока писал нашел ошибку :)    
правильно писать надо regsvr32  
буквы r и v перепутаны были :(

Добрый вечер коллеги.  
У меня есть таблица которая тянется из веб-запроса.  
через VBA я меняю строку подключения и обновляю данные.  
на сайт вход по имени и паролю, но настройки такие что входи автоматом и ничего вводить не надо.  
попалась проблема пользователя, для входа надо ввести имя и пароль.  
проблема в том что имя это домен + имя (moscow\name)  
я пробовал так  
 
URL;https://moscow\name:пароль@сайт.....  
 
из-за \ (слеша не работает походу :(    
 
как по другому можно переслать имя и паролдь чтобы получить доступ к сайту???

Меня интересует как вытащить какая строка подключения у запоса?  
присвоение я знаю, а вот как узнать что иммено там забито :(  
 
Мне надо в яейку вытащить строки подключения из разных веб-запросов. (для контроля)

Извините что офтоплю, Админы если что, то удалите темку.  
 
Нужен адвокат по гражданским делам, необходима консультация по разводу и вести процесс раздела имущества. Нужен ХОРОШИЙ адвокат, можно сказать необходим ОТЛИЧНЫЙ адвокат который отсудит кв-ру в мою пользу!!! может у кого есть именно знакомый адвокат или знаете у кого есть :)

алгоритм расчета:  
1.значения отсортированы по убыванию  
2.найдена сумма не отрицательных значений  
3. найдено 80% от суммы  
3.сумируем от большего к меньшиму значению до тех пор пока сумма не станет приблиз равно 80% от суммы не отриц значений.  
вот это последнее значение по убыванию и надо получить формулой при НЕОТСОРТИРОВАННОЙ таблицы.

собственно сабж :)

Возможен ли сабж?  
 
Т.е. у меня есть скрытый столбец, я хочу курсором туда встать, можно ли это реализовать клавишами "стрелками" ?  
 
прописать АдресЯчейки явно не предлагать :)

Блин, да что за не везуха то. Вчера вечером шел домой с работы (как раз "неожиданно для Москвы" :) выпал снег. И надож было так упасть. ....."Здесь идет непереводимая игра слов"....  Походу рястянул лодыжку и что-то с коленкой. Больно блин двигаться. К врачу сегодня пойду, если с работы отпустят (я уже на работе), пришлось ехать на машине , но на тормоз жать больно.

Дорогие Одмины :)  
Во первых, с пятницой всех :) ;)    
Во вторых, есть предложение создать отдельно ветку "Вопросы по OpenOffice"  
К чему это я, сейчас все сидят на Офисе мелкософта, у нас в организации ХХХХХХ собираются отказаться от Офиса и перевести всех на ОО (экономия должна быть экономной), но большенство доработок использует VBA, а в ОО свой синтаксис и невозможно просто перекинуть код и чтобы он сразу работал. короче, выношу на повестку дня -  
Создать новый раздел для ОО или этот сайт будет только для Excel`я ? (я так думаю что Хозяина сайта спросить нужно, т.е. Павла )  :)  
 
Итак, предоставляется слово следующему оратору ....

Одмины :)    
Тема : Запуск файлов Excel без отключения макросов"  
Можете перенести эту тему в (копилка идей)?  
 
Мне как-то понадобилось сделать форму для пользователей, которую они заполняли и отправляли данные письмом мне. Но столкнулся с тем что у некоторых пользователях стоит ОЧЕНЬ ВЫСОКАЯ степень степень безопасности макросов (т.е. они отключены автоматом), у кого-то средний уровень (т.е. сами отключают). Ниже написан макрос который работает в ХР точно, вроде должен работать в Server 2003. Что он делает, открывает процесс Excel с НИЗКИМ уровнем безопасности макросов и загружает Вашу книгу лежащую в ЭТОЙ ЖЕ ДИРЕКТОРИИ где и сам макрос.  
 
 
Пример скрипта, который запускает файл с таким же именем (т.е. скрипт test.vbs запускает файл test.xls)---------------------  
Secur  
Sub Secur()    
Dim objXL  
Dim Secur  
Set objXL = CreateObject("Excel.Application")    
objXL.Visible = TRUE  
secur = objXL.AutomationSecurity  
objXL.AutomationSecurity = 1  
               objXL.Workbooks.Open ( mid(Wscript.ScriptFullName,1,len(Wscript.ScriptFullName)-4) & ".xls")  
objXL.AutomationSecurity = secur  
End Sub  
------------------------------------------

есть столбцы данных с месяцем и номером недели, хочу найти сколько недель в месяце на основании этих данных.  
 
Сделал через разницу ДМАКС и ДМИН по условию. Может кто может оптимизировать :)

Автофильтр через макросы работает, если фильтруем по числам или по тексту, а вот если пробовать фильтровать даты через VBA - то список пустой, хотя условие стоит, и если зайтив условие и нажать ОК, то автофильтр сработает нормально .  
 
Руками работает - макросом нет. :( в чем прикол ? и это именно фильтр по датам.

Открываю страницу через код:  
 
Dim oInternetExplorer As New SHDocVw.InternetExplorer  
   oInternetExplorer.Visible = True  
   oInternetExplorer.Navigate (Link)  
 
код нормально открывает страницу, хочу сохранить её в формате MHT туда куда мне надо, не получается :(  
 
Попробовал через "MSXML2.XMLHTTP" - но я что-то не пойму как сохранить и какая кстати библиотека?  
 
Спасибо.

как формулами получить значения грцппы.    
Расширенный вопрос и частичное решение(вопрос) в примере.  
 
на листе  "из темы" - есть похожая формула (обсуждалось здесь на форуме), только я не могу её прикрутить к решению своей задачи :(

Excel 2003.  
 
код:  
{quote}  
Private Sub Workbook_BeforeClose(Cancel As Boolean)  
For i = 2 To 60  
   If Sheets21.Cells(i + 1, 3).Value <> Sheets5.Cells(i, 12).Value Then MsgBox "Ошибка, есть несоответсвие по проводкам ВВОД П/П"  
   If Sheets22.Cells(i + 1, 3).Value <> Sheets5.Cells(i, 21).Value Then MsgBox "Ошибка, есть несоответсвие по проводкам SME"  
   If Sheets8.Cells(i + 1, 3).Value <> Sheets5.Cells(i, 22).Value Then MsgBox "Ошибка, есть несоответсвие по проводкам КРЕД-ОФОРМ"  
   If Sheets9.Cells(i + 1, 3).Value <> Sheets5.Cells(i, 23).Value Then MsgBox "Ошибка, есть несоответсвие по проводкам КРЕД-СОПРОВ"  
Next  
End Sub {/quote}  
 
все проверяет и выдает ошибку если есть, и закрывает книгу.  
Мне надо чтобы при ошибке он выдал сообщение и НЕ ЗАКРЫВАЛ книгу, пока не исправим ошибку.  
 
хотя как вариант можно кинуть макрос при сохранение книги проверять.

"Microsoft Office Excel 2007 для чайников. Краткий справочник", 2007г. - 384стр, Уокенбах Джон, Банфилд, Колин.  
 
Перед вами — простое и дружеское пособие по последней версии популярнейшей программы электронных таблиц — Microsoft Office Excel 2007. Здесь вы сможете легко    
 
и быстро найти ответы на все вопросы, возникающие в процессе работы с программой, — ог описания нового интерфейса и создания рабочих листов и книг до работы    
 
со сводными таблицами и выполнения анализа "что-если". Книга написана понятным и доступным языком и незаменима для тех, кто собирается самостоятельно освоить    
 
Microsoft Office Excel 2007.  
 
Ссылка:  
http://rapidshare.com/files/177026867/Excel2007.part1.rar  
http://rapidshare.com/files/177029797/Excel2007.part2.rar  
 
Зеркало:  
http://depositfiles.com/files/oya89g785  
 
Зеркало:  
http://www.filefactory.com/file/aff0e74/n/Excel_2007_rar  
 
--------------------------------  
"Подробное руководство по созданию формул в Excel 2002.", 2002г. - 624стр., Уокенбах, Джон.  
 
Большинство изданий, посвященных Excel, представляют собой универсальные руководства по программному продукту, содержащие информацию об использовании всех    
 
его средств и инструментов. Наряду с этим вы вряд ли найдете книгу, посвященную одному из основополагающих компонентов Excel, имеющему исключительно важное    
 
значение для любого пользователя, а именно — формулам. Ведь именно формулы делают электронную таблицу действительно электронной. Следовательно, чем больше вы    
 
знаете о формулах, тем лучше будут ваши таблицы. Большая часть материала, представленного в этой книге, посвящена именно тем вопросам, которые пользователи    
 
задают в ходе решения поставленной задачи. Вы также найдете ответы на те вопросы, которые не приходят в голову даже в конце обучения. Эта книга не    
 
предназначена для начинающих пользователей Excel. В ней рассматривается достаточно сложный материал, который утомителен для начинающих пользователей, но    
 
чрезвычайно порадует тех, кому часто и долго приходится управлять электронными таблицами.  
 
Ссылка:  
http://rapidshare.com/files/177014699/Uokenbah_Podrobnoe_rukovodstvo_po_sozdan­iyu_formul.rar  
 
Зеркало:  
http://depositfiles.com/files/n25d1kfqf  
 
Зеркало:  
http://www.filefactory.com/file/aff0e80/n/Uokenbah_Podrobnoe_rukovodstvo_po_sozdani­yu_formul_rar  
 
---------------------------------  
"Профессиональное программирование на VBA в Excel 2002.", 2003г. - 784стр., Уокенбах, Джон.  
 
По Excel есть немало серьезных книг, но эта все равно единственная, где разработка приложений рассматривается в широком плане. Дело в том, что VBA — всего    
 
лишь один из компонентов разработки приложений (правда, компонент этот достаточно большой). А такой программный продукт, как Excel, отличается крайней    
 
таинственностью. В нем множество интересных возможностей, притаившихся где-то в глубинах и неведомых простому пользователю. Кроме того, некоторые хорошо    
 
известные функции можно использовать по-новому. Миллионы людей по всему миру используют Excel. И только несколько процентов пользователей действительно    
 
понимают, на что способен этот продукт. В данной книге автор попытается ввести вас в эту элитную компанию. Вы готовы?  
 
Ссылка:  
http://rapidshare.com/files/177015314/VBA_2002.rar  
 
Зеркало:  
http://depositfiles.com/files/u78pui40m  
 
Зеркало:  
http://www.filefactory.com/file/aff0e8b/n/VBA_2002_rar  
 
---------------------------------  
"Диаграммы в Excel", 2003г. - 448стр., Уокенбах, Джон.  
 
В настоящее время эта книга — единственное всеобъемлющее руководство по созданию диаграмм графическими средствами Excel. В книге подробно рассмотрены типы    
 
диаграмм, способы их создания, форматирования и настройки, устранение неполадок в диаграммах, интерактивные диаграммы, добавление линий тренда, сглаживание и    
 
прогнозирование зависимостей, анимация и автоматизация диаграмм средствами Visual Basic и другие вопросы. Рассматриваемые темы иллюстрируются многочисленными    
 
примерами диаграмм. Книга будет полезна как для начинающих, так и для опытных пользователей Excel. Для ее понимания требуется знание элементарных основ    
 
работы в Excel.  
 
Ссылка:  
http://rapidshare.com/files/177012097/diagrammi_v_excel.rar  
 
Зеркало:  
http://depositfiles.com/files/n3s9f1leo  
 
Зеркало:  
http://www.filefactory.com/file/aff0e82/n/diagrammi_v_excel_rar  
 
---------------------------------  
"Экономическое моделирование в Microsoft Excel, 6-е издание", 2004г. — 1024стр., Мур, Джеффри, Уэдерфорд, Ларри Р., и др.  
 
Эта книга посвящена основным принципам моделирования, которые можно применить к широкому спектру различных управленческих задач, решаемых с помощью Microsoft    
 
Excel. В ней подробно рассматривается определенные классы моделей, используемые в самых разнообразных ситуациях. Цель книги — не составить сборник готовых    
 
"рецептов" моделей, а научить читателя самостоятельно готовить "здоровую пищу" на основе нескольких типов моделей, приведенных в качестве примеров. Она    
 
поможет современным менеджерам в их карьерном росте, покажет реальные жизненные ситуации, которые можно смоделировать с помощью электронных таблиц, и    
 
представит методы построения и анализа моделей.  
Книга предназначена для широкого круга студентов, аспирантов и работающих специалистов.  
 
Ссылка:  
http://rapidshare.com/files/177020097/mur_ekonomika.part1.rar  
http://rapidshare.com/files/177023061/mur_ekonomika.part2.rar  
 
Зеркало:  
http://depositfiles.com/files/s7bxn1asz  
 
Зеркало:  
http://www.filefactory.com/file/ah0gaac/n/mur_ekonomika_rar

Open "...\52.txt" For Input As #1  
Do While Not EOF(1)  
   Line Input #1, TextLine  
   ................  
   какие-то действия с строкой  
   ................  
Loop  
Close #1  
 
Он открывает файл и начинает с 1 строки по 1 строке прогонять в цикле.  
 
Можно ли открывать файл с последней строки, и затем получить предыдущую и т.д. до 1 строки, т.е. в обратном порядке?  
 
а то я вижу выход так это , загнать это все в массив а затем уже и массива начиная с последней строки вытягивать данные.

У меня при открытии этих тем:  
http://www.planetaexcel.ru/forum.php?thread_id=7790  
http://www.planetaexcel.ru/forum.php?thread_id=7800  
http://www.planetaexcel.ru/forum.php?thread_id=7749  
http://www.planetaexcel.ru/forum.php?thread_id=7786  
 
Показывает вместо темы , след текст :  
 
Access denied due to security policy violation  
Reject ID: 496de0cb-100a2-ec97a8c0-7b6    
 
или  
 
Access denied due to security policy violation  
Reject ID: 496de3a7-10057-ec97a8c0-7b6

Кто знает работающие веб-прокси ?  
а то на работе стоит запрет на скачку архивов, поэтому я не могу с этого сайта качать архивы :(  
да сайты иногда разные по программированию закрыты для доступа :(