One way to load data into an Excel worksheet is to simply use “Get Data” option from Data tab. But it’s a manual process. In this article, let us look at another way to load data using VBA with ADO as the interface.
ADO stands for “ActiveX Data Objects”, the reason for using ADO in excel is that it acts as a common interface to get data from multiple sources. The main use of ADO is to unite all other data sources on to a single platform. If there was no ADO, then we would end up writing individual code for each data source.
‘ADO’ is not a default object added into the Excel VBA library. So, we must add it by navigating as follows: Tools -> references -> Microsoft ActiveX Data Objects 6.1 Library. Look at the below image for reference.
Let us write the VBA code using ADO object to load data from MS Access database. In this article we would be using “CarDetails” (MS Access Table) as the source DB table. Refer below snap to know more in detail.
The below code will fetch the data from MS Access database and loads into the excel file. Let us look at the loaded data in the below image. You can customize the query and the source depending on the required data. Indeed, you can get data from any source using ADO interface without any glitch.
Step 1: First step is to declare the new ADO object as:
“New ADODB.Connection”
Step 2: Open the connection by providing the connection string. In this article let us consider MS Access connection string
“Provider=Microsoft.ACE.OLEDB.12.0;”&”Data Source=”Location of the MS Access file;”
Step 3: Create a string object to write a SQL query
Dim query As String
query = “select * from CarDetails”
Step 4: Create a ‘New’ Recordset to open the connection and execute the query. Refer below code to get an idea
Dim rs As New ADODB.Recordset
rs.Open query,Conn
Step 5: Select the destination location to load the data. In this article, we have considered Excel as the destination file. So, select the specific range in the excel to populate the data
Sheet1.Range(“A1:F1”).Value = Array(“ID”, “Car name”, “Manufacturer”, “year of manufacture”, “BS Version”, “Price”)
Sheet1.Range(“A2”).CopyFromRecordset rs
Step 6: Final step is to close the connection
Conn.Close
Check out our Tableau Expertise Related Pages
-
- Tableau Expert San Francisco, CA
- Tableau Expert San Jose, CA
- Tableau Expert Los Angeles, CA
- Tableau Expert San Diego, CA
- Tableau Expert Sacramento, CA
- Tableau Expert Dallas Fort Worth, TX
- Tableau Expert Austin, TX
- Tableau Expert San Antonio, TX
- Tableau Expert Houston, TX
- Tableau Expert Norwalk, CT
- Tableau Expert Chicago, IL
- Tableau Expert Atlanta, GA
- Tableau Expert Philadelphia, PA
- Tableau Expert Pittsburgh, PA
- Tableau Expert Phoenix, AZ
- Tableau Expert Boise, ID
- Tableau Expert New York, NY
- Tableau Expert Rochester, NY
- Tableau Expert Jersey City, NJ
- Tableau Expert Washington, DC
- Tableau Expert Charlotte, NC
- Tableau Expert Seattle, WA
- Tableau Expert Miami, FL
- Tableau Expert Boston, MA