0

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)?

user2302113
  • 41
  • 1
  • 5
  • 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ᴇʜ Feb 04 '19 at 15:04
  • 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 Feb 04 '19 at 15:05

2 Answers2

0

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
Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20
  • Thank you for your answer. Unfortunately that doesn't solve the problem. To establish a version control, I have to change the users workbook contents as well. – user2302113 Feb 04 '19 at 16:08
  • OK. No worries. :) Yes, this will open, then do what is needed based on the version, you will change the version number in the shared file. – Nathan_Sav Feb 04 '19 at 16:09
0

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
IAmNerd2000
  • 761
  • 1
  • 4
  • 12
  • Thanks but I put it in my data source xlsm but nothing happens. No success, no error. – user2302113 Feb 04 '19 at 17:01
  • Try to hit the refresh button under data. – IAmNerd2000 Feb 04 '19 at 17:04
  • I updated the code to automatically refresh the connections – IAmNerd2000 Feb 04 '19 at 17:28
  • yes, you need to make sure to name the class as specified above. How are the connections specified in your code? It may not be a list object. Try running the Workbook_Open sub and step through it to see if everything is getting set. – IAmNerd2000 Feb 04 '19 at 20:15
  • Also, I would add the code I mentioned above to the macro that all sheets are referencing. This way the code above will execute when that macro is called. You could probably move the contents of the Workbook_Open subroutine into the targeted macro. Just make sure that the class module is also in the base workbook. – IAmNerd2000 Feb 04 '19 at 21:43