How to name ADO connection with VBA? Using ADO method of the following VBA code adds new connection which is named by default "Connection" or "Connection2" etc.
Sub Make_ADO_Connection()
Sheets(2).Cells.ClearContents
Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String
strFile = ThisWorkbook.FullName
ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Debug.Print ConnString
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open
SQL = "select asdf1,asdf2,asdf5 from [Arkusz1$] where asdf1 is not null"
Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open
'-------------------
'Here new connection is established with a name "Connection", I want to use a different name for it.
Set objListObject = ActiveWorkbook.Sheets(2).ListObjects.Add(SourceType:=xlSrcExternal, _
Source:=oRS, LinkSource:=True, _
TableStyleName:=xlGuess, Destination:=Sheets(2).Range("A1"))
objListObject.Refresh
If oRS.State <> adStateClosed Then
oRS.Close
End If
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing
End Sub
I would like to name new ADO connection while creating it i.e. "MyConnectionName" checking earlier if such a connection exists.
Edit. I can loop through the connections:
Dim conn As WorkbookConnection
For Each conn In ActiveWorkbook.Connections
Debug.Print conn.Name 'This name I want to set up
Next conn
I know that the connection with name "Connection" is created with this statements:
Set objListObject = ActiveWorkbook.Sheets(2).ListObjects.Add(SourceType:=xlSrcExternal, _
Source:=oRS, LinkSource:=True, _
TableStyleName:=xlGuess, Destination:=Sheets(2).Range("A1"))
objListObject.Refresh
See Question&Answers more detail:os