Is there a possibility to run a macro in an excel-file (data-source) when data connection is refreshed by another excel-file (data-target)?

0
votes

a lot of users in our network use an excel-workbook (.xlsm [office 2010]) created from a template.

Now, there are some important changes I've to do in the template and I want all the users to update their workbook but i'd like to avoid to contact all of them.

So, my Idea is to make an auto-update (copying the contents of their workbooks into new created workbooks and delete the former version).

Unfortunately there are no update-macros in the existing workbooks but they reference to a macro in another workbook. Each time they open their workbooks the data connections become refreshed automatically.

Can I use this refreshing event to trigger a macro in the (data-source) excel-file (maybe by creating a WithEvents-class module)?

2
excelvbaeventhandlerdata-connections
Since the data-source file is not opened in the users Excel you won't be able to run a macro in this file I assume. – Pᴇʜ
You can add some sort of version control, say in a text file in the same location as the shared file, just a text file saying ver9, then in a sheet on the excel file have a version number. Then open the text file, check the version numbers, if mismatch, copy the file. – Nathan_Sav

2 Answers

0
votes

You can do something along these lines, where the user opens a workbook, but its job is to control the version. You can change this to have the code modify sheets etc.

The text file, correct, contains ver9, the workbook contains ver8 in the ver_cont worksheet.

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
0
votes

You can try this. It uses withevents and runs when the data is updated.

First, you need to create a class name "clsQueryTable" and put this code in it

    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

Then, you can put this code in your workbook_open event on 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