Как обновить файл эксель не закрывая его
При создании внешних ссылок на другие книги (также называемые связями) вы можете управлять тем, обновляются ли они. Это важно, так как если кто-то другой изменяет значение связанной ячейки, Excel не обновляет ссылку и не отображает новое значение, если вы не сообщаете об этом.
Книга, которая содержит связи, называется книгой назначения, а книга, с которую она связана, называется книгой-источником. Вы можете связать несколько исходных книг с одной книгой.
1. Destination workbook contains the external reference (link).
2. Внешняя ссылка (или ссылка) — это ссылка на ячейку или диапазон в книге-источнике.
3. Книга-источник содержит связанную ячейку или диапазон и фактическое значение, возвращаемого в 9-й книге.
Примечание: Количество книг, на которые можно ссылаться, не ограничивается, но обычно лучше использовать как можно меньше ссылок. В противном случае могут возникнуть проблемы с производительностью при пересчете формул. Кроме того, можно перекрестно связывать книги, где они выступают в качестве источника и назначения, но это может запутать и затруднить управление.
Когда исходные и назначения книг открываются на одном компьютере, связи обновляются автоматически. Когда конечная книга открыта, а книга-источник не открыта, в области сведений о документе возможно появление запроса на обновление связей. Можно задать режим вывода этого запроса, а также режим обновления всех связей без вывода запроса. Вы также можете обновить только определенные ссылки, если книга содержит несколько ссылок.
В следующих разделах рассматриваются наиболее распространенные варианты изменения связей.
Закроем все исходные книги. Если одна книга-источник будет открыта, а остальные закрыты, обновления не будут одинаковыми.
Откройте конечную книгу.
Чтобы обновить связи, на панели доверия нажмите кнопку Обновить. Закройте планку доверия, если вы не хотите обновлять связи (на правом — X).
Откройте книгу, содержащую связи.
Перейдите в >запросы & подключения >ссылки.
Из списка Источник выберите связанный объект, который необходимо изменить.
Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.
Найдите книгу, которую вы хотите использовать в качестве нового источника для внешней ссылки, и обратите внимание на ее расположение.
В книге назначения выберите ячейку с внешней ссылкой, которую вы хотите изменить.
В строка формул наймем ссылку на другую книгу, например C:\Reports\[Budget.xlsx], и замените ее расположением новой книги-источника.
Вы можете решить, обновляются ли ссылки в этой книге при открытии файла в соответствии с настройками пользователя, не обновляются при открытии файла или обновляются автоматически без запроса пользователя.
Предупреждение: Этот режим влияет на всех пользователей книги. Если отключить обновление связей и выдачу запроса, пользователи этой книги не будут знать, что данные устарели.
Перейдите в >запросы & подключения >ссылки.
Выберите один из следующих трех вариантов:
Разрешим пользователям отображать оповещение или нет
Не показывать оповещение и не обновлять автоматические ссылки
Не отображайте ссылки на оповещения и обновления.
Параметры автоматического и ручного обновления. Для ссылок на формулы всегда установлено автоматическое обновление.
Даже если указать, что Excel не должен спрашивать, следует ли обновлять ссылки, вы по-прежнему будете уведомлены об их неработаете.
Открыв диалоговое окно Изменение связей (Запросы > данных & connections > Изменениесвязей), вы сможете работать с существующими ссылками несколькими вариантами. Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.
Обновление значений
При этом будут обновлены все выбранные книги.
Изменить источник
Этот параметр удобен, если нужно наказать существующие ссылки на другой источник. Например, у вас может быть книга за предыдущий год, и вам нужно будет навести указатель на новую книгу, когда начинается новый год. Если выбрать изменить источник, запустится диалоговое окно проводника, в котором можно найти новую книгу. Если книга состоит из двух листов, вам будет предложено указать, на какой из них должна указывать ссылка. Просто щелкните нужный лист и нажмите кнопку ОК.
Примечание: Вы можете наказать книгу на себя, выбрав ее в диалоговом оке Изменение источника. В этом случае любая формула будет отсоединяться от исходной исходной книги.
Open Source
Разрыв связи
Важно: При разрыве связей с источником все формулы, использующие источник, заменяются на их текущее значение. Например, ссылка =СУММ([Budget.xlsx]Годовой! C10:C25) будет преобразовано в сумму значений в книге-источнике. Так как это действие нельзя отменить, может потребоваться сначала сохранить версию файла.
На вкладке Данные в группе Запросы & Connections нажмитекнопку Изменить связи.
В списке Источник выберите связь, которую требуется разорвать.
Вы можете выбрать отдельные книги, нажав CTRL+щелчок, или все их с помощью CTRL+A.
Щелкните элемент Разорвать.
Если ссылка использовала определенное имя, оно не удаляется автоматически, поэтому вы также можете удалить его.
Чтобы удалить имя:
Если используется диапазон внешних данных, параметр запроса может также использовать данные из другой книги. Может потребоваться проверить и удалить эти типы связей.
На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
В столбце Имя выберите имя, которое следует удалить, и нажмите кнопку Удалить.
Проверка состояния
В области Изменение связей просто отображается уведомление о том, является ли связанная книга допустимым источником. Должно отображаться "ОК",но если этого не происходит, необходимо проверить исходные книги. Во многих случаях при вырезании ссылки может быть перемещена или удалена книга-источник. Если книга по-прежнему существует, вы можете использовать параметр Изменить источник, чтобы перессылка книги.
Можно ли заменить одну формулу ее вычисляемой величиной?
Да. Формулы в Excel, вместо которых вводится их значение, удаляются без возможности восстановления. Если вы случайно заменяете формулу значением и хотите ее восстановить, нажмите кнопку Отменить сразу после ввода или вжатия значения.
Нажмите CTRL+C, чтобы скопировать формулу.
Нажмите ALT+E+S+V, чтобы вировать формулу в качестве ее значения, или перейдите на главная> буфер обмена> в > Вэтом > в буфер обмена .
Что делать, если я не подключен к источнику?
Я не хочу заменять текущие данные новыми
Я пытался обновить его в прошлый раз, но это занимает слишком много времени
Кто-то другой создал книгу, и я не знаю, почему я вижу этот запрос
Щелкните Не обновлятьи обратитесь к владельцу книги. Вы также можете выяснить, какие связи находятся в книге. На вкладке Данные в группе Запросы & Connections нажмитекнопку Изменить связи.
Я отвечаю на запрос одинаково каждый раз и не хочу видеть его снова
Можно ответить на запрос и запретить его вывод для этой книги в будущем.
Не отображать запрос и обновлять связи автоматически
Этот параметр влияет на все книги, которые открываются на компьютере. Пользователи, которые откроют книгу на другом компьютере, не будут затронуты.
Откройте меню Файл, выберите раздел Параметры, а затем — Дополнительно.
В разделе Общие снимите флажок Запрашивать об обновлении автоматических связей. Если этот флажок снят, связи будут обновляться автоматически (без выдачи запроса).
Одинаковый запрос для всех пользователей этой книги
Предупреждение: Этот режим влияет на всех пользователей книги. Если отключить обновление связей и выдачу запроса, пользователи этой книги не будут знать, что данные устарели.
Перейдите в > запросы & подключения > Изменить ссылки > запросана запуск и выберите нужный параметр.)
Примечание: При наличии разорванных связей будет появляться оповещение об этом.
Что делать, если я использую запрос с параметрами?
Закройте конечную книгу.
Откройте конечную книгу.
Связь с параметрическим запросом нельзя обновить без открытия книги-источника.
Почему я не могу выбрать параметр "Вручную" в качестве параметра обновления для определенной внешней ссылки?
Для ссылок на формулы всегда установлено автоматическое.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Вопрос такой - в папке имеется несколько десятков файлов эксель, таблицы с одинаковой структурой, но разным количеством строк + один файл в который вытягиваются определенные значения из этих таблиц. В этом файле более 5к строк. Для вытягивания использую формулу:
Формула работает, всё, что нужно вытягивает. Проблема в том, что при первом открытии вместо значения выдаётся
и необходимо открыть/закрыть все книги, чтобы собрать данные. В интернете встречал решения на ВБА, но я в нём не разбираюсь и, так как решения для единичной книги не могу сообразить, что мне нужно сделать, чтобы данные со всей кучи моих файлов подгружались в файл-сборщик :(
Есть ли сравнительно простое решение?
П.С. Ранее структура таблиц была проще и для вытягивания значений я использовал формулу:
Использование этой формулы не требовало открытия всех основных файлов.
П.П.С. Если решением будет изменение формулы, также буду благодарен. Может просто не могу сообразить чего-то.
Вопрос такой - в папке имеется несколько десятков файлов эксель, таблицы с одинаковой структурой, но разным количеством строк + один файл в который вытягиваются определенные значения из этих таблиц. В этом файле более 5к строк. Для вытягивания использую формулу:
Формула работает, всё, что нужно вытягивает. Проблема в том, что при первом открытии вместо значения выдаётся
и необходимо открыть/закрыть все книги, чтобы собрать данные. В интернете встречал решения на ВБА, но я в нём не разбираюсь и, так как решения для единичной книги не могу сообразить, что мне нужно сделать, чтобы данные со всей кучи моих файлов подгружались в файл-сборщик :(
Есть ли сравнительно простое решение?
П.С. Ранее структура таблиц была проще и для вытягивания значений я использовал формулу:
Использование этой формулы не требовало открытия всех основных файлов.
П.П.С. Если решением будет изменение формулы, также буду благодарен. Может просто не могу сообразить чего-то.
Вопрос такой - в папке имеется несколько десятков файлов эксель, таблицы с одинаковой структурой, но разным количеством строк + один файл в который вытягиваются определенные значения из этих таблиц. В этом файле более 5к строк. Для вытягивания использую формулу:
Формула работает, всё, что нужно вытягивает. Проблема в том, что при первом открытии вместо значения выдаётся
и необходимо открыть/закрыть все книги, чтобы собрать данные. В интернете встречал решения на ВБА, но я в нём не разбираюсь и, так как решения для единичной книги не могу сообразить, что мне нужно сделать, чтобы данные со всей кучи моих файлов подгружались в файл-сборщик :(
Есть ли сравнительно простое решение?
П.С. Ранее структура таблиц была проще и для вытягивания значений я использовал формулу:
Использование этой формулы не требовало открытия всех основных файлов.
П.П.С. Если решением будет изменение формулы, также буду благодарен. Может просто не могу сообразить чего-то.
Заранее спасибо. Автор - Сергей
Дата добавления - 17.10.2012 в 16:43
Добрый день.
Есть скрипт:
Sub OtkritVseKnigi()
'Шаг 1:Объявляем переменные
Dim MyFiles As String
'Шаг 2: Укажите нужную папку
MyFiles = Dir("C:\папка\*.xlsx")
Do While MyFiles <> ""
'Шаг 3: Открываем файлы один за другим
Workbooks.Open "C:\папка\" & MyFiles
'Код макроса с действиями
MsgBox ActiveWorkbook.Name
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Close SaveChanges:=True
'Шаг 4: Следующий файл в папке
MyFiles = Dir
Loop
End Sub
этот скрипт открывает и обновляет эксели. Но на каждом экселе в свойствах подключения , далее в строке подключения убран пароль и снята галочка сохранить пароль, это сделано для того, чтобы никакой другой пользователь не обновлял эти эксели, кроме одного человека. Но т.к. экселей много, нужно придумать, чтобы это делал скрипт на VBA, как доработать скрипт чтобы он вставлял нужный пароль , обновлял ,потом убирал пароль и сохранял? И как доработать скрипт, если в одной книге , много листов с разными подключениями?
Добрый день.
Есть скрипт:
Sub OtkritVseKnigi()
'Шаг 1:Объявляем переменные
Dim MyFiles As String
'Шаг 2: Укажите нужную папку
MyFiles = Dir("C:\папка\*.xlsx")
Do While MyFiles <> ""
'Шаг 3: Открываем файлы один за другим
Workbooks.Open "C:\папка\" & MyFiles
'Код макроса с действиями
MsgBox ActiveWorkbook.Name
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Close SaveChanges:=True
'Шаг 4: Следующий файл в папке
MyFiles = Dir
Loop
End Sub
этот скрипт открывает и обновляет эксели. Но на каждом экселе в свойствах подключения , далее в строке подключения убран пароль и снята галочка сохранить пароль, это сделано для того, чтобы никакой другой пользователь не обновлял эти эксели, кроме одного человека. Но т.к. экселей много, нужно придумать, чтобы это делал скрипт на VBA, как доработать скрипт чтобы он вставлял нужный пароль , обновлял ,потом убирал пароль и сохранял? И как доработать скрипт, если в одной книге , много листов с разными подключениями? Minia13
Sub OtkritVseKnigi()
'Шаг 1:Объявляем переменные
Dim MyFiles As String
'Шаг 2: Укажите нужную папку
MyFiles = Dir("C:\папка\*.xlsx")
Do While MyFiles <> ""
'Шаг 3: Открываем файлы один за другим
Workbooks.Open "C:\папка\" & MyFiles
'Код макроса с действиями
MsgBox ActiveWorkbook.Name
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Close SaveChanges:=True
'Шаг 4: Следующий файл в папке
MyFiles = Dir
Loop
End Sub
этот скрипт открывает и обновляет эксели. Но на каждом экселе в свойствах подключения , далее в строке подключения убран пароль и снята галочка сохранить пароль, это сделано для того, чтобы никакой другой пользователь не обновлял эти эксели, кроме одного человека. Но т.к. экселей много, нужно придумать, чтобы это делал скрипт на VBA, как доработать скрипт чтобы он вставлял нужный пароль , обновлял ,потом убирал пароль и сохранял? И как доработать скрипт, если в одной книге , много листов с разными подключениями? Автор - Minia13
Дата добавления - 22.09.2021 в 11:58
Доброго времени суток!
Помогите пожалуйста.
Имеется группа экселевских файлов (около 400), данные из которых сводятся в отдельном файле.
Данные в каждом файле зависят от даты, которая забивается в сводном файле, и на которую ссылаются эти отдельные файлы.
Как сделать так, чтобы дата (или ссылка) в тих файлах обновлялась автоматически без открытия файла.
Или, может, есть какая-то программка, которая бы запускала обновление этих файлов в определенное время?
Доброго времени суток!
Помогите пожалуйста.
Имеется группа экселевских файлов (около 400), данные из которых сводятся в отдельном файле.
Данные в каждом файле зависят от даты, которая забивается в сводном файле, и на которую ссылаются эти отдельные файлы.
Как сделать так, чтобы дата (или ссылка) в тих файлах обновлялась автоматически без открытия файла.
Или, может, есть какая-то программка, которая бы запускала обновление этих файлов в определенное время? ELLE
Без открытия боюсь не получится. Но можно открывать файлы в фоновом режиме, так что пользователь этого не увидит
Без открытия боюсь не получится. Но можно открывать файлы в фоновом режиме, так что пользователь этого не увидит Serge_007
Serge_007, что значит открывать в фоновом режиме?
Я в excel новичок, можно сказать! Только учусь! ELLE
Нашёл в закромах - откуда взял, не записано.
'------------ Excel-файлы в этой папке ------------------
Имя = Dir(Папка & "*.xls*")
Do While Имя <> ""
Workbooks.Open FileName:=Папка & Имя , UpdateLinks:=True
'здесь Ваш макрос делает свое грязное дело
ActiveWorkbook.Close SaveChanges:=True
Имя = Dir
Loop
С доработкой вероятно так:
[vba]
Папка = "полный путь папки"
'------------ Excel-файлы в этой папке ------------------
Имя = Dir(Папка & "*.xls*")
Do While Имя <> ""
With .Workbooks.Open _
(Filename:=Папка & Имя, UpdateLinks:=True)
'здесь Ваш макрос делает свое грязное дело
.Close SaveChanges:=True
End With
Имя = Dir
Loop
Нашёл в закромах - откуда взял, не записано.
'------------ Excel-файлы в этой папке ------------------
Имя = Dir(Папка & "*.xls*")
Do While Имя <> ""
Workbooks.Open FileName:=Папка & Имя , UpdateLinks:=True
'здесь Ваш макрос делает свое грязное дело
ActiveWorkbook.Close SaveChanges:=True
Имя = Dir
Loop
С доработкой вероятно так:
[vba]
Папка = "полный путь папки"
'------------ Excel-файлы в этой папке ------------------
Имя = Dir(Папка & "*.xls*")
Do While Имя <> ""
With .Workbooks.Open _
(Filename:=Папка & Имя, UpdateLinks:=True)
'здесь Ваш макрос делает свое грязное дело
.Close SaveChanges:=True
End With
Имя = Dir
Loop
'------------ Excel-файлы в этой папке ------------------
Имя = Dir(Папка & "*.xls*")
Do While Имя <> ""
Workbooks.Open FileName:=Папка & Имя , UpdateLinks:=True
'здесь Ваш макрос делает свое грязное дело
ActiveWorkbook.Close SaveChanges:=True
Имя = Dir
Loop
С доработкой вероятно так:
[vba]
Папка = "полный путь папки"
'------------ Excel-файлы в этой папке ------------------
Имя = Dir(Папка & "*.xls*")
Do While Имя <> ""
With .Workbooks.Open _
(Filename:=Папка & Имя, UpdateLinks:=True)
'здесь Ваш макрос делает свое грязное дело
.Close SaveChanges:=True
End With
Имя = Dir
Loop
Hugo, спасибо большое! Только не могли бы Вы еще объяснить как создать макрос более подробно. что за чем.
ищу в сети, не могу найти.
не так много времени.
заранее спасибо!
Hugo, спасибо большое! Только не могли бы Вы еще объяснить как создать макрос более подробно. что за чем.
ищу в сети, не могу найти.
не так много времени.
заранее спасибо! ELLE
Освоите простой код - можете попробовать этот (но сперва на тестовых файлах, или на копии рабочих в спецпапке).
Путь к папке пропишите вместо
Папка = "полный путь папки"
например
Папка = "C:\TMP\спецпапка\"
Освоите простой код - можете попробовать этот (но сперва на тестовых файлах, или на копии рабочих в спецпапке).
Путь к папке пропишите вместо
Папка = "полный путь папки"
например
Папка = "C:\TMP\спецпапка\" Hugo
Освоите простой код - можете попробовать этот (но сперва на тестовых файлах, или на копии рабочих в спецпапке).
Путь к папке пропишите вместо
Папка = "полный путь папки"
например
Папка = "C:\TMP\спецпапка\" Автор - Hugo
Дата добавления - 18.09.2012 в 14:43
Вы можете в любой момент нажать кнопку Обновить, чтобы обновить данные в сводных таблицах в книге. Можно обновлять значения в сводных таблицах, подключенных к внешним данным, таким как базы данных (SQL Server, Oracle, Access и другие), куб служб Analysis Services, веб-каналы данных и многие другие источники. Вы также можете обновлять данные из исходной таблицы в той же или другой книге. Кроме того, можно настроить автоматическое обновление данных сводной таблицы при открытии книги.
По умолчанию с помощью этой ссылки вы можете указать, что при ее автоматическом обновлении в книге, которая содержит ее, с помощью стеблицы.
Обновление вручную
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.
Совет: Чтобы одновременно обновить все стебли в книге, нажмите кнопку Анализ > Обновить все.
Если таблица обновляется слишком долго, на вкладке Анализ нажмите стрелку рядом с кнопкой Обновить и выберите пункт Состояние обновления. Так вы сможете проверить состояние обновления.
Чтобы отменить обновление, нажмите Отменить обновление.
Блокировка изменения ширины столбцов и форматирования ячеек
Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.
Автоматическое обновление данных при открытии книги
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке Данные проверьте поле Обновлять данные при открытии файла.
Обновление вручную
Щелкните в любом месте сводной таблицы.
Отобразит вкладку "Параметры" и "Конструктор" в области "Инструменты для работы со таблицами".
На вкладке Параметры в группе Данные сделайте следующее:
Чтобы обновить данные в соответствие с источником данных, нажмите кнопку Обновить или нажмите клавиши ALT+F5.
Вы также можете щелкнуть правой кнопкой мыши и нажать кнопку Обновить.
Чтобы обновить все стебли в книге, нажмите кнопку Обновить и выберите обновить все.
Если обновление занимает больше времени, чем вы ожидаете, щелкните Параметры > обновить > Состояние обновления, чтобы проверить состояние обновления.
Чтобы отменить обновление, нажмите Отменить обновление.
Блокировка изменения ширины столбцов и форматирования ячеек
Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.
Щелкните Параметры > параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.
Автоматическое обновление данных в pivottable при открытии книги
Щелкните в любом месте сводной таблицы.
На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры таблицы на вкладке Данные выберите параметр Обновлять данные при открытии файла.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также: