Эксель макрос замены текста

Эксель макрос замены текста

размещено: 14 Июля 2016
обновлено: 15 Июля 2016

Если в Ваших DWG файлах используются блоки с одинаковой текстовой информацией, например, даты, заголовки и т.д. и данный текст расположен во многих местах Ваших чертежей, Вы можете автоматизировать замену текста в DWG файле-шаблоне до состояния редактируемого файла. Особенно удобно использовать данный файл Excel с макросом, если количество текстовых блоков составляет десятки штук, и ручная замена отнимет у Вас значительное время для редактирования. Для быстрой замены текста в блоках Вы можете использовать NanoCAD Free или AutoCAD.

Для пользователей NanoCAD:
1. Скачайте архив с файлом макроса “Replace_Text_In_Blocks_Excel_NanoCAD.xlsm”.
2. После открытия файла Excel нажмите “ALT+F11”, откроется редактор Microsoft Visual Basic for Application, выберите в меню “Tools — References”, и добавьте в список галочки:
“nanoCAD Type Library (NCAuto.dll)”
“OdaX Type Library (OdaX_csd.dll)”,
нажмите OK.
Для пользователей AutoCAD:
После открытия файла Excel нажмите “ALT+F11”, откроется редактор Microsoft Visual Basic for Application, выберите в меню “Tools — References”, и добавьте в список галочки:
“AutoCAD YYYY Type Library” (acaxXXenu.tlb),
где YYYY – год Вашей версии AutoCAD, XX – 21, 20, 19,…(номер версии);
“AutoCAD Focus Control for VBA Type Library” (AcFocusCtrlXX.dll);
“AXDBLib” (axdbXXenu.tlb),
3. Закройте редактор Microsoft Visual Basic for Application.
4. Включите поддержку макросов в Excel
https://support.office.com/ru-ru/article/Включение-и-отключение-макросов-в-документах-Office-7b4fdd2e-174f-47e2-9611-9efe4f860b12
5. Скопируйте все Ваши DWG файлы в ту же папку, что и файл с макросом Excel.
6. Нажмите “ALT+F8” для запуска макроса “Replace_Text_In_Blocks_Excel_NanoCAD” или “Replace_Text_In_Blocks_Excel_AutoCAD”. В этих файлах Ваше CAD приложение заменит текст в блоках в соответствии с таблицей данных в файле макроса, закроет файлы и сохранит их.

Возможности макроса и ограничения:
а. Максимальное число DWG файлов – 1000.
б. Максимальное число различных блоков для замены/вставки текста — 65530.
c. Ячейка “Block Name” должна содержать имя блока DWG файла (например, DATASHEET_TITLE, DATE, DESCRIPTION для файла “Stepping Motor SH4009l1206 Blocks.dwg” в ZIP архиве). Значение “Block Name” может быть типа A$C26677C24, и т.д.
г. Ячейка “Data Type” может принимать 2 значения: “FullText” или “InternalText”.
Для значения «FullText» макрос заменить текст полностью в блоках, которые состоят из примитивов Текст/МТекст.
Для значения “InternalText” макрос вставит новый текст внутри между текстовыми сегментами, заданными как “Text Before Insert” и “Text After Insert” во всех блоках на чертеже с указанным именем в ячейке “Block Name”. Если очистить ячейку “Block Name” для строки со значением “InternalText” макрос вставит текст во всех примитивах Текст/МТекст на чертеже (не в блоках), если найдет в них ключи “Text Before Insert” и “Text After Insert”.
д. Ячейка “Value To Replace/Insert” должна содержать текстовое значение для замены/вставки текста для блоков или примитивов Текст/МТекст.
е. Ячейка “Font Height” содержит значение высоты текста – при работе макроса высота всего замененного текста будет изменена на указанное значение. Формат данной ячейки – Число, используйте разделитель целой и дробной частей в соответствии с данными настройками Excel.
https://support.office.com/ru-ru/article/Изменение-знака-используемого-для-разделения-целой-и-дробной-частей-или-разрядов-c093b545-71cb-4903-b205-aebb9837bd1e?ui=ru-RU&rs=ru-RU&ad=RU

