0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как вернуть имя файла без расширения в ячейку в Excel?

Получить список имен файлов из папки в Excel (с VBA и без)

Получить список имен файлов из папки в Excel (с VBA и без)

В свой первый день работы в небольшой консалтинговой фирме я работал над коротким трехдневным проектом.

Работа была простая.

В каждой из папок сетевого диска были сотни файлов.

Мне пришлось пройти через эти три стадии:

  1. Выберите файл и скопируйте его имя.
  2. Вставьте это имя в ячейку Excel и нажмите Enter.
  3. Перейдите к следующему файлу и повторите шаги 1 и 2.

Все очень просто, не так ли?

Это было простой и большой потерей времени.

Несколько минут могли бы сделать то, на что у меня ушло 3 дня.

В этом учебнике я покажу вам различные способы сделать весь процесс очень быстрым и легким (с использованием VBA и без него).

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

Использование функции FILES для получения списка имен файлов из папки

Что такое FAILS? Знаете ли вы, что он делает?

Не волнуйтесь, если это не тот случай.

Это происходит из детства таблицы Excel (формула версии 4).

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

Представьте, что у вас на рабочем столе есть папка с именем ‘Test Folder’, и вы хотите просмотреть список всех файлов в ней.

Вот шаги, которые необходимо выполнить, чтобы извлечь имена файлов из этой папки:

  1. В ячейке A1 введите полный адрес папки со знаком звездочки (*)
    • Например, если ваша папка на диске C, то адрес будет выглядеть как
      C: Users Sumit Desktop Test Folder *
    • Если вы не знаете, как получить адрес папки, используйте следующий метод:
      • В папке, имена файлов которой вы хотите получить, создайте новую рабочую книгу Excel или откройте существующую рабочую книгу в этой папке и используйте следующую формулу в любой ячейке. Эта формула даст вам адрес папки и добавит звездочку (*) в конце. Теперь вы можете скопировать и вставить (вставить как значение) этот адрес в любую ячейку (A1 в данном примере) в рабочей книге, где вы хотите указать имена файлов. [Если вы создали новую книгу в папке, чтобы использовать приведенную выше формулу для получения адреса папки, вы можете удалить ее, чтобы она не появилась в списке файлов в этой папке].
      • В папке, имена файлов которой вы хотите получить, создайте новую рабочую книгу Excel или откройте существующую рабочую книгу в этой папке и используйте приведенную ниже формулу в любой ячейке. Эта формула даст вам адрес папки и добавит звездочку (*) в конце. Теперь вы можете скопировать и вставить (вставить как значение) этот адрес в любую ячейку (A1 в данном примере) в рабочей книге, где вы хотите указать имена файлов. [Если вы создали новую книгу в папке, чтобы использовать приведенную выше формулу для получения адреса папки, вы можете удалить ее, чтобы она не появилась в списке файлов в этой папке].
  2. Перейдите на вкладку «Формулы» и нажмите «Определить имя».
  3. В диалоговом окне «Новое имя» используйте следующие данные.
    • Имя: FileNameList (не стесняйтесь выбирать любое имя, которое вам нравится)
    • Объем: Рабочая тетрадь
    • Относится к: = ФАЙЛЫ (Sheet1! $ A $ 1)
  4. Теперь, чтобы получить список файлов, мы будем использовать именованный диапазон в функции ИНДЕКС. Перейдите в ячейку A3 (или любую ячейку, с которой должен начинаться список имен) и введите следующую формулу:
  5. Перетащите его вниз, и вы получите список всех имен файлов в папке.
Читайте так же:
Как быстро преобразовать или скопировать столбец адресов электронной почты в Excel в список рассылки Outlook?

Вы хотите извлечь файлы с определенным расширением?

Если вам нужны все файлы с определенным расширением, просто замените звездочку на это расширение. Например, если вам нужны только файлы Excel, вы можете использовать * xls* вместо *

Таким образом, адрес папки, которую следует использовать, — C: Users Sumit Desktop Test Folder * xls.

Аналогично, для файлов документов Word используйте * doc.

Как он работает?

В указанной папке FILES извлекает имена всех файлов с указанным расширением.

Для формулы INDEX мы задали имена файлов в виде массива и вернули первое, второе и третье имена в массиве. и т.д. Использование функции STRING.

Обратите внимание, что я использовал ROW () — 2, потому что мы начинаем с третьего ряда. Таким образом, ROW () — 2 будет 1 для первого случая, 2 для второго случая, когда номер строки равен 4, и так далее.

Смотреть видео — получение списка имен файлов из папки в Excel

Использование VBA для получения списка всех имен файлов из папки

Должен признать, что описанный выше метод немного сложный (с несколькими шагами).

Но даже в этом случае это намного лучше, чем делать это вручную.

Но если вы хорошо владеете VBA (или если вы знаете, как следовать точным шагам, которые я перечислю ниже), вы можете создать настраиваемую функцию (UDF), которая может легко получить все имена файлов.

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

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

Читайте так же:
Как в Excel создать гиперссылку на конкретную папку?

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

Где я должен разместить этот код?

Шаги по копированию этого кода в редакторе VB.

  • Перейдите на вкладку Разработчик.
  • Щелкните кнопку Visual Basic. Это откроет редактор VB.
  • В редакторе VB щелкните правой кнопкой мыши любой из объектов книги, с которой вы работаете, выберите «Вставить» и нажмите «Модуль». Если вы не видите Project Explorer, используйте сочетание клавиш Control + R (удерживайте клавишу управления и нажмите клавишу «R»).
  • Дважды щелкните объект Module, скопируйте и вставьте приведенный выше код в окно кода модуля.

Как эта функция используется?

Ниже описаны шаги для использования этой функции на листе:

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

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

В приведенной выше формуле ROW() + 2 вычитается из третьей строки и далее. Копирование этой формулы в ячейки ниже обеспечит ее увеличение на 1. Если вы вводите формулу в первой строке столбца, можно просто использовать ROW().

Как работает эта формула?

Формула GetFileNames возвращает массив, содержащий имена всех файлов в заданном каталоге.

Функция ИНДЕКС используется для указания имени файла в каждой ячейке, начиная с первой.

При использовании функции IFRESTRICT, #RELATED будет возвращена пустой! ошибка, которая отображается, когда формула копируется в ячейку, но имена файлов для перечисления отсутствуют.

Читайте так же:
Динамическое выделение точки данных на диаграмме Excel

Использование VBA для получения списка всех имен файлов с определенным расширением

Приведенная выше формула Excel отлично подходит, если вам нужен список имен всех файлов в определенной папке.

Как насчет получения только имен видеофайлов, или только файлов Excel, или только имен файлов, содержащих определенное ключевое слово?

В этом случае можно использовать немного другую функцию.

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

Приведенный выше код создаст функцию ‘GetFileNamesbyExt’, которую можно использовать в таблицах (так же, как и обычные функции).

Он принимает два аргумента: местоположение и расширение. Вы получите массив имен файлов для заданного расширения. Если ключевое слово или расширение не указано, то возвращаются все файлы в указанной папке.

Синтаксис: = GetFileNamesbyExt («Расположение папки», «Расширение»)

Где я должен разместить код?

Данный код можно скопировать в редактор VB, выполнив следующие шаги.

  • Перейдите на вкладку Разработчик.
  • Нажмите на кнопку Visual Basic. Откроется редактор VB.
  • В редакторе VB щелкните правой кнопкой мыши на одном из объектов рабочей книги, с которым вы работаете, выберите «Вставить» и нажмите «Модуль». Если вы не видите Project Explorer, используйте комбинацию клавиш Control + R (удерживайте клавишу Control и нажмите клавишу «R»).
  • Дважды щелкните на объекте Module, скопируйте и вставьте приведенный выше код в окно кода модуля.

Как используется данная функция?

Вот шаги по использованию этой функции в электронной таблице:

  • В любой ячейке введите адрес папки, из которой нужно отобразить имена файлов. Я напечатал это в ячейке A1.
  • В любой ячейке введите расширение (или ключевое слово), для которого вы хотите отобразить все имена файлов.
  • В ячейке, где должен появиться список, введите следующую формулу (я ввел ее в ячейку A3):
  • Скопируйте и вставьте формулу в ячейки ниже, чтобы получить список всех файлов.
Читайте так же:
Как автоматически пронумеровать объединенные ячейки в Excel?

А как насчет вас? Как вы облегчаете жизнь с помощью Excel? Я бы с удовольствием поучился у вас. Поделитесь в комментариях!

Сообщение об ошибке при открытии или сохранении файла в Microsoft Excel: «Недопустимое имя файла» (эта ссылка может указывать на содержимое полностью или частично на английском языке)

Введенный маршрут «» слишком длинный. Выберите более короткий маршрут.

Сообщение об ошибке 4

Не удалось найти . Убедитесь, что имя файла и его местоположение написаны правильно.

Сообщение 5 в Microsoft Office Excel 2007:

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

Чтобы проверить сообщение об ошибке, полученное в Excel 2007, нажмите CTRL + SHIFT + I. В правом нижнем углу диалогового окна сообщения об ошибке появится следующее число:

Предупреждение. Microsoft Office 2000 имеет встроенную функцию для предоставления дополнительной информации о предупреждениях и сообщениях об ошибках, связанных с проблемами.

Примечание. Сообщение об ошибке подскажет вам, если вы уже включили пользовательские оповещения, если вы нажали кнопку » web info «.

Причина

Ошибка появлялась, если путь к файлу (включая имя файла) превышал 218 символов. Имя диска должно содержать три символа. Имена папок должны содержать символ обратной косой черты между папками, а имена файлов должны содержать три символа. Это связано с ограничением Excel в 256 символов для ссылок на другой файл. Ограничение в 218 символов для имени пути определяется следующей информацией:

Название вида: до 31 символа.

Апостроф и квадратная скобка в названии книги.

Ссылка на клетку.

Читайте так же:
Как быстро поменять местами содержимое двух ячеек в Excel?

Предположим, что путь к файлу пересчитан как ‘c:excelpersonal’. [my workbook.xls]up_to_31_char_sheetname’!$A$1 Примечание. То же самое верно, если в пути присутствуют квадратные скобки.

Что попробовать предпринять

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

Переименуйте файл, чтобы сделать его более коротким.

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

Файлы следует перемещать в папки с более короткими именами.

Дополнительная информация

Если ввести 255 символов в поле Имя файла в диалоговом окне Сохранить как, а затем нажать OK, появится следующее сообщение об ошибке:

Введенный маршрут «<маршрут>» слишком длинный. Выберите более короткий маршрут.

Кроме того, если вы пытаетесь сохранить файл и путь превышает 255 символов, отображается следующее сообщение об ошибке:

Файл не удалось открыть. Попробуйте выполнить одно из следующих действий: проверьте, существует ли указанная папка; проверьте, что папка, содержащая файл, не доступна только для чтения; проверьте, что имя файла не содержит ни одного из следующих символов: < > ? [Убедитесь, что имя файла или путь к нему не превышает 218 символов.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector