Страницы: 1
RSS
Обобщение нескольких книг
 
Всем привет.
Тема конечно избитая, плюс есть PQ и иные средства, но не совсем это то что нужно.
Цель: объединить данные из нескольких книг в одну.
Условия: все книги под паролями (поэтому не PQ), тягать данные только с определённого листа (совпадает с именем текущего листа в общей книге), собирать данные начиная со второй строки (первая шапка), игнорировать столбец D (в большинстве случаев, где он имеет имя номер по порядку). Исходя из последнего условия будет требоваться заполнить этот столбец нумерацией, но пока можно оставить его пустым.
Нашёл подходящий макрос, подправил под свои нужды (раньше он каждый раз создавал новый лист), и теперь он что то не работает, не вставляет данные. Плюс ещё так и не понял как заставить его пропускать лист D.
 
Доброе время суток.
Вопрос-то в чём? Или вы просто пожаловаться на не лёгкую жизнь разработчика-автоматизатора VBA?
 
В идеале хочу его сделать надстройкой, так как книг в которые нужно сводить данные несколько, и в каждую из них добавлять этот код не хочу. А современем появятся новые и т.д.
 
Цитата
Kulibinslovoru написал:
все книги под паролями (поэтому не PQ)
тоже не повод отказываться: Собрать данные из защищенных паролем файлов - PowerQuery
Изменено: Дмитрий(The_Prist) Щербаков - 14.02.2020 13:12:49
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков, не знал

а как игнорировать данные из столбца (допустим D) и вносить в него последовательную нумерацию.
 
Цитата
Kulibinslovoru написал:
игнорировать данные из столбца (допустим D)
а в какой момент PQ разучилась удалять столбцы? Последовательная нумерация доступна через добавление столбца индекса(Добавить столбец - Столбец индекса). Если правильно понял про нумерацию.
Изменено: Дмитрий(The_Prist) Щербаков - 14.02.2020 13:21:17
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков,
Насколько я понимаю в конец таблицы? Рабочая таблицы ограничена диапазоном печати. В сером поле расположены служебные столбцы, хотя наверное их можно и не копировать.
Ну и последний довод отказа от PQ возможный скорый переход на АстраЛинукс, и коротенький макрос я думаю будет не сложно портировать под ОО или либро. Плюс в настоящее время возникнет вопрос, хотя и решаемый, с обновлением офиса на машине оператора, который будет этим заниматься. Опять же от сюда: одна кнопка без лишних шагов.

Но я все равно посмотрю как получится с PQ, может пока остановлюсь на этом варианте
 
Цитата
Kulibinslovoru написал:
коротенький макрос я думаю будет не сложно портировать под ОО или либро
ох, не надеялся бы я на это...
Цитата
Kulibinslovoru написал:
Рабочая таблицы ограничена диапазоном печати
PQ умеет отбирать и именованные диапазоны. И в этом случае это _xlmn.Print_Area. Можно задать собирать данные только из ячеек, заданных областью печати. Естественно, область печати должна быть реально задана(вкладка Разметка страницы -Область печати -Задать)
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Ну вот собственно мой макрос, в чём его проблема? Почему он не работает?
 
Отсюда вижу - код мой. И он работал изначально. И все что надо собирал. Но вот кто-то там только изменения разные вносил - это факт. Первое, что бросилось в глаза:
Код
Set wsDataSheet = ActiveWorkbook.Sheets(sSheetName).Select
Это уже бред по двум причинам:
1. Либо Set, либо Select. Т.е. правильно как минимум так:
Код
Set wsDataSheet = ActiveWorkbook.Sheets(sSheetName)
2. sSheetName = а такой лист точно есть в книге?

В общем попробуйте для начала убрать строку On Error Resume Next и посмотрите где будет ругаться макрос.

