Vba automation error неверная ссылка вперед или ссылка на не откомпилированный тип

Excel vba неверная ссылка вперед или ссылка на не откомпилированный тип Я пытаюсь написать скрипт, который открывает множество файлов Excel. Я продолжаю получать подсказку: Также я использую Excel 2010. Я нашел временное решение, которое, по крайней мере, позволит мне обработать эту работу. Я написал короткий скрипт AutoIt, который ждет появления окна «Обновить ссылки», а […]

Содержание

  1. Excel vba неверная ссылка вперед или ссылка на не откомпилированный тип
  2. Препараты
  3. Предпосылками
  4. Открытие вручную
  5. Открытие VBA
  6. Выводы
  7. Пример VBA Runtime Ошибка 1004 в Excel
  8. Ошибка выполнения VBA 1004 — Пример № 1
  9. Ошибка выполнения VBA 1004 — Пример № 2
  10. Ошибка выполнения VBA 1004 — Пример № 3
  11. Ошибка выполнения VBA 1004 — Пример № 4
  12. Ошибка выполнения VBA 1004 — Пример № 5
  13. То, что нужно запомнить
  14. Рекомендуемые статьи
  15. Переход между книгами при активной UserForm VBA
  16. Комментарии

Excel vba неверная ссылка вперед или ссылка на не откомпилированный тип

Я пытаюсь написать скрипт, который открывает множество файлов Excel. Я продолжаю получать подсказку:

Также я использую Excel 2010.

Я нашел временное решение, которое, по крайней мере, позволит мне обработать эту работу. Я написал короткий скрипт AutoIt, который ждет появления окна «Обновить ссылки», а затем нажимает кнопку «Не обновлять». Код выглядит следующим образом:

Пока, похоже, это работает. Однако я бы очень хотел найти решение, полностью основанное на VBA, чтобы сделать его автономным приложением.

После того, как все детали были обобщены и обсуждены, я потратил 2 честных часа на проверку параметров, и это обновление должно расставить все точки i s.

Препараты

Прежде всего, я выполнил чистую установку Office 2010 x86 на виртуальной машине Clean Win7 SP1 Ultimate x64, работающей на VMWare (это обычная процедура для моих повседневных задач тестирования, поэтому многие из них развернуты).

Затем я изменил только следующие параметры Excel (т. Е. Все остальные остались после установки):

  • Advanced > General > Ask to update automatic links проверено:

  • Trust Center > Trust Center Settings. > External Content > Enable All. (хотя тот, который относится к Data Connections, скорее всего, не важен для данного случая):

Предпосылками

Открытие вручную

Приведенный выше общий файл показывает при открытии (с перечисленными выше параметрами Excel) 2 предупреждения — в порядке появления:

После нажатия на Update я ожидал получить еще один:

Итак, я полагаю, что моя среда тестирования теперь очень похожа на OP ‘s. Пока все хорошо, мы наконец-то

Открытие VBA

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

1. Простое приложение. Workbooks.Open

Не удивительно — это выдает ОБА предупреждения, как при ручном открытии выше.

2. Application.DisplayAlerts = False

3. Application.AskToUpdateLinks = False

Очевидно, этот код заканчивается подавлением ОБА ПРЕДУПРЕЖДЕНИЙ.

5. UpdateLinks: = False

Наконец, это однострочное решение (первоначально предложенное @brettdj ) работает так же, как Double False: NO WARNINGS показаны!

Выводы

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

  1. Параметры Excel имеют значение, независимо от версии, особенно когда речь идет о решениях VBA.
  2. У каждой проблемы есть короткое и элегантное решение — вместе с не очевидным и сложным. Еще одно доказательство тому!)

Большое спасибо всем, кто внес свой вклад в решение, и особенно ОП, который поднял вопрос. Надеюсь, мои исследования и тщательно описанные этапы тестирования были полезны не только для меня)

Оригинальный ответ (проверено на Excel 2007 с определенными параметрами):

Этот код прекрасно работает для меня — он просматривает ВСЕ файлы Excel, указанные с использованием подстановочных знаков в InputFolder :

Я пробовал это с книгами с недоступными внешними ссылками — без предупреждений.

Откройте редактор VBA Excel и введите его в окно немедленного просмотра (см. Снимок экрана)

Закройте Excel, а затем откройте файл. Это не будет предлагать вам снова. Не забудьте сбросить его при закрытии книги, иначе она не будет работать и для других книг.

Таким образом, применяя его к вашему коду, ваш код будет выглядеть так

Sigil, приведенный ниже код работает и с файлами с неработающими ссылками. Вот мой тестовый код.

  1. Создайте 2 новых файла. Назовите их Sample1.xlsx и Sample2.xlsx и сохраните их в C:
  2. В ячейке A1 из Sample1.xlsx введите эту формулу =’C:[Sample2.xlsx]Sheet1′!$A$1
  3. Сохраните и закройте оба файла
  4. Удалить Sample2.xlsx .
  5. Откройте новую рабочую книгу и вставьте в нее модуль и запустите Sample . Вы заметите, что вы не получите подсказку.

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

Перейдите в DATA -> Edit Link -> Startup Prompt (внизу), затем выберите лучший вариант для вас.

Я хотел отключить запрос, который спрашивает, хотите ли вы обновить ссылки на другую книгу, когда моя книга открывается в Excel вручную (в отличие от программного открытия через VBA). Я попытался включить: Application.AskToUpdateLinks = False в качестве первой строки в моем макросе Auto_Open() , но это не сработало. Однако я обнаружил, что если вы поместите его вместо этого в функцию Workbook_Open() в модуле ThisWorkbook , он будет работать блестяще — диалоговое окно подавлено, но обновление по-прежнему происходит в фоновом режиме.

(У меня недостаточно точек повторений, чтобы добавить комментарий, но я хочу внести ясность в ответы здесь)

Application.AskToUpdateLinks = False, вероятно, не то, что вы хотите.

Если установлено значение False, то MS Excel будет пытаться автоматически обновлять ссылки, просто не будет запрашивать пользователя заранее, что-то вроде противоречия.

Правильное решение, если вы хотите открыть файл без обновления ссылок:

Workbook.Open (UpdateLinks: = 0)

Надеюсь дать дополнительный вклад в решение этого вопроса (или его части).

Это будет работать для открытия файла Excel из другого. Для изменения строки кода из Mr. Peter L. используйте следующее:

Это в MSDS . В результате он просто обновляет все (да, все) без предупреждения. Это также можно проверить, если вы записываете макрос.

В MSDS это относится к MS Excel 2010 и 2013 . Я думаю, что MS Excel 2016 это также покрывает.

Ошибка VBA 1004 — это ошибка, с которой мы сталкиваемся при выполнении кода в VBA. Он также известен как ошибка времени выполнения VBA. Когда мы работаем в VBA или на любом другом языке программирования или даже в нашей повседневной работе, мы сталкиваемся с различными видами ошибок. Иногда даже мы пропускаем один символ в коде, что приводит к тому, что весь код не работает или, возможно, весь код неверен.

