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
300-101 400-101 300-320 300-070 300-206 200-310 300-135 300-208 810-403 400-050 640-916 642-997 300-209 400-201 200-355 352-001 642-999 350-080 MB2-712 400-051 C2150-606 1Z0-434 1Z0-146 C2090-919 C9560-655 642-64 100-101 CQE CSSLP 200-125 210-060 210-065 210-260 220-801 220-802 220-901 220-902 2V0-620 2V0-621 2V0-621D 300-075 300-115 AWS-SYSOPS 640-692 640-911 1Z0-144 1z0-434 1Z0-803 1Z0-804 000-089 000-105 70-246 70-270 70-346 70-347 70-410