Код макроса открытый, доступен по “ALT+F11”

Надстройка предназначена для выполнения множественных замен в выделенном диапазоне ячеек.

После запуска надстройки, появляется панель инструментов из 3 кнопок:

  • Кнопка «Выполнить все замены» — выполняет замены в выделенном диапазоне ячеек
  • Кнопка «Изменить список замен» — отображает лист настроек, где в первом столбце находится список заменяемых значений, а во втором — текст, на который заменяем
  • Кнопка «Сохранить изменения» — скрывает лист настроек, сохраняя изменения в списке замен

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

Перед нажатием кнопки, необходимо выделить диапазон ячеек для замены (не менее 2 ячеек)

PS: Позже надстройка будет доработана, и приобретет новые возможности.

Вложение Размер Загрузки Последняя загрузка
Replacements.xla 75.5 КБ 87 2 года 5 недель назад
  • 36280 просмотров
Читайте также:  Самые лучшие игры выживалки

Комментарии

ОГРОМНОЕ СПАСИБО за эту великолепную надстройку!
Помогла в решение проблемы с переносом документов между разными конфигурациями 1С 8.1 без привлечения программистов (замена GUID).
=))))

Здравствуйте, Илья
2000 руб будет стоить. Замены будут выполняться очень быстро (единицы секунд)

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

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

Не тащит 16 тыс. строк, и, почему-то, меняет только 5% по всему столбцу.

Добрый день, Владислав!

Вы сначала получаете строку "Большой успех", а потом второе слово в ней меняется на "малый успех", вот и получается "Большой малый успех".

И даже если бы регистр учитывался, Вам нужно поменять местами 1 и 2 строку замен, иначе результат всегда будет получаться "Большой малый успех"

Если нужно, чтобы макрос учитывал регистр, то пропишите в нем вместо Option Compare Text строку Option Compare Binary (а если там нет Option Compare Text, то просто Option Compare Binary в декларациях напишите).

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

Что заменить | На что заменить

Успех | Большой успех
успех | малый успех

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

Когда-то пользовался данной надстройкой без проблем. После очередного запуска Excel, появилось предупреждение о том, что надстройка не может быть запущена (текст касался проблемы безопасности, я естественно расстроился, потому что даже в таком виде она была полезна). Я решил скачать эту надстройку ещё раз, расположил на другом диске, опять подключил и как ни странно, она опять заработала ! Но в отличие от первой установки (когда я не замечал ошибок работы), сейчас замены происходят с ошибкой (!), что меня неприятно удивило.
Насколько я понял, замена происходит жестко по 100 % совпадению, учитываются пробелы, что концептуально правильно. Естественно, что я ожидаю именно такой результат.
Что произошло у меня. Например, была строчка в Excel Набор для вышивания Риолис 1335 "Акварельные розы", задача состояла в том, чтобы удалить в большом количестве строк слова "Набор для вышивания", что сделала надстройка.
В части строк, она справилась и удалила слова, после чего строка выглядела так код "Название товара",а в некоторых строках почему-то при удалении искомого значения, удаляет КОД (. ), хотя в словаре замен я его не указываю !

Предположим, что нам необходимо заменить все "ул." на листе на "улица ". Или русское слово "дом" на английское "house". Или еще интереснее: все английские буквы на русские. Например, английская "а" должна быть заменена на русскую "a", английская "c" на русскую "с", английская "H" на русскую "Н" и т.д. А такое тоже нередко бывает и доставляет проблемы. Ведь если в одной таблице будут русские буквы, а в другой английские — то применение большинства встроенных функций поиска(та же ВПР) просто не найдут соответствия.
Если подобную замену надо сделать для одного сочетания, то все просто: жмем Ctrl+H и указываем что заменить и на что. Но если таких замен надо сделать 20? Или 120? Это надо будет 120 раз нажать и ввести что заменять и на что. А если это надо сделать еще и не в одном документе — то. Думаю сами справитесь с умножением количества замен на количество файлов, в которых это надо сделать. И вроде бы простая операция превращается в ваш личный ад на работе.
Недавно на форуме участнику потребовалось автоматизировать именно такую штуку. Т.к. код несложный — решил написать и чуть дополнив выложить для всех кому код может потребоваться:

