Archie Goodwin

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

Мною руководили две причины, почему я собственно этот макрос вообще написал:

• 1. У меня стояла задача обновлять файлы с расчетами из какого-то типичного шаблона, копируя график с нужной стилистикой, заголовком и диапазонном ссылок на нужное место. Но при копировании, вся автоматизация сразу же сводилась на нет, т.к. все равно после отработки макроса, который копировал несколько диаграмм, приходилось руками менять диапазон ссылок в каждой такой диаграмме.
• 2. Curiosity - любопытство. Мне стало любопытно и интересно разобраться как можно обратиться ко всем графикам на листе, как посчитать сколько линий графиков на каждой такой диаграмме, как применить полученные ранее знания о "регулярных выражениях" и т.д.

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

Ниже собственно сам код с комментариями, а во вложении два файла для демонстрации работы макроса.

Sub Del_link_to_another_book_in_chart()
'макрос для удаления ссылок на другую книгу - книгу-источник, если ранее скопировали график с другой книги

 Set oRegExp1 = CreateObject("VBScript.RegExp"): oRegExp1.Global = True: oRegExp1.MultiLine = True 'подключаем объект регулярных выражений
 Set oRegExp2 = CreateObject("VBScript.RegExp"): oRegExp2.Global = True: oRegExp2.MultiLine = True 'подключаем объект регулярных выражений
 Set oRegExp3 = CreateObject("VBScript.RegExp"): oRegExp3.Global = True: oRegExp3.MultiLine = True 'подключаем объект регулярных выражений
 
 oRegExp1.Pattern = "\'.\:\\" 'формируем маску поиска для понимания закрытая ли книга источник, в таком случае в ссылке указыавается путь на диск
 oRegExp2.Pattern = "\'.\:\\.*?\[.*?\]" 'формируем маску поиска - имя книги-источника в формуле в квадратных скобках - для закрытой книги
 oRegExp3.Pattern = "\[.*?\]" 'формируем маску поиска - имя книги-источника в формуле в квадратных скобках - для открытой книги

 For Each cht In ActiveSheet.ChartObjects 'цикл для перебора всех графиков на листе
 'эMsgBox (cht.Name) 'так можно вывести все имена всех графиков на листе
 For Each sLine In cht.Chart.FullSeriesCollection 'цикл для перебора всех линий на графиках
 If oRegExp1.test(sLine.Formula) = True Then 'проверяем закрыта ли книга источник
 sLine.Formula = oRegExp2.Replace(sLine.Formula, "'") ' удаляем ссылку на другую закрытую книгу
 Else
 sLine.Formula = oRegExp3.Replace(sLine.Formula, "") ' удаляем ссылку на другую открытую книгу
 End If
 Next sLine
 Next cht
End Sub