Create a Table Of Contents With Hyperlinks In Excel Using VBA
October 12, 2015
Difference Between Early Binding & Late Binding
October 9, 2015
Using ADO To Communicate With SQL Database
August 31, 2015
Update All Pivot Table Report Filters Simultaneously With The Same Date
Invoke WebService With Parameter Using Excel VBA (XMLHTTP Post)
July 1, 2015
Call SQL Stored Procedure With 2 Parameters Using VBA
June 24, 2015
Unhide All Hidden Worksheets At Once
February 12, 2015
Reset All Slicers At Once
Macro To Add Transparent Rectangle On Active Cell
January 11, 2015
Enable Developer Tab
December 27, 2014
If you want to be Bob Munden at Excel, use the Keyboard.. As simple as that!
If you observe most of the people that are good with Excel, you would har...
25 Excel Short Keys To Improve Speed
May 2, 2014
ADO stands for ActiveX Data Objects, which is used to communicate with different sorts of databases.
Here in the below examples, we are running a basic SQL Select statement and returning the data back to a Worksheet
We will use the Early Binding Technique in the first example.
If you are keen on knowing the difference between Early Binding & Late Binding Technique, click HERE!
In order for the Early Binding Technique to work, you will first have to set a reference to the Microsoft ActiveX Data Objects Library.
In order to do that, go to the Visual Basic Editor > Tools > Reference > Select Microsoft ActiveX Data Objects Library (Whichever is the latest one available)
Inser a regular code module and use the below code. Make the necessary changes to the connection string and it should work just fine
Sub ADOEarlyBinding() Dim Conn As ADODB.Connection, RS As ADODB.Recordset, SQLQuery As String
Const ConnectionString As String = "PROVIDER=SQLOLEDB;DATA SOURCE=192.168.2.222\SQLInstanceName;INITIAL CATALOG=MainDB; User Id=User1; Password=YourPassword;" Set Conn = New ADODB.Connection Set RS = New ADODB.Recordset SQLQuery = "Select * from MyTable" Conn.Open ConnectionString With RS .ActiveConnection = Conn .Source = SQLQuery .LockType = adLockReadOnly .CursorType = adOpenForwardOnly .Open End With Range("A1").CopyFromRecordset RS RS.Close Conn.Close End Sub
Generally, Early Binding is a better selection as it has some advantages over the Late Binding Technique, but your Early Bound code will fail, if the reference is not set, or set incorrectly.
I prefer to use the Late Binding Technique only If I am going to send the file to multiple users, or to someone who is not well versed with VBA to avoid any problems.
Changing an Early Bound Code to a Late Bound Code is pretty easy.
All you have to do is dimension you Variables using the generic Object Type, and use the CreateObject Function to create new instances of the Object.
You will also have to change the Constants to their Values.
Your Late Bound Code should look like this -
Sub ADOLateBinding() Dim Conn As Object, RS As Object, SQLQuery As String
Const ConnectionString As String = "PROVIDER=SQLOLEDB;DATA SOURCE=192.168.2.222\SQLInstanceName;INITIAL CATALOG=MainDB; User Id=User1; Password=YourPassword;" Set Conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") SQLQuery = "Select * from MyTable" Conn.Open ConnectionString With RS .ActiveConnection = Conn .Source = SQLQuery .LockType = 1 .CursorType = 0 .Open End With Range("A1").CopyFromRecordset RS RS.Close Conn.Close End Sub
This code will happily work without setting a reference to the Microsoft ActiveX Data Objects Library
Hope it helps! :)
Office, Excel, Microsoft, Microsoft 2010, Excel 2013
Stored Procedure In VBA
Database Connectivity Using VBA
Early Binding Vs a Late Binding
Early Bound Code Vs a Late Bound Code
MS SOAP Toolkit
Microsoft's Component Object Model
Pivot Table Report Filters
What is Binding
data validation list
drop down list
drop down list excel
drop down menu
excel short cuts
excel short keys
macro to add rectangle
table of content excel