Господа, помогите оптимизировать макрос на обновление данных в файлах. Макрос написал сам, но ВБА знаю плохо , изучаю около двух недель. Макрос очень часто не проходит ( выдает Debug на сохранениях файлов, при повторном запуске макрос проходит нормально), поэтому нужна помощь в оптимизации макроса
Workbooks.Open Filename:= _ "D:\Budget\2012\Pmp\Bags\Division\Distribution PMP\DS_PMP_DELSEY_2012.xlsb", _ UpdateLinks:=0, Password:="0712", WriteResPassword:="0712", IgnoreReadOnlyRecommended:="True" ActiveWorkbook.Save ActiveWindow.Close ActiveWorkbook.Save ActiveWindow.Close End Sub
[/vba]
Есть ли какая-нибудь комнда , обновляющая свзяи в книге ( чтобы не обязательно открывать 5 книг для обновления одной) и как можно уменьшить время прохождения макроса?
Господа, помогите оптимизировать макрос на обновление данных в файлах. Макрос написал сам, но ВБА знаю плохо , изучаю около двух недель. Макрос очень часто не проходит ( выдает Debug на сохранениях файлов, при повторном запуске макрос проходит нормально), поэтому нужна помощь в оптимизации макроса
Workbooks.Open Filename:= _ "D:\Budget\2012\Pmp\Bags\Division\Distribution PMP\DS_PMP_DELSEY_2012.xlsb", _ UpdateLinks:=0, Password:="0712", WriteResPassword:="0712", IgnoreReadOnlyRecommended:="True" ActiveWorkbook.Save ActiveWindow.Close ActiveWorkbook.Save ActiveWindow.Close End Sub
[/vba]
Есть ли какая-нибудь комнда , обновляющая свзяи в книге ( чтобы не обязательно открывать 5 книг для обновления одной) и как можно уменьшить время прохождения макроса?ВикторЧерный
Сообщение отредактировал ВикторЧерный - Пятница, 25.01.2013, 13:00
У меня аналогичная задача, обновлять связи из других файлов (около сотни). Только, мне кажется, немного сложнее.
Есть файлы с данными за день, каждый отдельный файл типа: 01.12.2013.xls, 02.12.2013.xls и т.д. Так же есть сводная месячная таблица типа: Декабрь.xls., где собираются данные за месяц из файлов по дням.
Причем самих файлов-дней изначально нет на диске. Еесть только ссылки на них, в сводной месячной таблице. Эти фалы присылаются по эл. почте, потовая программа сама сохраняет их в определенную папку. Да, важно, что не все файлы-дни могут быть подряд. Некоторых может не быть вообще, скажем за выходной день.
Естественно, что при открытии сводного файла Декабрь.xls. происходит обновление связей, и возникают ошибки, т.к. некоторых файлов еще нет. Пользователь ошибку игнорирует. И сводная таблица берет только данные из тех файлов, которые уже есть в наличии.
Использую Excel 2003.
Задача, периодически (скажем раз в 3 часа), обновлять связи и игнорировать возникающие ошибки.
И если появились новые файлы брать из них данные. Если делаю руками через меню: Правка >> Связи >> Обновить, так же возникает ошибка из-за отсутствующих файлов. Я её игнорирую, и все работает как надо.
В макросах я вообще не силен. Пытался делать макрос такого же типа как ВикторЧерный и запускать его вручную. Но макрос почему-то не обновляет данные из новых файлов. Он натыкается на первую же ошибку и останавливается.
Например, есть следующие файлы: 01.12.2013.xls 02.12.2013.xls 04.12.2013.xls 05.12.2013.xls При открытии сводного файла Декабрь.xls, Excel актуализирует данные из всех доступных для чтения, в данный момент, файлов.
При выполнении макроса, обновляются только данные из файлов: 01.12.2013.xls и 02.12.2013.xls А данные из файлов: 04.12.2013.xls и 05.12.2013.xls уже не актуализируются.
Решение, которое предложил Boroda у меня не работает.
Пробовал так же макрос, который бы открывал и снова открывал сводный файл. Но он тоже не работает. А кроме того его надо размещать в отдельном файле, что тоже не очень удобно.
Пробовал создавать файлы-пустышки. Чтобы не было ошибок. Но тогда почтовая программа не замещает уже существующие файлы и в сводную таблицу вообще ничего не попадает.
В общем, задача, у меня написать макрос, который будет через интервал времени автоматически актуализировать данные из внешних файлов. И игнорирую возникающие ошибки, проверять на доступность все существующие ссылки.
Посоветуйте, пожалуйста, что можно сделать!
Добрый день!
У меня аналогичная задача, обновлять связи из других файлов (около сотни). Только, мне кажется, немного сложнее.
Есть файлы с данными за день, каждый отдельный файл типа: 01.12.2013.xls, 02.12.2013.xls и т.д. Так же есть сводная месячная таблица типа: Декабрь.xls., где собираются данные за месяц из файлов по дням.
Причем самих файлов-дней изначально нет на диске. Еесть только ссылки на них, в сводной месячной таблице. Эти фалы присылаются по эл. почте, потовая программа сама сохраняет их в определенную папку. Да, важно, что не все файлы-дни могут быть подряд. Некоторых может не быть вообще, скажем за выходной день.
Естественно, что при открытии сводного файла Декабрь.xls. происходит обновление связей, и возникают ошибки, т.к. некоторых файлов еще нет. Пользователь ошибку игнорирует. И сводная таблица берет только данные из тех файлов, которые уже есть в наличии.
Использую Excel 2003.
Задача, периодически (скажем раз в 3 часа), обновлять связи и игнорировать возникающие ошибки.
И если появились новые файлы брать из них данные. Если делаю руками через меню: Правка >> Связи >> Обновить, так же возникает ошибка из-за отсутствующих файлов. Я её игнорирую, и все работает как надо.
В макросах я вообще не силен. Пытался делать макрос такого же типа как ВикторЧерный и запускать его вручную. Но макрос почему-то не обновляет данные из новых файлов. Он натыкается на первую же ошибку и останавливается.
Например, есть следующие файлы: 01.12.2013.xls 02.12.2013.xls 04.12.2013.xls 05.12.2013.xls При открытии сводного файла Декабрь.xls, Excel актуализирует данные из всех доступных для чтения, в данный момент, файлов.
При выполнении макроса, обновляются только данные из файлов: 01.12.2013.xls и 02.12.2013.xls А данные из файлов: 04.12.2013.xls и 05.12.2013.xls уже не актуализируются.
Решение, которое предложил Boroda у меня не работает.
Пробовал так же макрос, который бы открывал и снова открывал сводный файл. Но он тоже не работает. А кроме того его надо размещать в отдельном файле, что тоже не очень удобно.
Пробовал создавать файлы-пустышки. Чтобы не было ошибок. Но тогда почтовая программа не замещает уже существующие файлы и в сводную таблицу вообще ничего не попадает.
В общем, задача, у меня написать макрос, который будет через интервал времени автоматически актуализировать данные из внешних файлов. И игнорирую возникающие ошибки, проверять на доступность все существующие ссылки.
Посоветуйте, пожалуйста, что можно сделать!jack_jackson
Сообщение отредактировал jack_jackson - Понедельник, 23.12.2013, 14:56
Решение, которое предложил Boroda у меня не работает.
Конечно не работает. Вам нужно каждые 3 часа, а мой пред. код отрабатывает при открытии файла. Слова Workbook_Open Вас не насторожили? Вам нужно что-то типа этого [vba]
Код
Sub go_() Up_ End Sub Sub Up_() Dim tn_ ActiveWorkbook.RefreshAll tn_ = TimeSerial(Hour(Now) + 3, Minute(Now), Second(Now)) Range("B2") = Range("B2") + 1 Application.OnTime tn_, "Up_" End Sub
[/vba] Запускаем первый макрос, тот, который go_ называется Для проверки можете строку tn_ = TimeSerial(Hour(Now) + 3, Minute(Now), Second(Now)) переписать так tn_ = TimeSerial(Hour(Now), Minute(Now), Second(Now) + 2) - будет обновляться каждые 2 секунды. В ячейке В2 - счетчик обновлений. Можно переделать в другую ячейку или вообще стереть. Или написать Range("B2") = Now - тогда будет время последнего обновления (не забудьте поставить туда нужный формат ячейки)
Решение, которое предложил Boroda у меня не работает.
Конечно не работает. Вам нужно каждые 3 часа, а мой пред. код отрабатывает при открытии файла. Слова Workbook_Open Вас не насторожили? Вам нужно что-то типа этого [vba]
Код
Sub go_() Up_ End Sub Sub Up_() Dim tn_ ActiveWorkbook.RefreshAll tn_ = TimeSerial(Hour(Now) + 3, Minute(Now), Second(Now)) Range("B2") = Range("B2") + 1 Application.OnTime tn_, "Up_" End Sub
[/vba] Запускаем первый макрос, тот, который go_ называется Для проверки можете строку tn_ = TimeSerial(Hour(Now) + 3, Minute(Now), Second(Now)) переписать так tn_ = TimeSerial(Hour(Now), Minute(Now), Second(Now) + 2) - будет обновляться каждые 2 секунды. В ячейке В2 - счетчик обновлений. Можно переделать в другую ячейку или вообще стереть. Или написать Range("B2") = Now - тогда будет время последнего обновления (не забудьте поставить туда нужный формат ячейки)_Boroda_
Докладываю о результатах. Немного изменил код Boroda, чтобы макрос выполнялся раз в минуту (для тестов). И использовал отображение времени в ячейке S3. [vba]
Код
Sub go_() Up_ End Sub
Sub Up_() Dim tn_ ActiveWorkbook.RefreshAll tn_ = TimeSerial(Hour(Now), Minute(Now) + 1, Second(Now)) Range("S2") = Range("S2") + 1 Range("S3") = Now Application.OnTime tn_, "Up_" End Sub
[/vba]
Результат: 1. Макрос почему-то начинает автоматически выполняться при открытии книги. Хотя это вовсе и не проблема, но не понятно почему. Я понял так, что надо вручную запустить макрос go_. 2. Макрос выполняется совершенно не раз в минуту, а как-то не понятно. Вот хронометраж, состояние счетчика (B2) и последнего времени обновления (S3):
3. Связи при этом не обновлялись или пытались обновиться, но возникли ошибки. Пока работает макрос, добавляю новый файл. Через какое-то время захожу: Правка >> Связи >> Состояние, вижу что в колонке состояние напротив добавленного файла стоит ошибка "Предупреждение: значения не обновлены".
Что я не так делал? Подскажите.
Докладываю о результатах. Немного изменил код Boroda, чтобы макрос выполнялся раз в минуту (для тестов). И использовал отображение времени в ячейке S3. [vba]
Код
Sub go_() Up_ End Sub
Sub Up_() Dim tn_ ActiveWorkbook.RefreshAll tn_ = TimeSerial(Hour(Now), Minute(Now) + 1, Second(Now)) Range("S2") = Range("S2") + 1 Range("S3") = Now Application.OnTime tn_, "Up_" End Sub
[/vba]
Результат: 1. Макрос почему-то начинает автоматически выполняться при открытии книги. Хотя это вовсе и не проблема, но не понятно почему. Я понял так, что надо вручную запустить макрос go_. 2. Макрос выполняется совершенно не раз в минуту, а как-то не понятно. Вот хронометраж, состояние счетчика (B2) и последнего времени обновления (S3):
3. Связи при этом не обновлялись или пытались обновиться, но возникли ошибки. Пока работает макрос, добавляю новый файл. Через какое-то время захожу: Правка >> Связи >> Состояние, вижу что в колонке состояние напротив добавленного файла стоит ошибка "Предупреждение: значения не обновлены".
В общем покопавшись по разным форумам, кажется я понял, почему макрос, предложенный Boroda, не работает. Есть две проблемы:
1. Когда происходит обновление внешних связей, командой ActiveWorkbook.RefreshAll, и какая-то ссылка не существует (как в моем случае, файлы приходят постепенно и не все по порядку), то обновление происходит ровно до момента ошибки. Исправляю эту проблему созданием пустых файлов, которые в последствии частично заменятся пришедшими по эл. почте.
2. Когда в книге связей много (например, как у меня больше 90). Требуется время чтобы ActiveWorkbook.RefreshAll обновил все связи. Но так как после него в макросе стоят другие команды или Excel типа сам продолжает работать (тут я не совсем понял причину) то команда ActiveWorkbook.RefreshAll не успевает обновить все связи. В общем Excel выполняет только часть обновлений, как-то надо дать ему время на обновление всего большого количества связей.
Уточняю. По приблизительным замерам, с помощью секундомера, время обновления всех связей 1,5-2 сек.
Подскажите, уважаемые, как лечить вторую проблему?
Еще одно уточнение. Сделал тупо следующий макрос, который обновляет каждую внешнюю связь отдельно. Работает, конечно. Но решение громоздкое и абсолютно не универсальное. Если со временем появились новые связи или месяц не январь, а другой и т.д.
Sub M_1() ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\01.01.2014 Abend.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\01.01.2014 Morgen.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\01.01.2014 Tag.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\02.01.2014 Abend.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\02.01.2014 Morgen.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\02.01.2014 Tag.xls", Type:=xlExcelLinks ' и так далее для всех внешних связей End Sub
В общем покопавшись по разным форумам, кажется я понял, почему макрос, предложенный Boroda, не работает. Есть две проблемы:
1. Когда происходит обновление внешних связей, командой ActiveWorkbook.RefreshAll, и какая-то ссылка не существует (как в моем случае, файлы приходят постепенно и не все по порядку), то обновление происходит ровно до момента ошибки. Исправляю эту проблему созданием пустых файлов, которые в последствии частично заменятся пришедшими по эл. почте.
2. Когда в книге связей много (например, как у меня больше 90). Требуется время чтобы ActiveWorkbook.RefreshAll обновил все связи. Но так как после него в макросе стоят другие команды или Excel типа сам продолжает работать (тут я не совсем понял причину) то команда ActiveWorkbook.RefreshAll не успевает обновить все связи. В общем Excel выполняет только часть обновлений, как-то надо дать ему время на обновление всего большого количества связей.
Уточняю. По приблизительным замерам, с помощью секундомера, время обновления всех связей 1,5-2 сек.
Подскажите, уважаемые, как лечить вторую проблему?
Еще одно уточнение. Сделал тупо следующий макрос, который обновляет каждую внешнюю связь отдельно. Работает, конечно. Но решение громоздкое и абсолютно не универсальное. Если со временем появились новые связи или месяц не январь, а другой и т.д.
Sub M_1() ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\01.01.2014 Abend.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\01.01.2014 Morgen.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\01.01.2014 Tag.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\02.01.2014 Abend.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\02.01.2014 Morgen.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "D:\2014\Январь\02.01.2014 Tag.xls", Type:=xlExcelLinks ' и так далее для всех внешних связей End Sub
Решил возобновить тему, рассказать о результатах, вдруг кому будет полезно.
Решить мою проблему с помощью команды ActiveWorkbook.RefreshAll у меня так и не вышло. Почему-то, не хотела она обновлять все связи, а разобраться сам я так и не смог. Пошел по другому пути. Оно основано не только на Excel, но может будет кому-то полезно, поэтому я счет уместным описать его тут. Приношу свои извинения фуромчанам, если это не совсем по теме.
Еще раз напомню в чем моя задача и проблема. Задача постоянно и автоматически обновлять данные с сводной таблице. А проблема в следующем, данные в сводной таблице берутся из внешних таблиц (файлов). Которые постоянно приходят по эл. почте. Необходимо было, чтобы данные из вновь полученных файлов, попадали в сводную таблицу. Первоначально я хотел чтобы сводная таблица была все время открыта и периодически обновляла свои связи.
Решение: 1. Отключил в Excel запрос на обновление связей, так чтобы при открытии книги внешние связи обновлялись автоматически, без запроса. 2. Сделал следующий макрос, который сохраняет книгу [vba]
Код
Sub Auto_Open() ActiveWorkbook.Save End Sub
[/vba] 3. В почтовой программе TheBath создал правило: - сохранять полученные файлы типа xls в определенную папку, - запускать bat-файл, который открывает мою сводную таблицу - закрывать Excel по истечении 60 секунд (этого мне хватает, чтобы все связи в книге обновились и она сохранилась)
Таким образом моя задача решилась, только с другого конца. Не Excel ждет новых данных, а почтовик запускает Excel чтобы обновились данные. Еще раз приношу извинения всем специалистам по Excel, понимаю, что моё решение не совсем по теме форума.
Решил возобновить тему, рассказать о результатах, вдруг кому будет полезно.
Решить мою проблему с помощью команды ActiveWorkbook.RefreshAll у меня так и не вышло. Почему-то, не хотела она обновлять все связи, а разобраться сам я так и не смог. Пошел по другому пути. Оно основано не только на Excel, но может будет кому-то полезно, поэтому я счет уместным описать его тут. Приношу свои извинения фуромчанам, если это не совсем по теме.
Еще раз напомню в чем моя задача и проблема. Задача постоянно и автоматически обновлять данные с сводной таблице. А проблема в следующем, данные в сводной таблице берутся из внешних таблиц (файлов). Которые постоянно приходят по эл. почте. Необходимо было, чтобы данные из вновь полученных файлов, попадали в сводную таблицу. Первоначально я хотел чтобы сводная таблица была все время открыта и периодически обновляла свои связи.
Решение: 1. Отключил в Excel запрос на обновление связей, так чтобы при открытии книги внешние связи обновлялись автоматически, без запроса. 2. Сделал следующий макрос, который сохраняет книгу [vba]
Код
Sub Auto_Open() ActiveWorkbook.Save End Sub
[/vba] 3. В почтовой программе TheBath создал правило: - сохранять полученные файлы типа xls в определенную папку, - запускать bat-файл, который открывает мою сводную таблицу - закрывать Excel по истечении 60 секунд (этого мне хватает, чтобы все связи в книге обновились и она сохранилась)
Таким образом моя задача решилась, только с другого конца. Не Excel ждет новых данных, а почтовик запускает Excel чтобы обновились данные. Еще раз приношу извинения всем специалистам по Excel, понимаю, что моё решение не совсем по теме форума.jack_jackson
Сообщение отредактировал jack_jackson - Среда, 29.01.2014, 23:05
Ещё реальнее - сделать так, чтобы обновление запускалось именно и только по событию поступления новой информации. То есть сама почтовая программа, после подтвержденного приёма, - инициирует обновение свода. Если в это время поступают новые сообщения - они накапливаются, а затем снова инициируется обновление - ибо поступили новые сообщения. И никаких циклов ожидания и надежд на скорость обновления данных приложением - привыкайте управлять полной моделью данных, включающей не только методы, но и события...
Ещё реальнее - сделать так, чтобы обновление запускалось именно и только по событию поступления новой информации. То есть сама почтовая программа, после подтвержденного приёма, - инициирует обновение свода. Если в это время поступают новые сообщения - они накапливаются, а затем снова инициируется обновление - ибо поступили новые сообщения. И никаких циклов ожидания и надежд на скорость обновления данных приложением - привыкайте управлять полной моделью данных, включающей не только методы, но и события... AndreTM
Ещё реальнее - сделать так, чтобы обновление запускалось именно и только по событию поступления новой информации. То есть сама почтовая программа, после подтвержденного приёма, - инициирует обновение свода.
Я так и сделал Если кому надо, пишите, помогу и bat-файл скину
Ещё реальнее - сделать так, чтобы обновление запускалось именно и только по событию поступления новой информации. То есть сама почтовая программа, после подтвержденного приёма, - инициирует обновение свода.
Я так и сделал Если кому надо, пишите, помогу и bat-файл скинуjack_jackson
Сообщение отредактировал jack_jackson - Пятница, 07.02.2014, 18:13
_Boroda_, Александр, а также форумчане!!! Прошу Вас помочь поправить мой код, по аналогичной теме (дабы не создавать новую). ЗАДАЧА: а если лист книги запоролены правильно я тогда понял что нужно сделать так вот: [vba]
Код
Private Sub Workbook_Open() 'отключаем обновление экрана Application.ScreenUpdating = False
Dim sh As Worksheet 'снимаем защиту For Each sh In Worksheets sh.Unprotect "09052002" 'цифры - это пароль Next sh
'обновляем запись For Each sh In ThisWorkbook.Sheets ActiveWorkbook.RefreshAll
Next sh 'устонавливаем защиту For Each sh In Worksheets sh.Protect password:="09052002", DrawingObjects:=True, Contents:=True, Scenarios:=True Next sh
[/vba] Но что то вроде бы не то ((( особено смущает меня именно вот это
Цитата
'обновляем запись For Each sh In ThisWorkbook.Sheets ActiveWorkbook.RefreshAll
Хотя при проверке ошибок не выявляет, но и при открывании файла связи не обновляются. А вот если я сам сниму пароль и обновлю то все олрайд!!!!
_Boroda_, Александр, а также форумчане!!! Прошу Вас помочь поправить мой код, по аналогичной теме (дабы не создавать новую). ЗАДАЧА: а если лист книги запоролены правильно я тогда понял что нужно сделать так вот: [vba]
Код
Private Sub Workbook_Open() 'отключаем обновление экрана Application.ScreenUpdating = False
Dim sh As Worksheet 'снимаем защиту For Each sh In Worksheets sh.Unprotect "09052002" 'цифры - это пароль Next sh
'обновляем запись For Each sh In ThisWorkbook.Sheets ActiveWorkbook.RefreshAll
Next sh 'устонавливаем защиту For Each sh In Worksheets sh.Protect password:="09052002", DrawingObjects:=True, Contents:=True, Scenarios:=True Next sh
[/vba] Но что то вроде бы не то ((( особено смущает меня именно вот это
Цитата
'обновляем запись For Each sh In ThisWorkbook.Sheets ActiveWorkbook.RefreshAll
Хотя при проверке ошибок не выявляет, но и при открывании файла связи не обновляются. А вот если я сам сниму пароль и обновлю то все олрайд!!!!lebensvoll
А не все вопрос закрыт. Связи обновились. Как только выключил запрос на обновление связей и установил галочку "НЕ задавать вопрос и обновлять связи". Вроде все ГУД... Спасибо всем....
А не все вопрос закрыт. Связи обновились. Как только выключил запрос на обновление связей и установил галочку "НЕ задавать вопрос и обновлять связи". Вроде все ГУД... Спасибо всем....lebensvoll
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Я же говорю: 
