Chat with us, powered by LiveChat
  • 646-205-3261
  • Mon - Fri | 9:00AM - 6:00PM
  • 646-205-3261
Should you integrate and automate Microsoft Excel and Access?
22 May
Share Button

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.

Click image for larger version. 

Name:	vbaexpress-integration-automation-spreadsheet.jpg 
Views:	7 
Size:	20.4 KB 
ID:	19125

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”
Option Explicit
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)
End Sub
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”)
With adoConn
.CursorLocation = adUseClient
.ConnectionTimeout = 0
.ConnectionString = sConnector
End With
If adoConn.State = adStateClosed Then
End If
End If
End Sub
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 & “‘”
Call sOpenGenericConnection(sDBName)
Set adoRS = New ADODB.Recordset
With adoRS
.ActiveConnection = adoConn
.Open strSQL
If Not .EOF Then
For j = 0 To .Fields.Count – 1
rOutputRange(j + 1, 1).Value = .Fields(j).Name
Next j
rOutputRange(2, 1).CopyFromRecordset adoRS
End If
End With
Set adoRS = Nothing
Set adoConn = Nothing
End Sub

Source: VBA Express

Share Button

Leave A Comments

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