P.S. Чую я, что с такими познаниями в VBA сложно Вам будет это под ОО перестраивать :)
Изменено: Дмитрий(The_Prist) Щербаков - 14.02.2020 17:29:40
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков,
Спасибо большое, я вообще гуманитарий, но выбравший не ту стезю, познания в vba и python на уровне интуиции и математических способностей. Python'ом кодил в 2010-2011 годах, потом некогда было, а вот сейчас столкнулся с vba, учу потихоньку (где то с середины января). И из-за этого добиться истины поставленым путём - спортивный интерес, а без помощи не всегда это получается.
А про переход на ОО или либро, когда петух клюнет, вот тогда и буду соображать.

Кстати с ОО засада будет, на основании ведущихся сейчас таблиц формируются разные документы, это я прикрутил потихоньку. Плюс  реалиховано открытие этих документов без гиперссылок (что бы не нажать случайно, сделал по двойному клику по ячейке). Так же есть и другие плюшки. Вот это всё перенести на ОО  будет точно сложно 😁😁😁
 
Цитата
Дмитрий(The_Prist) Щербаков написал:
Отсюда вижу - код мой.
Да, точно, посмотрел Ваш код и код из своего примера практически одно и тоже.
А Ваш код я пробовал и он мне не подошёл и вот почему. Рабочие таблицы делал не я, и что там с ними начудили наверняка сказать не могу. Рабочие области, как уже говорил, ограничены диапазоном печати, остальное защищено от редактирования. Почему то заблокирован переход от ячейки к ячейке Tab'ом. Ну да ладно не в этом дело. Используя ваш код последней строкой определяется конец именованного диапазона (диапазон печати), а это 1001 строка. Код из примера последнюю заполненную строку определяет правильно. Копал, ковырял и понял, что вся таблица, хоть и с виду пустая, заполнена данными "null". PQ кстати тоже при формировании сводной таблицы посчитал, что таблицы заполнены до конца и свел аж 3003 строк (3 файла по 1001 строке). 🤔
Куда копать то?

а вообще Ваш пример по функционалу решал поставленную задачу на 80-90%. Лишний столбец откуда взяты данные и каждый раз новый лист.
Изменено: Kulibinslovoru - 15.02.2020 22:05:43
 
Цитата
Kulibinslovoru написал:
Куда копать то?
а фиг знает. Без файла-примера с этими данными и конкретным описание чего именно Вам надо взять из такой книги советовать вообще нечего.
Цитата
Kulibinslovoru написал:
Рабочие области, как уже говорил, ограничены диапазоном печати
если опираться на это - то есть возможность получить адрес области печати, если она была задана(выше я об этом упоминал). В том числе макросом:
Код
activesheet.PageSetup.PrintArea.address
если же область печати не была задана или заранее неизвестно, задана она правильно или нет, то придется определять диапазон на основании конкретных условий, которые четко смогут дать понять что надо считать диапазоном для отбора.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Это понятно.
Больше интересно почему пустые ячейки воспринимаются как заполненные, как будто в них внесено значение null.
Но в то же время метод определения последней заполненной строки из примера работает нормально.
 
Цитата
Kulibinslovoru написал:
почему пустые ячейки воспринимаются как заполненные
много факторов. Включая тот, что непонятно откуда Вы решили, что они не пустые. И как определили, что там именно null?
Возможно, все проще: Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?
Но опять же - не видя файл сидеть и гадать дело неблагодарное. Особенно учитывая Ваш опыт(без обид). Все может быть элементарно, а мы тут гипотезы будем всякие выдвигать...
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Файл на работе, а на работе я буду а только в понедельник.
Почему я знаю что там null? Power Query мне так "сказал". Когда я с его помощью сводил таблицы он скопировал строки которые были пустые, но заполнив их значением null. Вот такой трабл.
Формул там ни каких нет, единственное где то в половине ячеек стоит проверка данных, выбор из списка. И второе, имеющиеся данные в исходных таблицах были внесены через userform. Вот и  всё.
Короче в понедельник закину файл что бы было понятнее.
 
