Accessing multiple data sources using ADO and VBA in Excel

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.

ActiveX Data Object model to connect excel

‘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.

ADO object 6.1 in excel VBA Library

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.

Table in MS Access Database to connect to excel

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.

MS Access table copied to Excel using ADO and VBA

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

MS Excel for Windows Vs Mac: Who wins the race?

Excel is one of the widely used spreadsheet applications created by Microsoft for Windows, MacOS, Android and iOS. In this article, we will be focusing on some of the major differences between Excel for Windows & Mac.

Power Pivot & Power Charts

The below table clearly shows the major differences in windows and MacOS versions of Excel.

Excel Windows and MAC differences

Static Charts can be depicted in Mac, but they are not interactive like Windows excel charts and they don’t update upon changing the source pivot table. Any pivot table having source data based on Excel data model will be unfilterable if the spreadsheet is opened by a Mac user. The following image shows an error message while opening a file in MacOS.

Pivot table error message in MacOS

VBA Editor User Interface

The VBA editor on Excel for Mac 2016 is in a sorry state compared to its windows counterpart.

  • ‘Properties’ window is missing
  • Developing a ‘User form’ in Excel for Mac by using design mode would be a tedious job compared to Excel for Windows

Importing VBA collections would be an added disadvantage

VBA Workbook and Worksheet events

If you are someone who uses worksheet events of VBA often, then you would be disappointed as there are no Workbook & Worksheet VBA events in Excel MacOS. Thus, you should rely completely on VBA modules only.

VBA Events in MAC pop error message

Default file location settings and Autosave workbooks

  • The windows version of excel enables you to set a default location for saving files. This setting is not available in Mac version of Excel
  • “AutoSave” is one of time saving features which automatically saves your workbook as a draft and enables you to retrieve older version of the file even if you didn’t save your changes. This feature too is no available in Mac version of Excel

Find and Replace formats

In windows, find and replace dialog has an option to find cells based on their formats (Say, background color) and it would replace the found cells of the same background. This is not available in Excel for Mac.

ActiveX Controls

ActiveX controls are intended to be used with VBA programming, but this feature is not available in Mac version of excel. Thus, Excel VBA pros are restricted to use Form controls in excel MacOS.

Relative references

Irrespective of operating system, relative references are very useful in creating dynamic range while recording macro. Windows allows user to record macros in both absolute and relative references, but Mac doesn’t have relative reference feature while recording the macro.

 

 

Check out our Tableau pages

XLOOKUP: A Brand-new formula in Office365

The wait is over!! Finally, Microsoft has released an update to Office365 with a brand-new formula “XLOOKUP”.

XLOOKUP has the following advantages, compared to its predecessors.

  • Can lookup on both left and right directions.
  • Exact match is default.
  • Can return FIRST or LAST match.
  • XLOOKUP also replaces VLOOKUP & HLOOKUP.

Earlier, INDEX-MATCH formula combination was used to lookup values where HLOOKUP & VLOOKUP fell short. Now, XLOOKUP can replace all other functions. It is a robust and easy to use formula which can lookup values from right to left and vice versa.

Syntax:

=XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])

Explanation

Let us consider the below example to get deep insight of the formula. Our agenda is to fill column D of Table1 (Salary) using XLOOKUP

Two distinct tables for Xlookup

Lookup_value: Exactly same as VLOOKUP. In this scenario, let us consider Column B of Table1 as our lookup value.

Lookup_Array: Typically, the destination range/column where XLOOKUP will look for the match. Column H of Table2 would be our lookup array.

Return_Array: This argument is asking for the return value. In this case, we want salary column to be filled so we would consider Column G of Table2 as the return value.

[match_mode]: This is an optional argument in XLOOKUP formula. It can be any one of the following. By default, it is “Exact Match”.

  • 0 – Exact match
  • -1 – Exact match or next smaller item
  • 1 – Exact match or next larger item
  • 2 – Wildcard character match.

[Search_mode]: It can be either 1 or -1. “1” is to search from first to last and “-1” is from last to first.

Let us write the below formula in cell “D3” of table1 and the output looks as below.

XLOOKUP($B$3:$B$9,$H$3:$H$9,$G$3:$G$9,0,1)

Xlookup output