13_ExcelVBE/getConnections.bas at master · AlignmentSystems/13_ExcelVBE · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
Attribute VB_Name = "getConnections"
Option Explicit
Option Compare Text
Option Base 0
'============================================================================================================================
'
'
'   Author      :       John Greenan
'   Email       :       john.greenan@alignment-systems.com
'   Company     :       Alignment Systems Limited
'   Date        :       6th April 2014
'
'   Purpose     :       Matching Engine in Excel VBA for Alignment Systems Limited
'
'   References  :       See VB Module FL for list extracted from VBE
'   References  :
'============================================================================================================================

Function DecodeCommandType(Incoming As Excel.XlCmdType) As String
'============================================================================================================================
'
'
'   Author      :       John Greenan
'   Email       :       john.greenan@alignment-systems.com
'   Company     :       Alignment Systems Limited
'   Date        :       6th April 2014
'
'   Purpose     :       Matching Engine in Excel VBA for Alignment Systems Limited
'
'   References  :       See VB Module FL for list extracted from VBE
'   References  :
'============================================================================================================================

Select Case Incoming
    Case Excel.XlCmdType.xlCmdCube
        DecodeCommandType = "xlCmdCube"
    Case Excel.XlCmdType.xlCmdDAX
        DecodeCommandType = "xlCmdDAX"
    Case Excel.XlCmdType.xlCmdDefault
        DecodeCommandType = "xlCmdDefault"
    Case Excel.XlCmdType.xlCmdExcel
        DecodeCommandType = "xlCmdExcel"
    Case Excel.XlCmdType.xlCmdList
        DecodeCommandType = "xlCmdList"
    Case Excel.XlCmdType.xlCmdSql
        DecodeCommandType = "xlCmdSql"
    Case Excel.XlCmdType.xlCmdTable
        DecodeCommandType = "xlCmdTable"
    Case Excel.XlCmdType.xlCmdTableCollection
        DecodeCommandType = "xlCmdTableCollection"
    Case Else
        DecodeCommandType = "[UNKNOWN]"
End Select


End Function

Function DecodeConnectionType(Incoming As Excel.XlConnectionType) As String
'============================================================================================================================
'
'
'   Author      :       John Greenan
'   Email       :       john.greenan@alignment-systems.com
'   Company     :       Alignment Systems Limited
'   Date        :       6th April 2014
'
'   Purpose     :       Matching Engine in Excel VBA for Alignment Systems Limited
'
'   References  :       See VB Module FL for list extracted from VBE
'   References  :
'============================================================================================================================

Select Case Incoming
    Case Excel.XlConnectionType.xlConnectionTypeDATAFEED
        DecodeConnectionType = "DATAFEED"
    Case Excel.XlConnectionType.xlConnectionTypeMODEL
        DecodeConnectionType = "MODEL"
    Case Excel.XlConnectionType.xlConnectionTypeNOSOURCE
        DecodeConnectionType = "NOSOURCE"
    Case Excel.XlConnectionType.xlConnectionTypeODBC
        DecodeConnectionType = "ODBC"
    Case Excel.XlConnectionType.xlConnectionTypeOLEDB
        DecodeConnectionType = "OLEDB"
    Case Excel.XlConnectionType.xlConnectionTypeTEXT
        DecodeConnectionType = "TEXT"
    Case Excel.XlConnectionType.xlConnectionTypeWEB
        DecodeConnectionType = "WEB"
    Case Excel.XlConnectionType.xlConnectionTypeWORKSHEET
        DecodeConnectionType = "WORKSHEET"
    Case Excel.XlConnectionType.xlConnectionTypeXMLMAP
        DecodeConnectionType = ""
    Case Else
        DecodeConnectionType = "[UNKNOWN]"
End Select

End Function


Function EntryPointGetConnections()
'============================================================================================================================
'
'
'   Author      :       John Greenan
'   Email       :       john.greenan@alignment-systems.com
'   Company     :       Alignment Systems Limited
'   Date        :       6th April 2014
'
'   Purpose     :       Matching Engine in Excel VBA for Alignment Systems Limited
'
'   References  :       See VB Module FL for list extracted from VBE
'   References  :
'============================================================================================================================
Dim oWorkbook As Excel.Workbook
Dim oWorkbookConnection As Excel.WorkbookConnection
Dim oODBCConnection As Excel.ODBCConnection
Dim oOLEDBConnection As Excel.OLEDBConnection
Dim strConnectionSpecificString As String

Set oWorkbook = ThisWorkbook

For Each oWorkbookConnection In oWorkbook.Connections
    If oWorkbookConnection.Type = xlConnectionTypeODBC Then
        Set oODBCConnection = oWorkbookConnection.ODBCConnection
        With oODBCConnection
            strConnectionSpecificString = "[" & DecodeConnectionType(oWorkbookConnection.Type) & "] Command=[" & .CommandText & "] Connection=[" & .Connection & "] SourceConnectionFile=[" & .SourceConnectionFile & "] CommandType=[" & DecodeConnectionType(.CommandType) & "]"
        End With
    ElseIf oWorkbookConnection.Type = xlConnectionTypeOLEDB Then
        Set oOLEDBConnection = oWorkbookConnection.OLEDBConnection
        With oOLEDBConnection
            strConnectionSpecificString = "[" & DecodeConnectionType(oWorkbookConnection.Type) & "] Command=[" & .CommandText & "] Connection=[" & .Connection & "] SourceConnectionFile=[" & .SourceConnectionFile & "] CommandType=[" & DecodeConnectionType(.CommandType) & "]"
        End With
    ElseIf oWorkbookConnection.Type = xlConnectionTypeTEXT Then
        With oWorkbookConnection
            strConnectionSpecificString = "[" & DecodeConnectionType(oWorkbookConnection.Type) & "] Command=[" & .TextConnection.Connection & "]"
        End With
    End If
    Debug.Print strConnectionSpecificString & "[" & oWorkbookConnection.Description & "] Type=[" & DecodeCommandType(oWorkbookConnection.Type) & "]"
Next
End Function