Ошибки, безусловно, являются частью кода, который мы пишем. Это может быть непреднамеренным, но они существуют. Независимо от того, насколько мы профессиональны в кодировании, ошибки во время выполнения могут возникнуть где угодно. Как объяснено выше VBA 1004 Ошибка — это ошибка, которая возникает во время выполнения кода в Excel. Это также называется ошибкой приложения или объекта.

Существуют различные типы причин, по которым мы получаем VBA Runtime Error 1004 в Excel, давайте рассмотрим некоторые из них.

  • Ошибка выполнения VBA 1004: сбой метода «Диапазон» объекта «_ Global»:

Эта ошибка возникает, когда значение диапазона, которое мы ссылаемся на VBA, неверно. Он также называется метод «Диапазон» объекта «_ Global» не удалось.

  • Ошибка выполнения VBA 1004: это имя уже занято.Попробуйте другой:

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

  • Ошибка выполнения VBA 1004: невозможно получить свойство select класса Range:

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

  • Ошибка выполнения VBA 1004: сбой метода «Открыть» объекта «Книги»:

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

  • Ошибка выполнения VBA 1004: извините, мы не смогли найти:

Мы получаем эту ошибку, когда пытаемся открыть лист, который не существует.

Как мы узнали, могут быть разные причины, по которым мы получаем ошибку во время выполнения. Ошибка выполнения может возникнуть в любой строке кода. Нам нужно научиться обрабатывать эти ошибки, и это называется VBA Error Handling.

Пример VBA Runtime Ошибка 1004 в Excel

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

Вы можете скачать этот шаблон Excel с ошибкой VBA 1004 здесь — Шаблон Excel с ошибкой VBA 1004

Ошибка выполнения VBA 1004 — Пример № 1

  • Давайте назовем этот заголовок таблицы как DATA .

  • Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.

  • Вызовите заголовок, который мы назвали следующим кодом, написанным ниже.

  • Когда мы запускаем код, мы видим в Excel, что он был выбран, поскольку мы правильно вызвали заголовок.
  • Теперь мы неправильно написали орфографию имени заголовка.

  • Запустите код еще раз, чтобы увидеть результат.

Мы получаем Excel VBA Runtime Ошибка 1004, потому что мы неправильно написали имя диапазона.

Ошибка выполнения VBA 1004 — Пример № 2

Мы получаем эту ошибку, когда пытаемся переименовать лист с именем, которое уже занято. Например, я переименовал лист 1 в « Ананд », и я постараюсь переименовать лист 2 в тот же, чтобы увидеть результат.

  • Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
  • Объявите подфункцию, чтобы начать писать код.

  • Попробуйте переименовать лист 2 в Anand с помощью следующего кода ниже,

  • Запустите приведенный выше код и посмотрите результат.

Ошибка выполнения VBA 1004 — Пример № 3

Я попытаюсь добавить значение из листа 2 к переменной на листе 3. Но я не буду активировать лист 2 и посмотрю, что произойдет.

  • Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
  • Объявите подфункцию, чтобы начать писать код.

  • Объявите две переменные A и B как целое число.

  • В переменной B сохраните значение A в дополнение к ячейке A1 листа 2.

  • Давайте предположим, что код работает, и используем функцию msgbox для отображения значения B.

  • Запустите код, чтобы увидеть полученный результат.

Мы получаем эту ошибку, потому что мы не активировали лист 2, но мы пытаемся использовать значение листа 2.

Ошибка выполнения VBA 1004 — Пример № 4

Мы сталкиваемся с этой ошибкой во время выполнения, когда у нас уже открыто одно и то же имя рабочей книги, но мы пытаемся открыть ее снова.

В этом примере я уже переименовал свою книгу в VBA 1004 Error.xlsm, и я попытаюсь снова открыть ее, которая уже открыта, и посмотреть, получу ли я ошибку VBA 1004.

  • Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
  • Объявите подфункцию, чтобы начать писать код.

  • Объявите переменную в качестве рабочей книги.

Попробуйте открыть книгу, которую мы уже открыли, с помощью следующего кода.

Запустите приведенный выше код, чтобы увидеть результат.

Мы получаем эту ошибку, потому что мы уже открыли ту же книгу.

Ошибка выполнения VBA 1004 — Пример № 5

Мы получаем эту ошибку, когда пытаемся открыть книгу, которой не существует. Это в некоторой степени похоже на приведенную выше ошибку, поскольку VBA не может найти книгу.

  • Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
  • Объявите подфункцию, чтобы начать писать код.

  • Попробуйте открыть любую книгу с помощью следующего кода,

  • Я уже удалил лист с места.
  • Запустите код, чтобы увидеть результат.

Поскольку лист не существует в данном месте, мы получаем эту ошибку.

То, что нужно запомнить

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

Рекомендуемые статьи

Это было руководство к VBA 1004 Ошибка. Здесь мы обсудили Excel VBA Runtime Error 1004 вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Переход между книгами при активной UserForm VBA

Благодарности принимаются на кошелёк WebMoney: R318574877619 и Яндекс.Деньги: 41001335672216

Точно .
Спасибо большое!- не знал!!
Теперь другая проблема, появился доступ к листам самой базы- а это не хорошо.Я специально поставил на всех формах запрет закрытия по крестику и пароль который убирается со всех листов книги при открытии книги(чтобы не мешал работе макросов) и одновременном запуске главной формы а при закрытии (закрыть можно только с главной) происходит установка пароля на все листы и автозакрытие книги. Таким образом я не давал возможность юзеру попасть на листы этой базы, даже если он в момент запуска отключит макросы можно только полистать листы — пароль снят не будет. Тоесть в Вашем предложенном варианте появляется доступ к листам базы тогда когда уже пароль снят и там можно накуралесить делов тока держись — кладовщицы народ шустрый .
Наверно в таком случае мне надо подумать над другим способом защиты листов базы, чоб макрос работал а ручки нет. А как на счет скрытых листов.
На тулбаре «Элементы управления» есть кнопка «Свойства».
Стоя на листе в свойстве Visible можно установить значение 2, и тогда лист нельзя будет сделать видимым даже при помощи Формат/Лист/Показать (только из редактора VBA)
Тогда может и заморочки с паролями не понядобятся? Спасибо!
Мысль конешно интересная но как это сделать в моём случае?
число листов в базе колеблется в районе 200 и самое интересное их число непрерывно (в процессе работы с базой) изменяется программно, как добавляются так и удаляются, а пользователь видит только первый лист за формой которому я просто поставил зелёный фон и ярлычки от листов которые он не может активировать(поэтому он и не видит что происходит на рабочих листах). Если бы это сделать как с установкой/снятием паролей одновременно на все листы сколько бы их там не было который я использую

Нет ничего проще

If Sh.Index>1 then Sh.Visible=2

. и спрятали всё, кроме первого

Действительно! Что то я протупил.
но теперь другое, иногда программе надо выбрать лист (например перед загрузкой диапазона в комбобокс из 6 колонок), метод селект завершен неверно, наверно надо перед этим на время делать так Sh.Visible=-1 а потом Sh.Visible=2
Я на верном пути ? Или есть другой выход?
Большой спасибо за помощь!

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

В данном случае, многое зависит от того, как Вы заполняете комбобокс.
Если макросом считываете значения с листа, и по-одному записываете в комбобокс, — то отображать лист не требуется.

полностью откажитесь от использования методов Select и Activate в своём коде.
В 95 % случаев их использование не требуется, и только замедляет выполнение кода.

В любом случае, для записи в комбобокс использование метода Select не требуется.

Благодарности принимаются на кошелёк WebMoney: R318574877619 и Яндекс.Деньги: 41001335672216

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

Комментарии

Елена, это уже совсем другой макрос нужен.
Можем сделать под заказ. От 1500 руб.

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

Не знаю что даже сказать. Магия сработала. Спасибо разработчик большое! Здоровья тебе и твоим близким.

Спасибо Дружище. Желаю тебе всего хорошего, чего сам себе желаешь.

Отличный макрос! Спасибо!

Автор, спасибо огромное!

Спасибо ОГРОМНОЕ . Помогло, заменил 267 гиперссылок.

Спасибо автору! Макрос помог!

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

Сработал расширенный макрос,
более 5000 адресов исправлены на коректные
Огромная благодарность
Дай Бог тебе здоровья автор

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

Я спасён!
Огромное спасибо за помощь! Помогли сэкономить огромный вал рабочего времени!

Огромно спасибо. Автоматически исправил более 5 тысяч ссылок в архиве. Все работает. Ну просто нет слов, как я Вам признателен.

СПАСИБО ОГРОМНОЕ ЗА ВАШ РЕСУРС И ДОБРОТУ.
Правда, Ваш макрос у меня не работает почему-то, цель сделать из абсолютных ссылок относительные.но выяснилось, что вполне работает обычное «найти и заменить»
в строку «найти» пишем кусок пути ДО нужной папки, а в «заменить» оставляем пустоту, ставим галку «во всей книге» и жмем «заменить все»

Слетели все ссылки после вставки автосохраненного файла. Почему то данный макрос не работает
Sub ZamenaIsporchennihGiperssilok()
On Error Resume Next
Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet
‘ part of hyperlink, which you want to change
oldString = «C:UsersDianaGAAppDataRoamingMicrosoftExcel»
‘ to what to change
newString = «C:UsersDianaGADekstopÀÁÌ — 2015»
For Each sh In ActiveWorkbook.Worksheets ‘ ïåðåáèðàåì âñå ëèñòû â àêòèâíîé êíèãå
For Each hl In sh.Hyperlinks ‘ ïåðåáèðàåì âñå ãèïåðññûëêè íà ëèñòå
If hl.Address Like «*» & oldString & «*» Then
hl.Address = Replace(hl.Address, oldString, newString)
End If
Next
Next sh
End Sub
Никаких действий не происходит

Тут ничего особенного делать не надо, — Excel сам все гиперссылки в книге подправит, как только вы лист переименуете.

Добрый вечер всем! Выручайте! Как изменить имя листа книги в Excel при этом не потеряв гиперссылки? Заранее благодарю!

Спасибо тебе, добрый человек!

Благодарность не знает границ! Спасибо очень выручили!)))

Автор, огромное вам СПАСИБО. Очень выручили

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

vitamax555, а какое отношение ваш вопрос имеет к теме статьи, — замене гиперссылок?
На вопросы «не в тему» не отвечаю

Доброго дня! Подскажите, пожалуйста, у меня есть экселевский файл и в нем прописан код VBA. Вроде бы работал правильно, но мне нужно было изменить данные, так как не правильно тянул часть данных из исходника. Как только изменил данные и исходник, данные стали грузиться правильно, однако очень долго. Так раньше на это требовалось около 1 минуты, теперь это занимает 10. Что мне делатать. Подскажите, пожалуйста! Спасибо!

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

Здравствуйте, Юрий
Достаточно убрать цикл по листам,
применяя изменения только к активному листу:

Спасибо! Очень выручила данная статья!
Но как всегда возник вопрос — как изменить данный макрос, что бы изменения коснулись только ОДНОГО листа в книге? (Файлов больших несколько, листов в каждом масса, соответственно разбирать файл по листам а потом собирать его обратно — очень долгий и не совсем желаемый процесс. )
Заранее СПАСИБО.

целую твои умелые ручки, автор!

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

Здравствуйте, Владимир
Так не получится, — Excel (тем более, если он не запущен) не отслеживает перемещение файлов и папок.
Да и как Excel узнает, где искать перемещенные файлы. мало ли куда вы их перенесете, вдруг вообще на другой комп.

Надо делать гиперссылки относительными (чтобы путь прописывался относительно файла Excel)?
а папку с файлами держать в той же папке. где сохранён файл Excel, и не переименовывать.
Тогда,если переносите на другой комп файл Excel вместе с папкой, гиперссылки вроде должны сохранять работоспособность.

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

Здравствуйте, Татьяна.
Так вы перенесите файлы на другой комп в ту же папку (чтобы путь к папке не изменился), — и тогда ничего переделывать не придётся.
А если путь к папке изменится, — тогда макрос этот поможет.

Как запустить макрос — объяснять не буду (об этом в интернете много написано, в т.ч. есть видеоинструкции)
В макросе надо будет только задать, что на что менять (в коде указать старый путь к папке, и новый)
Попробуйте, — всё очень просто.
PS: Чтобы вставить макрос в файл, надо нажать сначала Alt + F11, потом Ctrl + R, потом Enter, — и в появившееся большое текстовое поле скопировать код из статьи.

Здравствуйте!
Я в вопросе макросов вообще новичок, вернее никогда с ними не работала, поэтому прошу помощи в следующем вопросе. Я создала файл в EXELе со множеством гиперссылок на разные файлы, эти файлы во многих папках, но эти папки собрала в одну. Теперь мне нужно перенести рабочий файл на другой компьютер. Подскажите, пожалуйста как я могу это сделать не потеряв все гиперссылки, их более 3000. Могу ли я воспользоваться макросом который вы предлагаете? Буду очень признательна за помощь.

Да повыситься многократно твоя карма, уважаемый автор! Спасло!:)

Большое человеческое СПА-СИ-БО!
Ваш макрос здорово меня выручил.

и снова не работает, вот так у меня прописан макрос6

Sub ЗаменаИспорченныхГиперссылок()
On Error Resume Next
Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet
‘ part of hyperlink, which you want to change
oldString = «AppDataLocalLocalSettingsAppDataLocalLocalSettingsTemp»
‘ to what to change
newString = «Ok!»
For Each sh In ActiveWorkbook.Worksheets ‘ перебираем все листы в активной книге
For Each hl In sh.Hyperlinks ‘ перебираем все гиперссылки на листе
If hl.Address Like oldString & «*» Then
hl.Address = Replace(hl.Address, oldString, newString)
End If
Next
Next sh
End Sub

более того при попытке заменить в адресах хотя бы один символ (для проверки работоспособности)например:

‘ part of hyperlink, which you want to change
oldString = «Temp»
‘ to what to change
newString = «Ok!»

Попробуйте менять ЧАСТЬ пути в гиперссылках
(пути в гиперссылках могут быть сохранены не абсолютные, а относительные, — потому замена и не выполняется)

