Ersatz - "Share" a single ADODB connection across multiple worksheets ... e.g.:
Option Explicit
Sub connection_across_sheets()
Dim strConn As String, strSQL As String
Dim wrkshts As Variant
Dim cnn As Object, rs As Object
strConn = "Driver={ODBC Driver 13 for SQL Server};Server=WWWJJJ; Database=; UID=MyUser; PWD=ThePassword"
strSQL = "SELECT * From ..." ', etc. - Your SQL
wrkshts = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cnn.Open strConn
rs.Open strSQL, cnn
Sheets("Sheet1").Range("A1").CopyFromRecordset rs
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Sheets(wrkshts).FillAcrossSheets Sheets("Sheet1").Range("A1").CurrentRegion
End Sub
Bookmarks