Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Ссылка на старый файл в именах в Диспечере имен, Проблема при переносе листа
 
#1
28.07.2022 23:05:50
Уважаемые гуру, прошу помочь.

При переносе листа из активной книги, в том числе переносятся все имена (именованных диапазонов и т.д.). С какого то момента при переносе страницы из рабочей книги, имена в новом файле стали содержать ссылку на книгу, из которой был перенесен лист.
В текстоваом виде в Диспечере имен это выглядит так:
Код
Name=Trucks
Refers To='[Oldworkbook.0.14.xls]DataC'!$A$6:$A$10
А должна быть просто:
Код
Name=Trucks
Refers To=DataC!$A$6:$A$10
Так как все они содержат апостроф в начале ссылки ('), то удалить такие имена из Диспечера имен макросом не получается, можно только вручную, что делает работу бесполезной, т.к. там несколько десятков имен, а процесс создания новых файлов из старого происходит часто.

В других файлах, да и в этом, до какого-то момента, так не происходило. Как избавиться от этой ссылки в именах при копировании?
Изменено: viajador - 28.07.2022 23:06:56
 
 
 
#2
29.07.2022 02:38:04
Ситуацию по добавлению имени файла при копировании листа в другую книгу воспроизвести не удалось. Где и куда копать - непонятно.
Но можно потом макросом удалить все ссылки на другие файлы из Диспетчера имён активной книги: смотри вложение.
ЗЫ. Удаляются только имена файлов. Ошибочные имена (типа =#ССЫЛКА!...) не проверяются.
 
 
 
#3
29.07.2022 09:51:20
Цитата
написал:
Ситуацию по добавлению имени файла при копировании листа в другую книгу воспроизвести не удалось. Где и куда копать - непонятно.
Но можно потом макросом удалить все ссылки на другие файлы из Диспетчера имён активной книги: смотри вложение.
ЗЫ. Удаляются только имена файлов. Ошибочные имена (типа =#ССЫЛКА!...) не проверяются.
Спасибо за ответ.

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

Макросом эти имена не удаляются никак, как я писал вначале. При добавлении имен, в начале ссылки на диапазон ячеек, ставится имя файла источника. Они все начинаются на апостороф (в примере '[Oldworkbook.0.14.xls]DataC'!$A$6:$A$10). Макрос не может выполнить удаление таких имен, возникает ошибка
 
Изменено: viajador - 29.07.2022 09:52:36
 
 
 
#4
29.07.2022 10:23:50
Можно попробовать сделать так: в исходном файле переделать все имена с области Книга на область листа DataC. Тогда при копировании листа конфликта имён не будет.
Макрос делался для Excel2010, под ним всё нормально. Для других версий, возможно, есть нюансы.

Кстати, заметил, что апостроф к названию файла прямого отношения не имеет. Он появляется, если в полном названии листа есть пробел или некоторые другие служебные символы. Например, если имя ссылается на лист "Всем привет" в этой же книге, то апостроф появится: ='Всем привет'!$A$1:$B$2. Если название файла (точнее, окна) и листа не содержат пробела, то апострофа не появится: =[Пример1.xlsx]Лист1!$A$1:$B$2. Но если окно Пример1.xlsx закрыть, то в ссылке апостроф уже появляется:  ='D:\[Пример1.xlsx]Лист1'!$A$1:$B$2 - из-за слэша \. Если заново открыть файл, то исчезает.
Опять же, всё это для Excel2010.  
Изменено: tolikt - 29.07.2022 10:59:55
 
 
 
#5
29.07.2022 11:42:17
Цитата
написал:
Можно попробовать сделать так: в исходном файле переделать все имена с области Книга на область листа DataC. Тогда при копировании листа конфликта имён не будет.
Макрос делался для Excel2010, под ним всё нормально. Для других версий, возможно, есть нюансы.

Кстати, заметил, что апостроф к названию файла прямого отношения не имеет. Он появляется, если в полном названии листа есть пробел или некоторые другие служебные символы. Например, если имя ссылается на лист "Всем привет" в этой же книге, то апостроф появится: ='Всем привет'!$A$1:$B$2. Если название файла (точнее, окна) и листа не содержат пробела, то апострофа не появится: =[Пример1.xlsx]Лист1!$A$1:$B$2. Но если окно Пример1.xlsx закрыть, то в ссылке апостроф уже появляется:  ='D:\[Пример1.xlsx]Лист1'!$A$1:$B$2 - из-за слэша \. Если заново открыть файл, то исчезает.
Опять же, всё это для Excel2010.  
В исходном файле имена все локальные (DataC!$A$1:$B$2) и относительные. Нет ни одного абсолютного имени с указанием книги (файла). Они ведь автоматически, при создании, все локальные и такими создавались. Поэтому там нечего переделать.
Но при переносе листа в новый файл (пустой, там нет никаких имен), имена начинают ссылаться на исходную книгу. Они становятся абсолютными и уже содержат ссылку с указанием названия файла-источника, а то и пути к файлу, если лист переносится в книгу, в другую папку.

Все имена листов точно не содержат пробелов. Какие то макросы когда то не выполнялись на таких листах и пробелы я убираю сразу при создании листов уже много лет. По этой же причине не используется в любых названиях кириллица.  
 
 
 
#6
29.07.2022 20:29:41
Всё-таки ещё раз рекомендую поработать с именами в исходном файле, чтоб исключить возникновение проблемы со ссылками на другую книгу в именах. Но немного по-другому.
См. файл. Там есть имя OtherSheetName, которое относится к Лист1, но ссылается на ячейки на Лист2. Если скопировать Лист1 в новую книгу, то в ней как раз образуется имя OtherSheetName с именем файла в ссылке. Чтоб такого не возникало, надо все имена, которые относятся именно к этому листу и которые ссылаются на другой лист, исправить или удалить.
Причём, если лист именно переместить, а не скопировать, то в исходном файле в имена могут добавиться ссылки на название книги. И если исходный файл сохранить случайно, то так и останется корявое имя.
 
 
 
#7
29.07.2022 21:22:55
В этой теме много загадок.
Выложите, пожалуйста, пример файла, в котором у Вас не получается удалить имена книги (листа)  макросом.
Владимир
 
 
 
#8
31.07.2022 11:55:10
Цитата
написал:
Всё-таки ещё раз рекомендую поработать с именами в исходном файле, чтоб исключить возникновение проблемы со ссылками на другую книгу в именах. Но немного по-другому.

Спасибо огромное за ответы! Должен извиниться, нашел пробел в имени файла, который появился недавно. Тем не менее, удаление пробела из имени файла, проблему не решило, макрос удаления имен так и не работал.

Проблему решил случайно. В стандартном макросе, удаляющим имена из экспортного файла (того листа с данными, на которым были неудаляемые имена), не было обработчика ошибок. Добавил в макрос "On Error Resume Next" и все заработало как должно.

Файл работал более 10 лет и макрос работал успешно без этой строчки. Моя догадка: После недавних изменений файла, видимо, возникла перекрестная связь между именами. Макрос, удаляющий имена, не мог выполнить задачу, потому что какое то из имен нужно было удалить первым, а потом другое. С данной процедурой обработки ошибок, макрос смог добраться до того имени, которое нужно удалить первым.  
 
 
 
Страницы: 1
Наверх