Как вставить путь к файлу в Excel

Обновлено: 20.11.2024

Марго хочет вставить путь и имя файла на лист Excel. Она хочет иметь возможность вставлять информацию либо в ячейку, либо в верхний/нижний колонтитул. Это довольно легко сделать в Excel.

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

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

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

Функция LEFT удаляет все символы от правой скобки до конца строки, а функция SUBSTITUTE удаляет левую скобку.

Поместить путь и имя файла в верхний или нижний колонтитул очень просто:

  1. Выберите лист, верхний или нижний колонтитул которого вы хотите изменить.
  2. Выберите параметр «Параметры страницы» в меню «Файл». Excel отобразит диалоговое окно «Параметры страницы».
  3. Нажимайте кнопки «Пользовательский верхний колонтитул» или «Пользовательский нижний колонтитул» по своему усмотрению. Word отображает диалоговое окно «Верхний колонтитул» или «Нижний колонтитул». (См. рис. 1.)

Рисунок 1. Диалоговое окно "Заголовок".

При печати рабочего листа Excel заменяет коды на шаге 5 путевым именем и именем файла рабочей книги соответственно.

Если вы используете Excel 97 или Excel 2000, описанные выше шаги не будут работать. Вместо этого вам нужно использовать макрос для вставки пути и имени файла:

Этот макрос будет работать и в более поздних версиях Excel. Чтобы указать другое место для пути и имени файла, просто измените CenterFooter на другое место (например, LeftFooter, RightFooter, LeftHeader, CenterHeader или RightHeader). Если вы решите использовать подход макросов, вам нужно будет не забывать запускать его каждый раз, когда вы меняете либо имя файла книги (вы используете Сохранить как), либо изменяете место, где книга хранится на вашем диске.

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

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

В Excel нет функции для прямого получения пути и имени файла, но функция CELL возвращает путь к файлу, имя и лист. Используя текстовые функции НАЙТИ, ВЛЕВО и ПОДСТАВИТЬ, мы можем изолировать путь и имя файла.

= ПОДСТАВИТЬ (ЛЕВАЯ (ЯЧЕЙКА ("имя файла", B2), НАЙТИ ("]", ЯЧЕЙКА ("имя файла", B2)) - 1), "[", "")

Давайте пройдемся по формуле.

Имя файла, путь и рабочий лист

Мы используем функцию CELL, чтобы вернуть путь к файлу, имя и лист, введя «имя файла» в качестве типа информации.

НАЙТИ позицию имени файла

Как показано выше, функция CELL возвращает путь к файлу, имя и рабочий лист. Нам не нужен рабочий лист или квадратные скобки, поэтому мы используем функцию НАЙТИ, чтобы определить положение последнего символа (т. е. того, который стоит перед «]») имени файла.

Удалить имя рабочего листа

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

Функция ПОДСТАВИТЬ

