Excel has two sets of help group files. When you press the F1 key while you are at the Excel workbook, you will get the “Excel Help” that has the help file for Excel’s workbook functions, formulas, navigation, etc.
At the VBA Editor, when you press F1, you will have the “Excel VBA Help”. This help file is very useful when you are programming Excel Macros. It has programming samples and explanation on how to accomplish a certain coding task. I often use this Excel built-in help files and it saves me most of the time.
Microsoft ActiveX Data Object (ADO) is a Windows 2000 (or later) component for accessing databases. It doesn’t need to be distributed because it belongs to its core services.
ADO has the object name “Microsoft ActiveX Data Objects 2.x Library”. The “x” refers to the version number and it varies on every machine. Loading this, please see Referencing Object discussion.
The first step to making use of the ADO is connecting to the database. You need to have the correct “Connection String” prior to connecting to the database. If you don’t know exactly what Connection String you have to use, you can use the Connection String builder I discussed in Building Database Connection String Programmatically. Once you have your Connection String, you can store it in a variable or you can save it in worksheet cell for future use with your program.
Below is a sample code on how to make connection to the database:
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.mdb;"
If your Connection String is in a cell in a worksheet, for example, it is in Cell A1 of Sheet1, the Connection String in the code is can be replaced with the following:
cn.Open Sheets(“Sheet1”).Cell(1,1)
or,
cn.Open Sheets(“Sheet1”).Range(“A1”)
Once connected to the database, you can now do the data manipulation. You can create your own table and columns. You can add, edit or delete a record to the database.
In my future posting, I will include the following topics to help you in learning ADO in details:
3 Main Objects of ADO
- What is a Recordset?
- What is a Cursor?
- Executing a Command with ADO
- Retrieving and Manipulating Data with ADO
- ADO – Open Method
- ADO – Close Method
- Updating Database using ADO
The following code is a procedure to create an Access Database file with the use of DAO component:
Sub CreateDB(vF As String)
Dim wrkDB As Workspace
Dim dbNew As Database
Set wrkDB = CreateWorkspace("",Application.UserName, "")
If Dir(vF) <> "" Then Exit Sub
Set dbNew = wrkDB.CreateDatabase(vF, _
dbLangGeneral, dbEncrypt)
dbNew.Close
End Sub
DAO is an old Windows Component to manipulate database. The code above is one good reason to use DAO even if ADO is already available with Windows. I am not sure if there is a way to create an Access-file using ADO, I have researched the internet and I have tried so many times to do it using ADO but I failed. So I stick with DAO to do the trick. If you know a way to do it with ADO, please let me know. It will help a lot of people out there.
In manipulating the database like creating tables and columns, adding, editing and deleting records, I recommend using the ADO instead of the DAO. It is easier. To know more about ADO, please read the topic: Microsoft ActiveX Data Objects (ADO)
Situation:
In a few days from now, I will be doing a new Excel VBA project for a more-than-2-year-old client. The new project will require me to produce pivot-table reports from an external data source. This kind of requirement is usually easy because Excel PivotTable already supports external source of data. But the problem with this project, they are requiring me to come up with an Excel VBA program that be used to connect with different database platform. As I was thinking about the solution of this problem, I’ve posted to different Excel VBA forum to seek for help. No one has given me the complete answer but while I was reviewing their suggestions, I tried to put them together and came up with the correct solution. Truly forums are very helpful. Below is the sample source code of the database connection string builder that can provide different OLEDB connectivity programmatically:
Solution:
'PROCEDURE TO GET CONNECTION STRING USING DataLinks
Function GetConnectionString() As String
Dim objLink As New MSDASC.DataLinks
Dim strConnectionString As String
strConnectionString = ""
On Error GoTo LinkErr
strConnectionString = objLink.PromptNew
LinkErr:
GetConnectionString = strConnectionString
End Function
'PROCEDURE TO TEST THE FUNCTION ABOVE
Sub Test()
Dim strCN As String
strCN = GetConnectionString
If strCN <> "" Then
MsgBox strCN
End If
End Sub
The heart of the code is the GetConnectionString procedure. It uses the MSDASC.DataLinks object. But before you can use this code you need to load 2 object files from Microsoft. They are:
1.) Microsoft ActiveX Data Objects 2.5 Library (or later version)
2.) Microsoft OLE DB Service Component 1.0 Type Library
See: Referencing Objects
When MSDASC.DataLinks generates a connection string, it stores the value in strConnectionString variable and eventually, it is the value to be returned by the GetConnectionString function.
Situation:
When you receive an error message about a missing object, sometimes the culprit is not the VBA program itself. Most of the time, this kind of situation is due to improper referencing of the MS Excel to required object file. To correct this problem, there are two things that you can do:
Solutions:
1.) The required object is already loaded in the OS memory but Excel referencing is not established. To do that, you have to run the VBAProjects References by clicking Tools>References on the VBA Editor, as shown below:
Once clicked, the Reference Window will appear (please see below) and you will see choices of objects that you can check to be used in your application development. Some of the objects are already checked. Please do not uncheck any of those because it might bring about an unwanted behavior on MS Excel and can corrupt your file.
Let’s go back to the original situation. To get rid of the missing object error, you have to check the object that your program is looking for from the list of objects references. If the object is not in the list do number 2 below.
2.) In this approach, an object file is needed to be uploaded in the OS memory and must be recorded to the Windows registry. First thing to do is locate the path of the object file and register it by using the “regsvr32” program. The “regsvr32” program is already included in the Windows installation. To register, click Start>Run and enter the following:
Regsvr32 [path][object file]
When object file is registered successfully, do the steps in number 1.
This is the code to find a value in a worksheet or selected group of cells. This code can only highlight the value when found.Dim strValueToFind As String
strValue = “excel vba”
Selection.Find(What:=strValue, After:=ActiveCell, _
Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, matchCase:=False, _
SearchFormat:=False).Activate
To store the row location of the found item, use the following syntax:Dim intRow As Integer
Dim strValueToFind As String
strValue = “excel vba”
intRow = 0
‘You need to zero out the integer variable first so that when the item is not found it remains zero
intRow = Selection.Find(What:=strValue, After:=ActiveCell, _
Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, matchCase:=False, _
SearchFormat:=False).Row
If intRow = 0 thenMsgBox “Item Not Found”
ElseMsgBox “Item is located at Row: “ & intRow
End if
For the column location:Dim intColumn As Integer
Dim strValueToFind As String
strValue = “excel vba”
intColumn = 0
‘You need to zero out the integer variable first so that when the item is not found it remains zero
intColumn = Selection.Find(What:=strValue, After:=ActiveCell, _
Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, matchCase:=False, _
SearchFormat:=False).Column
If intColumn = 0 thenMsgBox “Item Not Found”
ElseMsgBox “Item is located at Column: “ & intColumn
End if
Variable Declaration’s general syntax is:
Dim VariableName As DataType
We can place more than one declaration on a line to save space. For instance, the following line
declares three variables:
Dim strFullName As String, dtBirthday As Date, byteMonth As Byte
Declaration of a variable is actually defining its data type. Variables are commonly declared with the DIM keyword. Other type of keywords are also used but it will be tackled in my future posting. Examples of variable declaration are as follows:
Dim strFullName As String
Dim dtBirthday As Date
Dim byteMonth As Byte
Dim intAge As Integer
Dim boolIsMarried As Boolean
Dim sglHeight As Single
Dim curMoney As Currency
Dim wbkPayables As Workbook
Dim chSales As Chart
VBA treats a variable as Variant whenever it is used without first declaring it. Or data type is not mention when declaring it, as in:
Dim IsMarried
Declaring a variable without a data type is not a good idea because it is a waste of memory. Waste of memory because the variable will use the memory size requirement for a Variant data type which is 16 bytes. Using the example above, if you intend to use the variable as a storage for a Boolean value but fail to declare it, the variable will use 16 bytes instead of 2 bytes. Fourteen (14) bytes are wasted. In a large program that is using hundreds of variant variables, memory waste is significant. Moreover, maintaining a Variant variable involves more overhead compare to specifically declared variable. Using Variant variable will result in bad performance of your program.