постараюсь более детально описать процесс создания файла:
вставлены именно макросы, вставлены непосредственно т.е. -> правая кнопка -> гиперссылка -> выбор файла из архива где все добро и хранится. Первые 100 ссылок проверил на работоспособность в т.ч. с переносом папки архива на домашний компьютер, убедившись в работоспособности базы данных продолжил, а после обнаружил проблему. Гиперссылки забиты не формулами а вставкой. Найти и заменить не помогло, «не обнаружено совпадений» правильность написания проверил трижды.
Возможно что в ячейках адрес как таковой не прописан, его можно увидеть только подведя курсор к ячейке с гиперссылкой и он отображается в всплывающей подсказке, именно в ней я и увидел несоответствие фактического и прописанного адресов и вот его и нужно изменить, но в строке формулы ячейки адреса не прописаны наверное поэтому «найти и заменить» не сработало.
Макрос так же не сработал. Могу предоставить скриншоту по каждым моим действиям, может ошибка именно в последовательности

А вы уверены, что у вас на листе именно гиперссылки (синие, подчеркнутые), а не формулы, например?

Нажмите Ctrl + H (диалоговое окно поиска и замены),
в поле «Что заменять» введите AppDataLocalLocalSettingsAppDataLocalLocalSettingsTemp
в поле «На что заменять» введите Ok!
и нажмите «Заменить все»

Если это поможет, — то вам нужен другой макрос (попроще, который выполняет замены в ячейках, а не в гиперссылках):
вместо

Доброго дня! Помогите советом, до сего дня макросами не пользовался и в этой области «чайник».
Проблема следующая на рабочем компьютере создаю базу данных типовых серий (вставляю гиперссылки), скидываю по почте на домашний дорабатываю (на этом этапе гиперссылки вовсе не трогаю, добавляю примечания и прочую доп.информацию. ) скидываю обратно ч/з почту, а на работе сюрприз: большинство гиперссылок не работают. При объеме базы в 3500-5000 файлов попробуйте представить тяжесть разочарования и стресса. Выяснил что адрес сменился с
D:Нормативно техническая базаТиповые материалы для проектированияТиповые серииOk!
например было D:Нормативно техническая базаТиповые материалы для проектированияТиповые серииOk!-312 в.0.djvu
стало D:Нормативно техническая базаТиповые материалы для проектированияТиповые серииAppDataLocalLocalSettingsAppDataLocalLocalSettingsTemp-312 в.0.djvu
макрос бы помог да не выходит ничего. Пожалуйста помогите разобраться, вот как я его записал

Sub ZamenaIsporchennihGiperssilok()
On Error Resume Next
Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet
‘ part of hyperlink, which you want to change
oldString = «D:Нормативно техническая базаТиповые материалы для проектированияТиповые серииAppDataLocalLocalSettingsAppDataLocalLocalSettingsTemp»
‘ to what to change
newString = «D:Нормативно техническая базаТиповые материалы для проектированияТиповые серииOk!»
For Each sh In ActiveWorkbook.Worksheets ‘ перебираем все листы в активной книге
For Each hl In sh.Hyperlinks ‘ перебираем все гиперссылки на листе
If hl.Address Like oldString & «*» Then
hl.Address = Replace(hl.Address, oldString, newString)
End If
Next
Next sh
End Sub

Однако положительных признаков работы не подал.

Ну можно «выделить» так строки — вот только нужно ли?
Если вы не знаете, зачем это «выделение» — зачем его делаете?
Это точки останова макроса. Оно вам надо, чтобы макрос останавливался на каждой строке?

Код один раз сработал — значит, проблема точно не в нём.
Ищите проблему в соответствии пути гиперссылок, и текст для замены в макросе.
И убирайте все красные точки (и не ставьте их впредь)

Скажите пожалуйста — скрипт сработал лишь раз. Затем почему то перестал работать. Я зашел в «Войти» в Макросах и там можно выделить все строчки красным (красная точка и красная линия), кроме Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet. На нее так же нельзя перетащить стрелку дебага. То есть я так понял скрипт стопорится на ней. Хотя 1 раз скрипт точно сработал. Я сохранил документ, затем его немного переместили, я изменил скрипт снова как надо (то есть места папок назначения) и он перестал работать.

Огромное спасибо! Очень помогло! Можно сказать, спасло от кары босса )))!

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

то есть
. создать ссылки на сайты.

>> В меню «Изменение Гиперссылки» слева есть возможность выбора:
это для того, чтобы можно было мышкой создать ссылку. а так, эти кнопки особой роли не играют.
можно при помощи этих кнопок создать ссылки на файлы, затем запустить скрипт по изменению ссылок, поменять например «http://ya.ru» на «helloworld.txt» и при следующем открытии меню «Изменение Гиперссылки» слева будет нажата другая кнопочка

В меню «Изменение Гиперссылки» слева есть возможность выбора:
Связать с:
— файлом, веб-страницей;
— местом в документе;
— новым документом;
— электронной почтой.
Ты не ошибся.

а по-моему все ссылки одинаковые. она становится гипер или обычной просто из-за содержимого ссылки. например если начинается на «http://. » значит гипер, а если «C:. » или «\192.168.1.1. » значит локальная или сетевая там.

как сделать чтобы менял обычные ссылки а не гипер ?

Вроде бы формат файла не изменился (xls) 97-2003, как был так и остался (не у всех, просматривающих, таблицу есть новый (xlsx))

Источник

Источник
 

egorkaqwerty

Пользователь

Сообщений: 20
Регистрация: 12.11.2019

#1

28.11.2019 09:54:30

Добрый день.

Во время исполнения макроса, а именно переключение с одного листа на другой(после использования макроса удаление, переход на лист «свод2»), выскакивает ошибка «Неверная ссылка вперед или ссылка на неоткомпилированный тип». В предыдущие дни все работало нормально, никаких правок в сам файл, либо в код не вносил. Перерыл весь гугл, но похожих проблем не нашел.
Вопросы- как это починить и как не допускать этого в будущем?

Код 
  Sheets("свод1").Select
 Call удаление
  Sheets("свод2").Select
 Call удаление2
 

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 13997
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

#2

28.11.2019 10:07:57

Цитата
egorkaqwerty написал:
как это починить

на основе представленных 4-х строк кода помочь нечем. Они точно ошибок не несут.

Скрытый текст

Изменено: Дмитрий(The_Prist) Щербаков — 28.11.2019 10:10:42

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

 

Alexey795

Пользователь

Сообщений: 37
Регистрация: 31.03.2018

#3

06.10.2020 12:32:12

Код 
'работает
Set a = Workbooks(название_файла)

'работает
Set a = Workbooks(название_файла).Worksheets(название_листа)

'не работает
Set a = Workbooks(название_файла).Worksheets(название_листа).ListObjects(название_таблицы)

У меня такая ошибка возникает при сохранении объекта-таблицы в переменную.

Проблема очень существенная, из-за этого весь процесс буксует( Как чинить не ясно.
Создал «микро-таблицы» на разных листах, попробовал к ним обратится — и такая же ошибка.

Прикрепленные файлы

  • errVBA0001.PNG (4.85 КБ)

Изменено: Alexey795 — 06.10.2020 12:33:22
 

New

Пользователь

Сообщений: 4405
Регистрация: 06.01.2013
 

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 13997
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

#5

06.10.2020 13:14:10

Цитата
Alexey795 написал:
У меня такая ошибка возникает

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

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

 

OblivionR

Пользователь

Сообщений: 273
Регистрация: 28.05.2018

Надеюсь кому-то поможет! Столкнулся с подобной ошибкой сегодня. Скопируйте лист, удалите его, и поменяйте название копии -> все заработает. Почему? Не знаю.)
 

Олег Ирхин

Пользователь

Сообщений: 29
Регистрация: 07.07.2022

#7

22.09.2022 11:02:53

Цитата
написал:
Надеюсь кому-то поможет! Столкнулся с подобной ошибкой сегодня. Скопируйте лист, удалите его, и поменяйте название копии -> все заработает. Почему? Не знаю.)

Добрый день!
Такая же беда..
К сожалению замена листа копией с переименованием не помогла.

Возможно есть еще варианты борьбы?
Ну и конечно очень любопытно разобраться в причинах возникновения.

Добавлю инфы: на вкладке «Разработчик» нажимаю слева «Макросы» — и удивительное дело, ни один макрос не отображается. Иду по кнопочке «Visual Basiic» — щелкаю дважды любой из модулей — и вижу макросы в полной красе.

Ещё инфа — на листах где «накрылись» макросы, так же перестала работать надстройка ExcelTools (форма календарика для быстрого ввода даты в ячейку).
Вчерашняя копия файла прекрасно работает…

Изменено: Олег Ирхин — 22.09.2022 11:21:37
 

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 13997
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

#8

22.09.2022 12:49:40

Цитата
Олег Ирхин написал:
Такая же беда

и такой же ответ

Цитата
Дмитрий(The_Prist) Щербаков написал:
Приложите пример со своим кодом, таблицами и ошибками — тогда будет о чем говорить

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

 

Олег Ирхин

Пользователь

Сообщений: 29
Регистрация: 07.07.2022

#9

22.09.2022 17:58:45

Цитата
написал:

Цитата
Олег Ирхин написал:
Такая же беда

и такой же ответ

Цитата
Дмитрий(The_Prist) Щербаков написал:
Приложите пример со своим кодом, таблицами и ошибками — тогда будет о чем говорить

Ребята, сам порешал. До причин сбоя не докопался, но исправить смог — всё же помогло простое копирование листов. Сразу так не получилось по глупой причине — при копировании листов автоматически присваивались новые номера «Смарт таблицам», ну а макросы ессно ссылались на прежние. Когда откорректировал макросы — всё заработало.
 

Rambler68

Пользователь

Сообщений: 2
Регистрация: 06.04.2015

#10

16.01.2023 19:42:06

Цитата
написал:

Цитата
написал:
 

Цитата
Олег Ирхин  написал:
Такая же беда

 и такой же ответ
 

Цитата
Дмитрий(The_Prist) Щербаков  написал:
Приложите пример со своим кодом, таблицами и ошибками — тогда будет о чем говорить

 

Ребята, сам порешал. До причин сбоя не докопался, но исправить смог — всё же помогло простое копирование листов. Сразу так не получилось по глупой причине — при копировании листов автоматически присваивались новые номера «Смарт таблицам», ну а макросы ессно ссылались на прежние. Когда откорректировал макросы — всё заработало.

Да, у меня тоже сработал этот способ решения такой-же проблемы.
Источник

Excel vba неверная ссылка вперед или ссылка на не откомпилированный тип

Я пытаюсь написать скрипт, который открывает множество файлов Excel. Я продолжаю получать подсказку:

Также я использую Excel 2010.

Я нашел временное решение, которое, по крайней мере, позволит мне обработать эту работу. Я написал короткий скрипт AutoIt, который ждет появления окна «Обновить ссылки», а затем нажимает кнопку «Не обновлять». Код выглядит следующим образом:

Пока, похоже, это работает. Однако я бы очень хотел найти решение, полностью основанное на VBA, чтобы сделать его автономным приложением.

После того, как все детали были обобщены и обсуждены, я потратил 2 честных часа на проверку параметров, и это обновление должно расставить все точки i s.

Препараты

Прежде всего, я выполнил чистую установку Office 2010 x86 на виртуальной машине Clean Win7 SP1 Ultimate x64, работающей на VMWare (это обычная процедура для моих повседневных задач тестирования, поэтому многие из них развернуты).

Затем я изменил только следующие параметры Excel (т. Е. Все остальные остались после установки):

  • Advanced > General > Ask to update automatic links проверено:

  • Trust Center > Trust Center Settings. > External Content > Enable All. (хотя тот, который относится к Data Connections, скорее всего, не важен для данного случая):

Предпосылками

Открытие вручную

Приведенный выше общий файл показывает при открытии (с перечисленными выше параметрами Excel) 2 предупреждения — в порядке появления:

После нажатия на Update я ожидал получить еще один:

Итак, я полагаю, что моя среда тестирования теперь очень похожа на OP ‘s. Пока все хорошо, мы наконец-то

Открытие VBA

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

1. Простое приложение. Workbooks.Open

Не удивительно — это выдает ОБА предупреждения, как при ручном открытии выше.

2. Application.DisplayAlerts = False

3. Application.AskToUpdateLinks = False

Очевидно, этот код заканчивается подавлением ОБА ПРЕДУПРЕЖДЕНИЙ.

5. UpdateLinks: = False

Наконец, это однострочное решение (первоначально предложенное @brettdj ) работает так же, как Double False: NO WARNINGS показаны!

Выводы

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

  1. Параметры Excel имеют значение, независимо от версии, особенно когда речь идет о решениях VBA.
  2. У каждой проблемы есть короткое и элегантное решение — вместе с не очевидным и сложным. Еще одно доказательство тому!)

Большое спасибо всем, кто внес свой вклад в решение, и особенно ОП, который поднял вопрос. Надеюсь, мои исследования и тщательно описанные этапы тестирования были полезны не только для меня)

Оригинальный ответ (проверено на Excel 2007 с определенными параметрами):

Этот код прекрасно работает для меня — он просматривает ВСЕ файлы Excel, указанные с использованием подстановочных знаков в InputFolder :

Я пробовал это с книгами с недоступными внешними ссылками — без предупреждений.

Откройте редактор VBA Excel и введите его в окно немедленного просмотра (см. Снимок экрана)

Закройте Excel, а затем откройте файл. Это не будет предлагать вам снова. Не забудьте сбросить его при закрытии книги, иначе она не будет работать и для других книг.

Таким образом, применяя его к вашему коду, ваш код будет выглядеть так

Sigil, приведенный ниже код работает и с файлами с неработающими ссылками. Вот мой тестовый код.

  1. Создайте 2 новых файла. Назовите их Sample1.xlsx и Sample2.xlsx и сохраните их в C:
  2. В ячейке A1 из Sample1.xlsx введите эту формулу =’C:[Sample2.xlsx]Sheet1′!$A$1
  3. Сохраните и закройте оба файла
  4. Удалить Sample2.xlsx .
  5. Откройте новую рабочую книгу и вставьте в нее модуль и запустите Sample . Вы заметите, что вы не получите подсказку.

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

Перейдите в DATA -> Edit Link -> Startup Prompt (внизу), затем выберите лучший вариант для вас.

Я хотел отключить запрос, который спрашивает, хотите ли вы обновить ссылки на другую книгу, когда моя книга открывается в Excel вручную (в отличие от программного открытия через VBA). Я попытался включить: Application.AskToUpdateLinks = False в качестве первой строки в моем макросе Auto_Open() , но это не сработало. Однако я обнаружил, что если вы поместите его вместо этого в функцию Workbook_Open() в модуле ThisWorkbook , он будет работать блестяще — диалоговое окно подавлено, но обновление по-прежнему происходит в фоновом режиме.

(У меня недостаточно точек повторений, чтобы добавить комментарий, но я хочу внести ясность в ответы здесь)

Application.AskToUpdateLinks = False, вероятно, не то, что вы хотите.

Если установлено значение False, то MS Excel будет пытаться автоматически обновлять ссылки, просто не будет запрашивать пользователя заранее, что-то вроде противоречия.

Правильное решение, если вы хотите открыть файл без обновления ссылок:

Workbook.Open (UpdateLinks: = 0)

Надеюсь дать дополнительный вклад в решение этого вопроса (или его части).

Это будет работать для открытия файла Excel из другого. Для изменения строки кода из Mr. Peter L. используйте следующее:

Это в MSDS . В результате он просто обновляет все (да, все) без предупреждения. Это также можно проверить, если вы записываете макрос.

В MSDS это относится к MS Excel 2010 и 2013 . Я думаю, что MS Excel 2016 это также покрывает.

Ошибка VBA 1004 — это ошибка, с которой мы сталкиваемся при выполнении кода в VBA. Он также известен как ошибка времени выполнения VBA. Когда мы работаем в VBA или на любом другом языке программирования или даже в нашей повседневной работе, мы сталкиваемся с различными видами ошибок. Иногда даже мы пропускаем один символ в коде, что приводит к тому, что весь код не работает или, возможно, весь код неверен.

Ошибки, безусловно, являются частью кода, который мы пишем. Это может быть непреднамеренным, но они существуют. Независимо от того, насколько мы профессиональны в кодировании, ошибки во время выполнения могут возникнуть где угодно. Как объяснено выше VBA 1004 Ошибка — это ошибка, которая возникает во время выполнения кода в Excel. Это также называется ошибкой приложения или объекта.

Существуют различные типы причин, по которым мы получаем VBA Runtime Error 1004 в Excel, давайте рассмотрим некоторые из них.

  • Ошибка выполнения VBA 1004: сбой метода «Диапазон» объекта «_ Global»:

Эта ошибка возникает, когда значение диапазона, которое мы ссылаемся на VBA, неверно. Он также называется метод «Диапазон» объекта «_ Global» не удалось.

  • Ошибка выполнения VBA 1004: это имя уже занято.Попробуйте другой:

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

  • Ошибка выполнения VBA 1004: невозможно получить свойство select класса Range:

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

  • Ошибка выполнения VBA 1004: сбой метода «Открыть» объекта «Книги»:

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

  • Ошибка выполнения VBA 1004: извините, мы не смогли найти:

Мы получаем эту ошибку, когда пытаемся открыть лист, который не существует.

Как мы узнали, могут быть разные причины, по которым мы получаем ошибку во время выполнения. Ошибка выполнения может возникнуть в любой строке кода. Нам нужно научиться обрабатывать эти ошибки, и это называется VBA Error Handling.

Пример VBA Runtime Ошибка 1004 в Excel

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

Вы можете скачать этот шаблон Excel с ошибкой VBA 1004 здесь — Шаблон Excel с ошибкой VBA 1004

Ошибка выполнения VBA 1004 — Пример № 1

  • Давайте назовем этот заголовок таблицы как DATA .

  • Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.

  • Вызовите заголовок, который мы назвали следующим кодом, написанным ниже.

  • Когда мы запускаем код, мы видим в Excel, что он был выбран, поскольку мы правильно вызвали заголовок.
  • Теперь мы неправильно написали орфографию имени заголовка.

  • Запустите код еще раз, чтобы увидеть результат.

Мы получаем Excel VBA Runtime Ошибка 1004, потому что мы неправильно написали имя диапазона.

Ошибка выполнения VBA 1004 — Пример № 2

Мы получаем эту ошибку, когда пытаемся переименовать лист с именем, которое уже занято. Например, я переименовал лист 1 в « Ананд », и я постараюсь переименовать лист 2 в тот же, чтобы увидеть результат.

  • Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
  • Объявите подфункцию, чтобы начать писать код.

  • Попробуйте переименовать лист 2 в Anand с помощью следующего кода ниже,

  • Запустите приведенный выше код и посмотрите результат.

Ошибка выполнения VBA 1004 — Пример № 3

Я попытаюсь добавить значение из листа 2 к переменной на листе 3. Но я не буду активировать лист 2 и посмотрю, что произойдет.

  • Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
  • Объявите подфункцию, чтобы начать писать код.

  • Объявите две переменные A и B как целое число.

  • В переменной B сохраните значение A в дополнение к ячейке A1 листа 2.

  • Давайте предположим, что код работает, и используем функцию msgbox для отображения значения B.

  • Запустите код, чтобы увидеть полученный результат.

Мы получаем эту ошибку, потому что мы не активировали лист 2, но мы пытаемся использовать значение листа 2.

Ошибка выполнения VBA 1004 — Пример № 4

Мы сталкиваемся с этой ошибкой во время выполнения, когда у нас уже открыто одно и то же имя рабочей книги, но мы пытаемся открыть ее снова.

В этом примере я уже переименовал свою книгу в VBA 1004 Error.xlsm, и я попытаюсь снова открыть ее, которая уже открыта, и посмотреть, получу ли я ошибку VBA 1004.

  • Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
  • Объявите подфункцию, чтобы начать писать код.

  • Объявите переменную в качестве рабочей книги.

Попробуйте открыть книгу, которую мы уже открыли, с помощью следующего кода.

Запустите приведенный выше код, чтобы увидеть результат.

Мы получаем эту ошибку, потому что мы уже открыли ту же книгу.

Ошибка выполнения VBA 1004 — Пример № 5

Мы получаем эту ошибку, когда пытаемся открыть книгу, которой не существует. Это в некоторой степени похоже на приведенную выше ошибку, поскольку VBA не может найти книгу.

  • Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
  • Объявите подфункцию, чтобы начать писать код.

  • Попробуйте открыть любую книгу с помощью следующего кода,

  • Я уже удалил лист с места.
  • Запустите код, чтобы увидеть результат.

Поскольку лист не существует в данном месте, мы получаем эту ошибку.

То, что нужно запомнить

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

Рекомендуемые статьи

Это было руководство к VBA 1004 Ошибка. Здесь мы обсудили Excel VBA Runtime Error 1004 вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Переход между книгами при активной UserForm VBA

Благодарности принимаются на кошелёк WebMoney: R318574877619 и Яндекс.Деньги: 41001335672216