Выше вы можете видеть, что между путем и именами файлов все еще есть открытая квадратная скобка. Используйте функцию ПОДСТАВИТЬ, чтобы заменить «[» пустой строкой.

Объединение этих шагов в одну формулу дает нам:

= ПОДСТАВИТЬ (ЛЕВАЯ (ЯЧЕЙКА ("имя файла", B2), НАЙТИ ("]", ЯЧЕЙКА ("имя файла", B2)) - 1), "[", "")

Получить только путь

Возможно, вы захотите показать только путь, без имени файла. Для этого мы можем остановиться на ЛЕВОЙ функции с небольшой настройкой. Нет необходимости ЗАМЕНЯТЬ, так как не будет символов в середине строки для удаления. Чтобы вернуть только путь, мы находим позицию первого символа имени файла («[“) вместо последнего, а имя пути — все, что слева.

Практический лист Excel

Попрактикуйтесь в функциях и формулах Excel с помощью наших 100 % бесплатных тренировочных листов!

В этом посте я покажу вам ответ на вопрос, который кто-то недавно прислал мне.

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

Хорошо, позвольте мне показать вам решение, которое я придумал.

Скачать файл примера

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


Скачать файл: 0052 Вставьте путь к файлу в ячейку с помощью кнопки «Обзор».zip

Посмотреть видео

Решение

Давайте посмотрим на решение в действии.

Ячейка C3 содержит путь к файлу со значком папки рядом с ним.

Когда мы нажимаем на значок папки, открывается диалоговое окно открытия файла. Затем мы можем выбрать файл и нажать «Открыть».

Новый путь к файлу теперь вставлен в ячейку C3.

Если вы хотите узнать, как это сделать самостоятельно, то вы попали по адресу 🙂

Создание решения

Теперь мы знаем, что он делает; давайте построим его.

Создать именованный диапазон

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

  1. Выберите ячейку, содержащую путь к файлу.
  2. В поле имени введите "filePath" и нажмите Enter

    3. Первый шаг. Готово.

    Макрос VBA

    Основной движущей силой этого решения является макрос VBA.

    Если у вас отображается лента "Разработчик", выберите "Разработчик" > Visual Basic, а если нет, нажмите ALT + F11. Откроется редактор Visual Basic.

    Щелкните правой кнопкой мыши файл в окне "Проект", затем выберите "Вставить" > "Модуль" в меню.

    Откроется новый модуль кода. Введите следующий код в модуль кода

    Комментарии в коде описывают назначение каждого раздела.

    Обратите внимание на следующее, так как вам потребуется изменить код для вашего конкретного сценария.

    ActiveSheet.Range("filePath").Value = dialogBox.SelectedItems(1)
    Измените слово "filePath" для именованного диапазона, который вы создали выше.

    dialogBox.InitialFileName = "C:\Users\marks\Downloads\Example Folder"
    Содержит путь к файлу по умолчанию, где папка будет открываться каждый раз. Измените это в соответствии с вашей средой.

    dialogBox.Filters.Clear
    dialogBox.Filters.Add «Книги Excel», «*.xlsx;*.xls;*.xlsm»
    Этот код ограничивает типы отображаемых файлов. Звездочка ( * ) является подстановочным знаком; любые файлы, заканчивающиеся на .xlsx, .xlsx и .xlsm будут отображаться. Чтобы добавить новые типы файлов, например PDF, добавьте “;*.pdf” в текстовую строку.

    После ввода кода закройте редактор Visual Basic, щелкнув значок [X] в правом верхнем углу окна.

    Вставить изображение папки

    Теперь нам нужно что-то, что будет действовать как кнопка, которую нужно нажать. Я собираюсь использовать значок. В зависимости от версии Excel у вас может не быть значков. Но вы можете вставить любую картинку или фигуру по своему желанию.

    Чтобы вставить значок, похожий на меня, нажмите Вставка > Значки.

    Найдите Папка в окне значков. Выберите нужный значок и нажмите «Вставить».

    Теперь изображение будет вставлено на лицевую сторону рабочего листа.

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

    Назначить макрос картинке

    Наконец, мы назначаем макрос изображению. Щелкните изображение правой кнопкой мыши и выберите в меню Назначить макрос….

    В окне "Назначить макрос" нажмите макрос selectFile (это название макроса, который мы создали ранее), затем нажмите "ОК".

    Протестируйте

    Вот и все. Были сделаны. Теперь иди и испытай это.
    Получите БЕСПЛАТНУЮ электронную книгу VBA с 30 наиболее полезными макросами Excel VBA.

    Автоматизируйте Excel, чтобы сэкономить время и перестать выполнять работу, которую могла бы выполнять обученная обезьяна.

    Не забывайте:

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

    Вам нужна помощь в адаптации этого к вашим потребностям?

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

    1. Читайте другие блоги или смотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, найдя собственные решения.
    2. Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
    3. Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
    4. Используйте Excel Rescue, моего партнера-консультанта. Они помогают решить небольшие проблемы с Excel.

    Что дальше?
    Пока не уходите, в Excel Off The Grid есть чему поучиться. Ознакомьтесь с последними сообщениями:

    3 мысли о «Вставить путь к файлу в ячейку с помощью кнопки «Обзор»»

    Хотели бы вы использовать метод, не основанный на VBA?

    При использовании MicroSoft 365 Excel у вас есть возможность использовать формулу для получения имени файла и пути.

    Я использую функцию гиперссылки, начиная с пути сохранения текущего файла.

    Эта побочная защита имеет проблемы с "незаконным макросом".
    Пример:
    =IF(ISNA(@CELL("filename",B1))",После сохранения файла эта формула отобразит путь и имя», ПОДСТАВИТЬ (ЛЕВАЯ («@ЯЧЕЙКА («имя файла», B1), НАЙТИ («]», @ЯЧЕЙКА («имя файла», B1), 1)-1), «[«,» ,1))

    Дает вам путь ко всему файлу.
    Если вы знаете, что будете возвращаться к этому файлу на регулярной основе:
    вставьте формулу в файл
    F2, затем нажмите Enter, чтобы принудительно пересчитать,
    скопировать/вставить значения обратно на мой домашний лист.
    Обрезать до длины, если я хочу создать ссылку на нужную папку или файл.
    Заключить его в формулу гиперссылки.
    />Нажмите «Сохранить».

    Вот пример, который позволяет щелкнуть, чтобы открыть папку, в которой сохранен файл.

    =ЕСЛИ(ISNA(@CELL("имя файла",B2))",",ЕСЛИ(ЕОШИБКА(НАЙТИ("\",@CELL("имя файла",A1),1)),HYPERLINK(LEFT( @CELL("имя файла",B2),НАЙТИ("[",@CELL("имя файла",B2),1)-1), "Сохранено в:"&MID(@CELL("имя файла",B2),НАЙТИ ("/[[“,ПОДСТАВИТЬ(@ЯЧЕЙКА(“имя файла”,B2)”,/””,/[[“,-1+СУММПРОИЗВ(ДЛСТР(ЯЧЕЙКА(“имя файла”,B2))-ДЛСТР(ПОДСТАВИТЬ( ЯЧЕЙКА("имя файла",B2)",/"",")))),1)+1,(НАЙТИ("/[",@ЯЧЕЙКА("имя файла",B2))-2)-НАЙТИ(" /[[“,ПОДСТАВИТЬ(@ЯЧЕЙКА(“имя файла”,B2)”,/””,/[[“,-1+СУММПРОИЗВ(ДЛСТР(ЯЧЕЙКА(“имя файла”,B2))-ДЛСТР(ПОДСТАВИТЬ(ЯЧЕЙКА( "имя файла",B2)",/"",")))),1)+1)),ГИПЕРССЫЛКА(ЛЕВАЯ(@ЯЧЕЙКА("имя файла",B2),НАЙТИ("[",@ЯЧЕЙКА("имя файла ",B2),1)-1), "Сохранено в:"&MID(@CELL("filename",B2),FIND("\[[",SUBSTITUTE(@CELL("filename",B2)",\ ””,\[[“,-1+СУММПРОИЗВ(ДЛСТР(ЯЧЕЙКА(“имя файла”,B2))-ДЛСТР(ПОДСТАВИТЬ(ЯЧЕЙКА(“имя файла”,B2)”,\””,”))))),1 )+1,(НАЙТИ("\[",@ЯЧЕЙКА("имя файла",B2))-2)-НАЙТИ("\[[",ПОДСТАВИТЬ(@ЯЧЕЙКА("имя файла",B2)",\", ”\[[“,-1+СУММПРОИЗВ(ДЛСТР(ЯЧЕЙКА(“имя файла”,B2))-ДЛСТР(ПОДСТАВИТЬ(ЯЧЕЙКА(“имя файла”,B2)”,\””,”)))),1)+ 1))))

    Во всяком случае, я зашел, чтобы сказать: «Спасибо за публикацию нескольких интересных примеров и указаний от точки зрения, которая работает».

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

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

    Здравствуйте! Могу я узнать, что вы подразумеваете под
    ActiveSheet.Range(“filePath”).Value = dialogBox.SelectedItems(1)
    Измените слово “filePath” для созданного вами именованного диапазона выше.

    В начале шага «Создать именованный диапазон» вы создали именованный диапазон. В примере я использовал «filePath». Но если вы использовали другое имя, то вам нужно будет изменить код

    Оставить ответ Отменить ответ

    Награды

    Майкрософт MVP:

    Acquisition International:
    награда Business Excellence Awards 2021
    Лучший эксперт по электронным таблицам

    Хотите легко найти электронную таблицу Microsoft Excel на своем ПК? Существует несколько способов упростить поиск файлов Excel. В этой статье основное внимание будет уделено отображению местоположения файла прямо на печатной копии электронной таблицы.

    Если вы когда-нибудь замечали путь к файлу, напечатанный вверху или внизу листа Excel, вы поймете, о чем я. По сути, создатель отчета «сказал» Excel, чтобы этот путь к файлу или местоположение файла печатались непосредственно на странице каждый раз, когда страница распечатывается.

    Это довольно легко сделать, но на самом деле это не так уж интуитивно понятно. Я покажу вам, как это сделать, и надеюсь, вы сэкономите время на поиске отчетов.

    Это лучше всего работает с Microsoft Excel 2007, но эта информация может оказаться полезной и для других версий.

    Шаг 1. Предварительный просмотр печати

    После создания листа Excel перейдите в режим "Предварительный просмотр перед печатью".

    Нажмите верхний левый значок "Файл" (который выглядит как логотип Microsoft в Excel 2007), нажмите "Печать", затем нажмите "Предварительный просмотр".

    Шаг 2. Настройка страницы

    Нажмите значок "Параметры страницы" в левом верхнем углу экрана.

    Появится окно "Настройка страницы".

    Шаг 3. Вкладка «Нижний колонтитул»

    Теперь мы можем вставить путь к листу Excel либо в верхний, либо в нижний колонтитулы. Чтобы вставить расположение файла электронной таблицы в заголовок (или верхнюю часть таблицы), нажмите кнопку «Пользовательский заголовок». Чтобы вставить расположение файла электронной таблицы в нижний колонтитул (или внизу), нажмите кнопку «Пользовательский нижний колонтитул».

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

    Шаг 4. Выровняйте путь к файлу

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

    Ради этой статьи я выберу правильную сторону.

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

    8 лучших альтернатив Adobe Photoshop (бесплатных и платных)

    8 лучших альтернатив LastPass, которые стоит попробовать

    Шаг 5. Оставайтесь на этом экране

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

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

    Нажмите кнопку "Вставить путь к файлу", затем нажмите "ОК".

    Шаг 6

    Готово. Теперь, когда ваш лист Microsoft Excel распечатывается как печатная копия, местоположение этого документа будет отображаться внизу (или вверху, если вы выберете) страницы.

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

    Читайте также: