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

Question

Многие пользователи в нашей сети используют рабочую книгу Excel (.xlsm [офис 2010]), созданную по шаблону.

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

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

К сожалению, в существующих книгах нет макросов обновления, но они ссылаются на макрос в другой книге. Каждый раз, когда они открывают свои книги, соединения данных обновляются автоматически.

Могу ли я использовать это событие обновления для запуска макроса в Excel-файле (источник данных) (возможно, путем создания модуля класса WithEvents)?

Answer 1

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

Правильный текстовый файл содержит ver9, книга содержит ver8 в листе ver_cont.

Function get_version() As String

Open "c:\workspace\test_ver.txt" For Input As #1

Input #1, get_version

Close #1

End Function

Function check_version()

If get_version = Worksheets("Ver_cont").Range("a1") Then
    '   Open the workbook here
Else
    '   Copy the workbook
    '   Then open it
End If

End Function

Answer 2

Вы можете попробовать это. Он использует withevents и работает, когда данные обновляются.

Во-первых, вам нужно создать имя класса "clsQueryTable" и поместить в него этот код

    Option Explicit

    Public WithEvents QTQueryTable As Excel.QueryTable

    Private Sub QTQueryTable_BeforeRefresh(blnCancel As Boolean)
        'Set blnCancel to true to stop the refresh
        Debug.Print blnCancel
    End Sub

    Private Sub QTQueryTable_AfterRefresh(ByVal blnSuccess As Boolean)
        'blnSuccess can be used to check for refresh success.

        '  I would put your update code here!
        Debug.Print blnSuccess
    End Sub

Затем вы можете поместить этот код в событие workbook_open на ThisWorkbook

    Option Explicit

    Dim colQueryTables As Collection

    Private Sub Workbook_Open()
        Dim shtMySheet As Worksheet
        Dim clsQT As clsQueryTable
        Dim qtMyQuery As QueryTable
        Dim loMyList As ListObject
        Dim conn As WorkbookConnection

        Set colQueryTables = New Collection

        For Each shtMySheet In ThisWorkbook.Worksheets
            For Each loMyList In shtMySheet.ListObjects
                Set clsQT = New clsQueryTable
                Set clsQT.QTQueryTable = loMyList.QueryTable

                colQueryTables.Add clsQT
            Next loMyList
        Next shtMySheet

        For Each conn In Connections
            conn.Refresh
        Next
    End Sub