MyTetra Share
Делитесь знаниями!
Модуль обновления подключений
Время создания: 31.07.2019 22:50
Текстовые метки: vba, Connection, Refresh
Раздел: !Закладки - VBA - Excel - Con
Запись: adgaver/mytetra_base_New/master/base/15036371089r7usxmkqx/text.html на raw.githubusercontent.com

Sub Refresh_Con_Name(ByVal oWb As Workbook, _

ByVal strConName As String)


Dim oCon As WorkbookConnection

Dim arConName As Variant, i As Long

If oWb.Connections.Count > 0 Then

arConName = Split(strConName, ";", -1, vbTextCompare)

For i = LBound(arConName) To UBound(arConName)


strConName = Trim(arConName(i))

For Each oCon In oWb.Connections

t = Timer

If oCon.Name Like "*" & strConName & "*" Then

oCon.Refresh

Debug.Print Round(Timer - t, 2), oCon.Name

End If

Next

Next i

End If

End Sub



'==================================================================================

Sub RefAllCon()

On Error Resume Next

tt = Timer


With ThisWorkbook


For Each con In .Connections

Application.StatusBar = "Обновление " & con

t = Timer

con.Refresh

t = Timer - t: Debug.Print Round(t, 2) & " " & con

Application.StatusBar = False

Next con

End With


tt = Timer - tt:: Debug.Print Round(tt, 2) & "==="

'ThisWorkbook.Connections("04_IncGretQuick_NonStr").Refresh

End Sub



'==================================================================================

Function FnRefCon(ByVal strConName As String) As Boolean

Application.StatusBar = "Обновление " & strConName

Dim dDateRef As Date

Dim dDateDelta As Date

With ThisWorkbook.Connections(strConName)

dDateRef = .ODBCConnection.RefreshDate

dDateDelta = Now - dDateRef


If dDateDelta < "01:00:00" Then

Select Case MsgBox("Последнее обновление было " & dDateDelta & " назад." & _

vbCrLf & "Обновить " & strConName, 52, "Обновление " & strConName)

Case 6 ' Да

.Refresh

FnRefCon = True

Case 7 ' Нет

FnRefCon = True

End Select

Else

.Refresh

FnRefCon = True

End If

End With

Application.StatusBar = False

End Function


'==================================================================================

Sub Ref_CSC()

Обновить = FnRefCon("conname")

End Sub


Так же в этом разделе:
 
MyTetra Share v.0.55
Яндекс индекс цитирования