Developing.ru

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

Имеется столбец с текстовыми данными (типа - "Каталог").
Данные отвечают именам подпапок (скажем - " Х-i "), которые постоянно, по мере необходимости, создаются сотрудниками в одной общей папке "Документы".
В каждой из этих подпапок находится по одному однотипному файлу .xllsx с неизменным названием (пусть будет - "Карточка").

Задача в том, чтоб из одной и той же ячейки каждой Карточки получить значение и присвоить его каждой ячейке Каталога, стоящей рядом с именем соответствующей подпапки.

Просто прописывать ссылки не имеет смысла - подпапки с Карточками постоянно добавляются и нет возможности установить для них, в отличии от содержащихся в них файлов (Карточек), даже стандартизированные имена, да это и не помогло бы. Данные в самих Карточках постоянно меняются, но структура файлов жесткая.

Попытка со старта просто выстроить сцеплением нужный для ссылки адрес (типа - "='W:\Документы\ Х-i \Карточка.xlsx'!А1") толку не дали (Excel ссылки заданные текстом отрабатывать не умеет, а жаль )).
Вот и получается, что нужен макрос, который будет при открытии файла Каталога каждый раз находить и выгружать нужные значения.

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

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

Спасибо.

Excel ссылки заданные текстом отрабатывать не умеет

на самом деле умеет. например:
=ДВССЫЛ(СЦЕПИТЬ("'";F2;F1))
где F2 - C:\Documents and Settings\Kompress\Мои документы\[
F1 - 0.xls]Лист1'!$D$4
Однако функция ДВССЫЛ может работать с сылками только на открытую книгу.

Дмит писал(а): Однако функция ДВССЫЛ может работать с сылками только на открытую книгу.

Увы. Подпапок с нужными файлами может быть больше сотни - не открывать же каждый раз их все. И главное, в файле, где Каталог, собранные значения должны сохраняться после закрытия, чтоб от него можно было сводные таблицы и другие отчетные формы строить.

Я вот думал, может ДВССЫЛ вместе с "активейтом" (Workbooks("маршрут и файл.xlsx").Activate потом Workbooks("маршрут и файл.xlsx").Close) зациклить и в макрос, который будет перебором выплевывать значения в Каталог, но чтоб сделать самому знаний не хватает.

1. Составляем список найденых файлов вместе с полным путем
2. Unzip xlsx
3. xl\worksheets\sheet1.xml (или другой нужный лист)
4. Смотрим содержимое нужной ячейки
5. Записываем в нужную ячейку каталога

Не знаю насчет макросов на VBA, но на других языках вполне реально. Причем скорость работы будет в десятки раз быстрее, нежели юзать Workbooks(filename).Activate/Open/Close, которые по нескольку секунд один файл открывают и это только ради одной ячейки)
Для отладки все равно нужна конкретная структура

На самом деле, решить поставленную задачу можно с помощью ссылок и без макросов :

1) В отдельном столбце - с помощью оператора & или стандартной функции =СЦЕПИТЬ() необходимо получить формулу, которая возвращает текст ссылки, т.е. Вашем случае значение формулы должно быть :

='W:\Документы\ Х-i \[Карточка.xlsx]ИмяЛиста'!А1

а сама формула (где ячейка A1 содержит " Х-i "), это :

="='W:\Документы\"&A1&"\[Карточка.xlsx]ИмяЛиста'!А1"

2) Затем, необходимо выделить все ячейки с такой формулой и в меню Правка выбрать команду Копировать (или воспользоваться сочетанием клавиш CTRL+C), после чего, в меню Правка выбрать команду Специальная вставка и в появившемся стандартном диалоговом окне установить переключатель напротив Значения и нажать кнопку OK.

3) После чего в меню Правка выбрать команду Заменить (CTRL+H) и в диалоговом окне, в полях Что: и Заменить на: ввести = и нажать кнопку Заменить все.

4) Теперь, повторить пункт 2 (если нужны значения, а не ссылки)

5) Сохранить изменения

Несмотря на многобуквие всего вышеопубликованного, проделать эти операции не займёт много времени, правда, если имя листа неизвестно, то, по всей видимости, придётся писать макрос ...

Ещё один вариант, это скачать надстройку Morefunc от Excel MVP - Laurent Longre, например, здесь и воспользоваться функцией =INDIRECT.EXT() которая является неким аналогом =ДВССЫЛ() но работает с закрытыми книгами (более подробную информацию можно получить в справке к надстройке)

pashulka писал(а): Несмотря на многобуквие всего вышеопубликованного, проделать эти операции не займёт много времени, правда, если имя листа неизвестно, то, по всей видимости, придётся писать макрос ...

Примерно так я сегодня и делаю. А с именем листа проблем особых нет - нужные ячейки в Карточках поименованы. Правда данных на деле из Карточек нужно не с одной ячейки получать, но это фигня. Проблема в другом - данные в Каталог постоянно добавляются и операции нужно проделывать регулярно. Я, извините, коммерческий директор целой компании, вступив в должность, застал ситуацию с учетом в безобразном состоянии. Быстренько накидал на коленке нечто подобное реляционной базе, а теперь время трачу на ... Пробовал поручать исполнителям - они раз через раз чего-нибудь да напутают. Необходимо человеческий фактор свести до минимума, вот и отвлекаю умных людей своими просьбами.

pashulka писал(а):Ещё один вариант, это скачать надстройку Morefunc от Excel MVP - Laurent Longre, ...

Че-то похоже не становится она у меня на 2013-й Екс. А не лицензионный ставить админы не велят. Продолжаю бороться. Если удасться - это будет лучший из возможных выходов. В любом случае, спасибо за наводку.

А, извиняюсь, никто не знает аналога идущего под 64-бит ? ... Че-ж так невезет-то?

1) На самом деле, неважно присвоено имя ячейке или нет, ибо при использовании ссылки на закрытую книгу - имя листа указывать всё равно необходимо.

2) Я не говорил, что все манипуляции нужно проделывать с одной единственной ячейкой, ибо в совете сказано о получении данных всех необходимых ячеек (во множественном числе)

3) Если нужен макрос, то лучше говорить предметно, т.е. просто выложить рабочую книгу, где будет указано в каких ячейках находятся названия каталогов, в каких необходимо получить данные ячеек, какие имена листов в источниках и т.п. (причём лучше выложить именно .XLS ибо не все пользуются новыми версиями Excel)

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

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

pashulka писал(а):2) Я не говорил, что все манипуляции нужно проделывать с одной единственной ячейкой, ибо в совете сказано о получении данных всех необходимых ячеек (во множественном числе)

Да, я понимаю - я упомянул просто в том смысле, что массив копируемых и вставляемых данных на деле больше, чем из моего описания возможно представилось. Но суть не в этом, я не хотел кого-нибудь запутать - извините. Я абсолютно правильно понял вашу рекомендацию, она работает - просто пытался объяснить, почему хочу более продвинутый функционал организовать.

pashulka писал(а):3) Если нужен макрос, то лучше говорить предметно, т.е. просто выложить рабочую книгу, где будет указано в каких ячейках находятся названия каталогов, в каких необходимо получить данные ячеек, какие имена листов в источниках и т.п. (причём лучше выложить именно .XLS ибо не все пользуются новыми версиями Excel)

Ну пример-то не сложный, просто суть в том что основной (сводный) файл отдельно, а файлы с данными в других папках собранных в одну папку. Это в виде примера не очень выложить получается . Я не расчитываю, что за меня всё полностью готовое сделают - имена, пути и количество вытягиваемых ячеек я уж поменяю-усложню, соображу, усидчивости хватит - мне бы текст того макроса, как рабочий вариант, хоть как-то близкий к задаче. Жалко с посоветованной надстройкой не получается (не идет на 64-бит, а сервак на 64).

Еще раз спасибо за советы.