Точно .
Спасибо большое!- не знал!!
Теперь другая проблема, появился доступ к листам самой базы- а это не хорошо.Я специально поставил на всех формах запрет закрытия по крестику и пароль который убирается со всех листов книги при открытии книги(чтобы не мешал работе макросов) и одновременном запуске главной формы а при закрытии (закрыть можно только с главной) происходит установка пароля на все листы и автозакрытие книги. Таким образом я не давал возможность юзеру попасть на листы этой базы, даже если он в момент запуска отключит макросы можно только полистать листы — пароль снят не будет. Тоесть в Вашем предложенном варианте появляется доступ к листам базы тогда когда уже пароль снят и там можно накуралесить делов тока держись — кладовщицы народ шустрый .
Наверно в таком случае мне надо подумать над другим способом защиты листов базы, чоб макрос работал а ручки нет. А как на счет скрытых листов.
На тулбаре «Элементы управления» есть кнопка «Свойства».
Стоя на листе в свойстве Visible можно установить значение 2, и тогда лист нельзя будет сделать видимым даже при помощи Формат/Лист/Показать (только из редактора VBA)
Тогда может и заморочки с паролями не понядобятся? Спасибо!
Мысль конешно интересная но как это сделать в моём случае?
число листов в базе колеблется в районе 200 и самое интересное их число непрерывно (в процессе работы с базой) изменяется программно, как добавляются так и удаляются, а пользователь видит только первый лист за формой которому я просто поставил зелёный фон и ярлычки от листов которые он не может активировать(поэтому он и не видит что происходит на рабочих листах). Если бы это сделать как с установкой/снятием паролей одновременно на все листы сколько бы их там не было который я использую

Нет ничего проще

If Sh.Index>1 then Sh.Visible=2

. и спрятали всё, кроме первого

Действительно! Что то я протупил.
но теперь другое, иногда программе надо выбрать лист (например перед загрузкой диапазона в комбобокс из 6 колонок), метод селект завершен неверно, наверно надо перед этим на время делать так Sh.Visible=-1 а потом Sh.Visible=2
Я на верном пути ? Или есть другой выход?
Большой спасибо за помощь!

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

В данном случае, многое зависит от того, как Вы заполняете комбобокс.
Если макросом считываете значения с листа, и по-одному записываете в комбобокс, — то отображать лист не требуется.

полностью откажитесь от использования методов Select и Activate в своём коде.
В 95 % случаев их использование не требуется, и только замедляет выполнение кода.

В любом случае, для записи в комбобокс использование метода Select не требуется.

Благодарности принимаются на кошелёк WebMoney: R318574877619 и Яндекс.Деньги: 41001335672216

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

Комментарии

Елена, это уже совсем другой макрос нужен.
Можем сделать под заказ. От 1500 руб.

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

Не знаю что даже сказать. Магия сработала. Спасибо разработчик большое! Здоровья тебе и твоим близким.

Спасибо Дружище. Желаю тебе всего хорошего, чего сам себе желаешь.

Отличный макрос! Спасибо!

Автор, спасибо огромное!

Спасибо ОГРОМНОЕ . Помогло, заменил 267 гиперссылок.

Спасибо автору! Макрос помог!

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

Сработал расширенный макрос,
более 5000 адресов исправлены на коректные
Огромная благодарность
Дай Бог тебе здоровья автор

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

Я спасён!
Огромное спасибо за помощь! Помогли сэкономить огромный вал рабочего времени!

Огромно спасибо. Автоматически исправил более 5 тысяч ссылок в архиве. Все работает. Ну просто нет слов, как я Вам признателен.

СПАСИБО ОГРОМНОЕ ЗА ВАШ РЕСУРС И ДОБРОТУ.
Правда, Ваш макрос у меня не работает почему-то, цель сделать из абсолютных ссылок относительные.но выяснилось, что вполне работает обычное «найти и заменить»
в строку «найти» пишем кусок пути ДО нужной папки, а в «заменить» оставляем пустоту, ставим галку «во всей книге» и жмем «заменить все»

Слетели все ссылки после вставки автосохраненного файла. Почему то данный макрос не работает
Sub ZamenaIsporchennihGiperssilok()
On Error Resume Next
Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet
‘ part of hyperlink, which you want to change
oldString = «C:UsersDianaGAAppDataRoamingMicrosoftExcel»
‘ to what to change
newString = «C:UsersDianaGADekstopÀÁÌ — 2015»
For Each sh In ActiveWorkbook.Worksheets ‘ ïåðåáèðàåì âñå ëèñòû â àêòèâíîé êíèãå
For Each hl In sh.Hyperlinks ‘ ïåðåáèðàåì âñå ãèïåðññûëêè íà ëèñòå
If hl.Address Like «*» & oldString & «*» Then
hl.Address = Replace(hl.Address, oldString, newString)
End If
Next
Next sh
End Sub
Никаких действий не происходит

Тут ничего особенного делать не надо, — Excel сам все гиперссылки в книге подправит, как только вы лист переименуете.

Добрый вечер всем! Выручайте! Как изменить имя листа книги в Excel при этом не потеряв гиперссылки? Заранее благодарю!

Спасибо тебе, добрый человек!

Благодарность не знает границ! Спасибо очень выручили!)))

Автор, огромное вам СПАСИБО. Очень выручили

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

vitamax555, а какое отношение ваш вопрос имеет к теме статьи, — замене гиперссылок?
На вопросы «не в тему» не отвечаю

Доброго дня! Подскажите, пожалуйста, у меня есть экселевский файл и в нем прописан код VBA. Вроде бы работал правильно, но мне нужно было изменить данные, так как не правильно тянул часть данных из исходника. Как только изменил данные и исходник, данные стали грузиться правильно, однако очень долго. Так раньше на это требовалось около 1 минуты, теперь это занимает 10. Что мне делатать. Подскажите, пожалуйста! Спасибо!

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

Здравствуйте, Юрий
Достаточно убрать цикл по листам,
применяя изменения только к активному листу:

Спасибо! Очень выручила данная статья!
Но как всегда возник вопрос — как изменить данный макрос, что бы изменения коснулись только ОДНОГО листа в книге? (Файлов больших несколько, листов в каждом масса, соответственно разбирать файл по листам а потом собирать его обратно — очень долгий и не совсем желаемый процесс. )
Заранее СПАСИБО.

целую твои умелые ручки, автор!

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

Здравствуйте, Владимир
Так не получится, — Excel (тем более, если он не запущен) не отслеживает перемещение файлов и папок.
Да и как Excel узнает, где искать перемещенные файлы. мало ли куда вы их перенесете, вдруг вообще на другой комп.

Надо делать гиперссылки относительными (чтобы путь прописывался относительно файла Excel)?
а папку с файлами держать в той же папке. где сохранён файл Excel, и не переименовывать.
Тогда,если переносите на другой комп файл Excel вместе с папкой, гиперссылки вроде должны сохранять работоспособность.

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

Здравствуйте, Татьяна.
Так вы перенесите файлы на другой комп в ту же папку (чтобы путь к папке не изменился), — и тогда ничего переделывать не придётся.
А если путь к папке изменится, — тогда макрос этот поможет.

Как запустить макрос — объяснять не буду (об этом в интернете много написано, в т.ч. есть видеоинструкции)
В макросе надо будет только задать, что на что менять (в коде указать старый путь к папке, и новый)
Попробуйте, — всё очень просто.
PS: Чтобы вставить макрос в файл, надо нажать сначала Alt + F11, потом Ctrl + R, потом Enter, — и в появившееся большое текстовое поле скопировать код из статьи.