Читайте также:  Телефон самсунг j2 прайм отзывы

Option Explicit Sub Replace_Mass() Dim s As String Dim lCol As Long Dim avArr, lr As Long Dim lLastR As Long Dim lToFindCol As Long, lToReplaceCol As Long, lLookAt As Long ‘запрашиваем направление перевода — с русского на англ. или наоборот lCol = Val(InputBox("Укажите направление перевода:" & vbNewLine & _ " 1 — ru-en" & vbNewLine & _ " 2 — en-ru", "Запрос", 1)) If lCol = 0 Then Exit Sub ‘запрашиваем по части ячейки искать или по всему тексту ‘по умолчанию — по части lLookAt = Val(InputBox("Искать соответствие по части ячейки или по всему тексту:" & vbNewLine & _ " 1 — по всему тексту" & vbNewLine & _ " 2 — по части ячейки", "Запрос", 2)) If lLookAt = 0 Then Exit Sub Select Case lCol Case 1 lToFindCol = 1 lToReplaceCol = 2 Case 2 lToFindCol = 2 lToReplaceCol = 1 End Select Application.ScreenUpdating = 0 ‘Получаем с листа Соответствия значения, которые надо заменить в выделенном диапазоне With ThisWorkbook.Sheets("Соответствия") lLastR = .Cells(.Rows.Count, 1).End(xlUp).Row avArr = .Cells(1, 1).Resize(lLastR, 2) End With ‘заменяем For lr = 1 To UBound(avArr, 1) s = avArr(lr, lToFindCol) If Len(s) Then ‘если значение для замены не пустое Selection.Replace s, avArr(lr, lToReplaceCol), lLookAt End If Next lr Application.ScreenUpdating = 1 End Sub

Как это работает. В книге есть специальный лист с именем "Соответствия". На нем в столбце А записаны слова, которые необходимо заменить, а в столбце В — на что эти слова заменить. Если в столбце А пусто — то замена не будет произведена. Если в столбце В пусто — то значение из столбца А будет просто удалено.
Замены производятся исключительно в выделенных на листе ячейках. Ячейки могут быть несмежными.

Итак, необходимо сделать много замен. Скачиваете файл:

Массовая замена слов (54,5 KiB, 6 713 скачиваний)

Примечание: Я сделал файл как переводчик. Т.е. в первом столбце русские слова, во втором английские. Но в столбцах может быть что угодно — хоть слова, хоть символы, хоть числа.
На лист "Соответствия" записываете в столбец А — что заменять, в столбец В — на что заменять. Переходите на лист книги, в котором необходимо произвести замену. Выделяете ячейки, значения в которых надо найти и заменить. После чего жмете Alt+F8 и выбираете макрос "Tips_Macro_ReplaceMASS.xls!Replace_Mass"(или просто "Replace_Mass", если код в той же книге, что и ячейки для поиска и замены).
Первым появится окно с запросом направления перевода. По умолчанию 1(ru-en). Т.е. будет браться слово из столбца А и заменяться словом из столбца В. Но если указать 2 — то будет браться слово из столбца В и заменяться словом из столбца А. Т.е. аналог переводчика — с рус. на англ. и наоборот. Либо из А в В, либо из В в А.
Вторым появится запрос на метод просмотра данных:

  • если указать "1 — по всему тексту" — данные из столбца А будут заменять только в том случае, если ячейка в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа "Соответствия". Например, в любой из выделенных ячеек записано "На столе книга", а на листе "Соответствия" в столбце А есть только слово "книга". Замена не будет произведена, т.к. необходимо, чтобы в столбце А было так же "На столе книга".
  • если указать "2 — по части ячейки" — данные из столбца А будут заменять в случае, если ячейка в выделенном для замены диапазоне содержит любое слово из столбца А листа "Соответствия". На том же примере — "На столе книга". Если выбрать 2, то в тексте "На столе книга" слово книга будет заменено на слово из столбца В — "book".

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

Как видно, здесь присутствую только номера статей, но нет их расшифровки. Зато расшифровка есть в отдельном листе "Справочник":

Как видно, в справочнике присутствуют нужные номера статей и можно было бы применить ту же ВПР(VLOOKUP) для замен. Если бы не одно но: в таблице по реализации помимо номеров статьей есть еще лишний текст "Статья затрат:". Конечно, можно сначала заменить этот текст, потом в отдельном столбце применить ВПР, заменить формулу значениями и вернуть в исходный столбец. Если при этом надо еще оставить текст "Статья затрат:", то надо будет сделать еще доп.манипуляции либо при составлении формулы, либо после. В любом случае — слишком много лишних телодвижений. А значит бОльшие времязатраты.
Приложенный ниже файл поможет сделать это в разы быстрее:
Скачать файл с примером и кодом:

Читайте также:  Необычные лаунчеры для android

Массовая замена слов — статьи.xls (91,5 KiB, 1 569 скачиваний)

и в итоге за пару секунд получим следующий результат:

Достаточно выделить столбец со статьями на листе с реализацией и запустить код(либо нажатием кнопки заменить значения, либо нажав Alt+F8 и выбрав из списка макросов макрос Replace_Mass ).
После нажатия на кнопку будут запрошены следующие параметры:

  1. указать номер столбца значений в листе "Справочник", в котором искать соответствия номерам статей(в нашем случае это столбец 1(А))
  2. указать номер столбца, значениями которого заменять найденные в таблице реализации значения(это может быть один из трех столбцов справочника: Группа затрат, Статья затрат, Подстатьи затрат). Логичнее всего указать столбец 4, т.к. он наиболее детализирован и конкретнее указывает расшифровку статьи
  3. далее будет предложено указать точность поиска:
    • если указать " 1 — по всему тексту " — данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа "Справочник". Т.е. если бы у нас в таблице реализации был бы записан только номер статьи(1.01), тогда можно было бы указать именно 1
    • если указать " 2 — по части ячейки " — данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне содержит любое значение из столбца А листа "Справочник". Это больше подходит к описанному случаю, т.к. нам необходимо заменить исключительно номер статьей на их расшифровку, оставив при этом текст "Статья затрат: "

    Если все указано корректно, то на листе будут произведены все необходимые замены.
    Возможные ошибки, которые предусмотрены кодом и о которых будет сообщено соответствующим сообщением(код прервется, замены не будут произведены):

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

    Особое внимание хочу уделить случаю, когда выбирается замена по части ячейки. В этом случае лучше список на листе Справочник отсортировать по длине текста по тому столбцу, в котором будут значения для поиска. Зачем это надо: т.к. значение по части ячейки будет заменять не полное соответствие, то есть вероятность неверных замен. Например, есть текст "Статья затрат: 1.011" . В то же время на листе Справочник есть статьи "1.01" и "1.011" . Т.к. "1.01" идет раньше в большинстве случаев, то текст будет заменен некорректно: "Статья затрат: ТВ 1 " .
    Чтобы получить длину строки текста можно использовать функцию ДЛСТР(LEN):
    =ДЛСТР( A2 )
    =LEN(A2)

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

    Статья помогла? Поделись ссылкой с друзьями!

    Ссылка на основную публикацию
    Шарик равноускоренно скатывается по наклонной плоскости
    За каждую секунду, путь пройденный шариком,увеличивается на 20см. Следовательно за 4 секунду он пройдет 70см. Ответ:(2) Если ответ по предмету...
    Что такое ogg формат
    Ogg — Dateiendung: .ogg, .oga, .ogv, .ogx MIME Type … Deutsch Wikipedia .ogg — Dateiendung .ogg, .oga, .ogv, .ogx MIME...
    Что такое pppoe соединение на роутере
    PPPoE (англ. Point-to-point protocol over Ethernet ) — сетевой протокол канального уровня (второй уровень сетевой модели OSI) передачи кадров PPP...
    Шарнирная стойка для дрели
    Стойка для дрели с тисками FIT 37861 Стойка для дрели Калибр 96203 Стойка для дрели RedVerg DS-43 Стойка для дрели...
    Adblock detector