These 3 pieces of code implement the BeforeRefresh query handler and must be put in the correct modules. It works with a query added manually with the Excel UI or a query added with VBA code, as your last post does. It expects the query to be on a sheet named "Sheet1".
Put this code in a
class module (e.g. Class1), and IMPORTANT, change its Name property to clsQuery instead of Class1:
VBA Code:
Option Explicit
Public WithEvents Query As QueryTable
Private Sub Query_BeforeRefresh(Cancel As Boolean)
Cancel = Not IsInternetConnected()
End Sub
Put this code in a
standard module (e.g Module1):
VBA Code:
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function InternetGetConnectedStateEx Lib "Wininet.dll" (ByRef lpdwFlags As LongPtr, ByVal lpszConnectionName As String, ByVal dwNameLen As Integer, ByVal dwReserved As Long) As Boolean
#Else
Private Declare Function InternetGetConnectedStateEx Lib "Wininet.dll" (ByRef lpdwFlags As Long, ByVal lpszConnectionName As String, ByVal dwNameLen As Integer, ByVal dwReserved As Long) As Boolean
#End If
Dim PQtable As clsQuery
Public Sub Create_Query_Handler()
Dim qt As QueryTable
Set qt = ThisWorkbook.Worksheets("Sheet1").ListObjects(1).QueryTable
Set PQtable = New clsQuery
Set PQtable.Query = qt
End Sub
Public Function IsInternetConnected() As Boolean
#If VBA7 Then
Dim flags As LongPtr
#Else
Dim flags As Long
#End If
Dim connectionName As String * 255
IsInternetConnected = InternetGetConnectedStateEx(flags, connectionName, LenB(connectionName), 0)
End Function
With your query set to an automatic periodic refresh (e.g. 1 minute) you can set up and test the BeforeRefresh handler by running the above Create_Query_Handler macro just once.
In order to set up the handler every time you open the workbook, put this code in the
ThisWorkbook module:
VBA Code:
Option Explicit
Private Sub Workbook_Open()
Create_Query_Handler
End Sub
Then save, close and reopen the workbook to test it.
