If you are like the majority of users of Vbaexpress.Com, you work with Microsoft Excel. At any moment in time there are far more people in the Excel forum on this site than all other forums combined, and for good reason, the world runs on Microsoft Excel. Excel is the most common and most used desktop productivity tool.
At least half the people in the Excel forum are here to learn. They may want to know what a Vlookup is and how they are to best use it, or they are here to learn how to extract online data via Excel VBA. It does not really matter what they are here to learn, there is an amazing group of dedicated individuals answering questions and sharing the knowledge they have acquired over the years/decades. Many of those experts are some of the sharpest minds in the Microsoft applications. Some are Microsoft MVPs, some are authors, many run websites and the list goes on and on. The nice thing is they are here and they have helped millions over the years.
Many of the people that work in Microsoft Excel do so at the professional level, meaning they use it to provide themselves an income. That does not necessarily mean that they are full-time Excel contractors, trainers, or authors. They may work in a finance department as a financial analyst. They make work for Microsoft on the Excel team, writing help files, or they may work for an online retailer. What it does mean is that they do work with Excel on a regular basis in some capacity to do the work they are paid for.
The days where a financial analyst works strictly with Excel and not with any of the other Microsoft data driven applications are more and more becoming a thing of the past. Why is that? Well, one reason is what people in the field call BIG data. The amount of data that people work with has increased over the years, as you would probably have expected. So for many of us that work in Excel that means Excel is housing more data than ever before. True, Excel will now allow you to have over one million rows, but how many of us are able to get Excel to work properly with half that? If you have 500,000 rows, 100 columns, and each cell having complex formulas what happens to Excel when you try to open it, when you try to update it, and when you want it to calculate? If you have not tried it I will tell you what happens, you wait and you wait. And if you are lucky, after a whole lot of waiting you have a workbook that did what was asked. But should it be that way? Not at all, and that is the reason for this post, to show you that there is a better way, a way taken by millions of people around the globe. That way is to integrate Microsoft Excel with Microsoft Access.
Click here to read about our Integrating Excel and Access solutions.
Many of those that integrate Excel and Access use Excel VBA Code that is similar to the code below.
This was provided by one of our members out of the UK.
Attribute VB_Name = “mSample”
Public adoConn As ADODB.Connection
Public Sub sRunAll()
Dim sDBName As String
Dim sFilter1 As String
Dim rOutputRange As Range
sDBName = Range(“rDBName”).Value
sFilter1 = Range(“rFilter”).Value
Set rOutputRange = Range(“rDataHere”).Cells
Call SbReturnDatafromDB(sDBName, sFilter1, rOutputRange)
Public Sub sOpenGenericConnection(sDBName As String)
Dim sConnector As String
sConnector = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & sDBName
If adoConn Is Nothing Then
Set adoConn = CreateObject(“ADODB.connection”)
.CursorLocation = adUseClient
.ConnectionTimeout = 0
.ConnectionString = sConnector
If adoConn.State = adStateClosed Then
Public Sub SbReturnDatafromDB(sDBName As String, sFilter1 As String, rOutputRange As Range)
Dim adoRS As ADODB.Recordset
Dim strSQL As String
Dim j As Long
On Error GoTo errout
strSQL = “Select * from [tblNames] where [NameType] = ‘” & sFilter1 & “‘”
Set adoRS = New ADODB.Recordset
.ActiveConnection = adoConn
If Not .EOF Then
For j = 0 To .Fields.Count – 1
rOutputRange(j + 1, 1).Value = .Fields(j).Name
rOutputRange(2, 1).CopyFromRecordset adoRS
Set adoRS = Nothing
Set adoConn = Nothing
Source: VBA Express