Цитата
Kulibinslovoru написал: Power Query мне так "сказал"
PQ все пустые ячейки обозначает как null...

Цитата
были внесены через userform
если из TextBox и прочих - то из них не пусто переносится, если вдруг они не заполнены, а строка нулевой длины. Ячейка выглядит пустой, но по факту таковой не является, т.к. содержит текст нулевой длины.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Вот собственно часть одного из тех файлов, которые необходимо сводить в обобщенные таблицы. Как уже писал выше имею проблему с тем что незаполненная строки некоторыми макросами и Power Query воспринимаются как заполненные и при слиянии нескольких таблиц в итоге получается таблица с кучей пустых строк (если делать через Power Query эти строки заполнены значением null).

Код
Cells(Rows.Count, 1).End(xlUp).Row

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

Код
Cells(1, 1).SpecialCells(xlLastCell).Row

А например данный метод считает заполненным весь диапазон определенный как "Область_печати", несмотря на то, что заполнено лишь часть строк и остальные строки никто не трогал. Такой же результат выдает Power Query.

Почему так получается?


Основной момент который принципиальный при объединении книг это столбец "D" - его желательно пропускать, в сводных отчетах будет общая нумерация (ну или возможно с присвоением индекса исполнителя, типа: №1-И). Если вести общую нумерацию думал сделать чрез переменную в цикле копирования строк, которая с каждым проходом будет увеличиваться на единицу. В PQ есть столбец индекса, это знаю. Ну, а если вариант с индексом исполнителя, то тут отдельно придется думать, но не сейчас, а когда приспичит.
Так что кроме основного вопроса поста (проблема с определением последней пустой строки), подскажите пожалуйста, как в макросе исключить из вставки один столбец.
Хотелось конечно максимально просто: небольшой макрос как в примере, который сможет работать на большинстве машин без танцев с бубном (это еще вопрос получится ли установить PQ на машину где будут вестись сводные отчеты, потому и в приоритете простой макрос или надстройка на его основе).
 
Вот пример.
Данные нужно будет добавлять в уже имеющийся лист, а не на новый.
 
Дмитрий(The_Prist) Щербаков,
Подскажите пожалуйста.
Хочу попробовать сделать сведение нескольких таблиц в одну с использованием PQ. Из-за того что в сводной таблице уже настроен отчёт (на отдельном листе сумируется количество строк по заданным условиям) хочется запихать всё это в уже готовую таблицу, не хочется править несколько десятков формул. Запрос в PQ я создал и он меня устраивает, а вот разместить его уже на имеющийся лист без смещения не получается. Я имею ввиду, что когда я его добавляю он вставляется на лист со сдвигом ячеек вниз и в итоге все формулы, которые обращаются к этому листу так же смещаются вниз. Есть ли возможность обойти этот костыль?
 
нет. По крайней мере я не знаю как можно настроить место вывода отчета из PQ. Не видал пока этих настроек. Возможно и есть вариант обойти иначе, но пока не поймем что у Вас где съезжает и на что должно ссылаться - маловероятно. Возможно, каким-то боком можно прикрутить ДВССЫЛ или ИНДЕКС.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков,
Обрезок рабочей таблицы в примере выше. Там должны быть ещё пару листов с отчётами, которые считают количество строк в таблице с данными (СЧЁТЕСЛИМН, СЧЁТЕСЛИ и т.д.). Вот на этих листах формулы и съезжают.
Жаль, что нет простого решения.
Зы. Придётся или формулы поправить после добавления запроса или ещё какой костыль сделать.
 
Нашёл я относительно простой костыль.
Загружаю запрос в нужный мне лист (или любой другой, но потом переименовать в необходимое имя). После из другой книги копирую отчёты через специальную вставку (интересуют формулы, но я копирую всё). Дальше просто правлю связи: Данные >>изменить связи >> выбираю текущую таблицу. Немного проще чем переписать (поправить) все формулы.
Страницы: 1
Наверх