Здравствуйте!
Я в вопросе макросов вообще новичок, вернее никогда с ними не работала, поэтому прошу помощи в следующем вопросе. Я создала файл в EXELе со множеством гиперссылок на разные файлы, эти файлы во многих папках, но эти папки собрала в одну. Теперь мне нужно перенести рабочий файл на другой компьютер. Подскажите, пожалуйста как я могу это сделать не потеряв все гиперссылки, их более 3000. Могу ли я воспользоваться макросом который вы предлагаете? Буду очень признательна за помощь.

Да повыситься многократно твоя карма, уважаемый автор! Спасло!:)

Большое человеческое СПА-СИ-БО!
Ваш макрос здорово меня выручил.

и снова не работает, вот так у меня прописан макрос6

Sub ЗаменаИспорченныхГиперссылок()
On Error Resume Next
Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet
‘ part of hyperlink, which you want to change
oldString = «AppDataLocalLocalSettingsAppDataLocalLocalSettingsTemp»
‘ to what to change
newString = «Ok!»
For Each sh In ActiveWorkbook.Worksheets ‘ перебираем все листы в активной книге
For Each hl In sh.Hyperlinks ‘ перебираем все гиперссылки на листе
If hl.Address Like oldString & «*» Then
hl.Address = Replace(hl.Address, oldString, newString)
End If
Next
Next sh
End Sub

более того при попытке заменить в адресах хотя бы один символ (для проверки работоспособности)например:

‘ part of hyperlink, which you want to change
oldString = «Temp»
‘ to what to change
newString = «Ok!»

Попробуйте менять ЧАСТЬ пути в гиперссылках
(пути в гиперссылках могут быть сохранены не абсолютные, а относительные, — потому замена и не выполняется)

постараюсь более детально описать процесс создания файла:
вставлены именно макросы, вставлены непосредственно т.е. -> правая кнопка -> гиперссылка -> выбор файла из архива где все добро и хранится. Первые 100 ссылок проверил на работоспособность в т.ч. с переносом папки архива на домашний компьютер, убедившись в работоспособности базы данных продолжил, а после обнаружил проблему. Гиперссылки забиты не формулами а вставкой. Найти и заменить не помогло, «не обнаружено совпадений» правильность написания проверил трижды.
Возможно что в ячейках адрес как таковой не прописан, его можно увидеть только подведя курсор к ячейке с гиперссылкой и он отображается в всплывающей подсказке, именно в ней я и увидел несоответствие фактического и прописанного адресов и вот его и нужно изменить, но в строке формулы ячейки адреса не прописаны наверное поэтому «найти и заменить» не сработало.
Макрос так же не сработал. Могу предоставить скриншоту по каждым моим действиям, может ошибка именно в последовательности

А вы уверены, что у вас на листе именно гиперссылки (синие, подчеркнутые), а не формулы, например?

Нажмите Ctrl + H (диалоговое окно поиска и замены),
в поле «Что заменять» введите AppDataLocalLocalSettingsAppDataLocalLocalSettingsTemp
в поле «На что заменять» введите Ok!
и нажмите «Заменить все»

Если это поможет, — то вам нужен другой макрос (попроще, который выполняет замены в ячейках, а не в гиперссылках):
вместо

Доброго дня! Помогите советом, до сего дня макросами не пользовался и в этой области «чайник».
Проблема следующая на рабочем компьютере создаю базу данных типовых серий (вставляю гиперссылки), скидываю по почте на домашний дорабатываю (на этом этапе гиперссылки вовсе не трогаю, добавляю примечания и прочую доп.информацию. ) скидываю обратно ч/з почту, а на работе сюрприз: большинство гиперссылок не работают. При объеме базы в 3500-5000 файлов попробуйте представить тяжесть разочарования и стресса. Выяснил что адрес сменился с
D:Нормативно техническая базаТиповые материалы для проектированияТиповые серииOk!
например было D:Нормативно техническая базаТиповые материалы для проектированияТиповые серииOk!-312 в.0.djvu
стало D:Нормативно техническая базаТиповые материалы для проектированияТиповые серииAppDataLocalLocalSettingsAppDataLocalLocalSettingsTemp-312 в.0.djvu
макрос бы помог да не выходит ничего. Пожалуйста помогите разобраться, вот как я его записал

Sub ZamenaIsporchennihGiperssilok()
On Error Resume Next
Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet
‘ part of hyperlink, which you want to change
oldString = «D:Нормативно техническая базаТиповые материалы для проектированияТиповые серииAppDataLocalLocalSettingsAppDataLocalLocalSettingsTemp»
‘ to what to change
newString = «D:Нормативно техническая базаТиповые материалы для проектированияТиповые серииOk!»
For Each sh In ActiveWorkbook.Worksheets ‘ перебираем все листы в активной книге
For Each hl In sh.Hyperlinks ‘ перебираем все гиперссылки на листе
If hl.Address Like oldString & «*» Then
hl.Address = Replace(hl.Address, oldString, newString)
End If
Next
Next sh
End Sub

Однако положительных признаков работы не подал.

Ну можно «выделить» так строки — вот только нужно ли?
Если вы не знаете, зачем это «выделение» — зачем его делаете?
Это точки останова макроса. Оно вам надо, чтобы макрос останавливался на каждой строке?

Код один раз сработал — значит, проблема точно не в нём.
Ищите проблему в соответствии пути гиперссылок, и текст для замены в макросе.
И убирайте все красные точки (и не ставьте их впредь)

Скажите пожалуйста — скрипт сработал лишь раз. Затем почему то перестал работать. Я зашел в «Войти» в Макросах и там можно выделить все строчки красным (красная точка и красная линия), кроме Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet. На нее так же нельзя перетащить стрелку дебага. То есть я так понял скрипт стопорится на ней. Хотя 1 раз скрипт точно сработал. Я сохранил документ, затем его немного переместили, я изменил скрипт снова как надо (то есть места папок назначения) и он перестал работать.

Огромное спасибо! Очень помогло! Можно сказать, спасло от кары босса )))!

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

то есть
. создать ссылки на сайты.

>> В меню «Изменение Гиперссылки» слева есть возможность выбора:
это для того, чтобы можно было мышкой создать ссылку. а так, эти кнопки особой роли не играют.
можно при помощи этих кнопок создать ссылки на файлы, затем запустить скрипт по изменению ссылок, поменять например «http://ya.ru» на «helloworld.txt» и при следующем открытии меню «Изменение Гиперссылки» слева будет нажата другая кнопочка

В меню «Изменение Гиперссылки» слева есть возможность выбора:
Связать с:
— файлом, веб-страницей;
— местом в документе;
— новым документом;
— электронной почтой.
Ты не ошибся.

а по-моему все ссылки одинаковые. она становится гипер или обычной просто из-за содержимого ссылки. например если начинается на «http://. » значит гипер, а если «C:. » или «\192.168.1.1. » значит локальная или сетевая там.

как сделать чтобы менял обычные ссылки а не гипер ?

Вроде бы формат файла не изменился (xls) 97-2003, как был так и остался (не у всех, просматривающих, таблицу есть новый (xlsx))

Источник

Источник

Понравилась статья? Поделить с друзьями:
  • Vba access обработка ошибок
  • Vb6 on error resume next
  • Vb6 on error goto
  • Vb6 error accessing system registry
  • Vb net on error resume next