Data Source Is Out Of Control

Have you experienced to create an MS Excel reports wherein the source of data is a .CSV file that is extracted from an Oracle system? If yes, then maybe you have also experienced the situation that I have been through.

A few months ago, I closed a contract to develop Purchase Order (PO) performance reports and Sales Order (SO) performance reports with one of my old clients. They looked very simple at first because there was no need for me to create data entry modules for the program. The source of the data would be coming from their Oracle system. Records would be entered from different countries across Asia Pacific and stored them into one central database server. Weekly, they would extract the records from it and convert it to Comma Separated file or .CSV file. My Macro program would import those contents of that .CSV file to my worksheets and somehow needed to consolidate them for reports and analysis purposes.

At first, everything went smoothly. One day, the project liaison called and told me that there have been errors in almost all the reports. She sent me a copy of the macro file and source data file to check on the problems. As I was checking the reports of the Excel file one-by-one, reports were devastated. I investigated for hours all the macro modules but I hadn’t seen any clue of what happened to them. So, I asked myself; “How come a program in a good running condition suddenly gives incorrect figures in almost all reports?” The answer is actually right under my nose. When I checked the records in details, I saw the null values of all the Date Fields like “Actual Shipment Date”, etc. were changed. Originally, if the null date field was converted to .CSV file, the content was “NULL”. It was changed to dash (“-“) and eventually would give incorrect values in reports. To correct the problem, I spent days to modify the program modules and worksheet cell formulas that are related to all the date fields. Whew! It was tedious.

That was a learning experience for me. I didn’t see that coming and hadn’t prepared for it. Partly, it was my fault because I presumed that the records format was final and didn’t realize that the data-entry program and data-conversion program were not mine and it could be changed anytime.

For that particular reason, I requested the project liaison to collaborate with their Oracle programmer to advice us if records structures were altered.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Number Rounding Off Issue

Three days ago, I was facing a difficult situation. A client of mine was asking me to make percentage-items of a report to sum up exactly to 100%. To visualize what I’m talking about, look at the example of figures below:

# of items ------------->%
7 -------------------------->58.33%
1 -------------------------->8.33%
3 -------------------------->25%
1 -------------------------->8.33%
-----------------------------------------------------------
12 -------------------------->99.99% <------TOTAL

The example above looks incorrect when you add all the percentage items as they are. The percentage total would be 99.99% and not 100%. It is even more incorrect if all percentage items are rounded off to the nearest one. See below:

# of items ------------->%
7 -------------------------->58%
1 -------------------------->8%
3 -------------------------->25%
1 -------------------------->8%
---------------------------------------------------
12 -------------------------->99% <------TOTAL


The total became 99%. But when you put the same calculation to Microsoft Excel, it would display a different result. Please create a New Worksheet in MS Excel and enter the following:

Cell A1 = 7
Cell A2 = 1
Cell A3 = 3
Cell A4 =1
Cell A5 =SUM(A1:A4)

Cell B1 = A1/$A$5

Copy formula of B1 to B3-B4 and copy A5 to B5. Format the entire column B to “Percentage” with zero decimal places. When done, you would see the following result:

# of items ------------->%
7 -------------------------->58%
1 -------------------------->8%
3 -------------------------->25%
1 -------------------------->8%
----------------------------------------------------
12 -------------------------->100% <------TOTAL

The result as you have seen is very interesting. Microsoft treated the value of each individual percentage-item as the number of its formula result and not as what is displayed. This is why the total percentage is 100% and not 99%. In general practices, the MS Excel computation is acceptable to most people and they consider the difference negligible. But there are few people who disagree with this kind of result and consider it unlikely and incorrect to present this type of calculation. Truly, the situation is really a matter of personal point of view and consideration of the person going on this kind of situation.

To satisfy those few people who prefer to have each percentage item to add up to exactly 100%, a VBA script is can be created to do the trick. However, before I present to you the solution, I would like to reiterate that the idea is a matter of preference and most likely debatable to most people because they prefer the Microsoft Excel way. It is correct to some people and the rest of them think otherwise.

Run the script below:



Sub ComputePercentage()

Dim i As Integer
Dim x As Integer
Dim Tot As Single
i = 1
'ROUTINE TO GET THE ROW LOCATION OF THE PERCENTAGE TOTAL
'AFTER THE LOOP i HAS THE ROW NUMBER
Do While Not Cells(i, 2).Formula Like "=SUM*"

i = i + 1

Loop
'COMPUTE THE PERCENTAGES
Tot = 0
For x = 1 To i - 1

If x <> Cells(x, 2) = Round(Cells(x, 1) / Cells(i, 1), 4)

Tot = Tot + Cells(x, 2)

Else

Cells(x, 2) = 1 - Tot

End If

Next x

End Sub

By the way, before you run the program, format the Column B to Percentage with 2 decimal places. When you run the program above, it would give you the following result.

# of items ------------->%
7 -------------------------->58.33%
1 -------------------------->8.33%
3 -------------------------->25.00%
1 -------------------------->8.34%
---------------------------------------------------------
12 -------------------------->100% <------TOTAL

Notice that the last percent number is slightly incorrect. When you divide 1 with 12 the quotient is 8.333333… I will say it again, to those people who prefer it this way, they might consider the inaccuracy of the last number as so small as to be neglected.

It is for you to decide (or your client) which one to use.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Creating Tables and Columns at Run Time

The following code is an example of creating a Table and Columns using ActiveX Data Objects (ADO):

Sub CreateEmployee()

Dim cn As ADODB.Connection
Dim strSQL As String
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=c:\test.mdb;"
cn.Open
strSQL = "CREATE TABLE `Employee` (`Last Name` varchar(30), `First Name` varchar(30), `Age` integer)"
cn.Execute strSQL
cn.Close

End Sub

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Three Main Objects of ADO

Connection Object

This object is the one used to connect to database, which is then used to execute commands against the database or retrieve a Recordset. The object has the ConnectionString property that is used to specify the database you want to connect to. The Open method establishes the database connection. The Close method releases the connection and the memory used by the object.

The Connection String can be generated at run time using the DataLinks. Please see the topic Building Database Connection String Programmatically.


Connection String Attributes:

Provider – The provider name or driver name that ADO will use to access the database.

User ID – When needed, it is used by the provider to establish the proper rights for accessing the database.

Password – To validate the user, the user’s password is needed by the provider.

Integrated Security – Setting this value to SSPI, the driver will use the Windows NT Integrated
Security. User ID and Password are not used when this is done.

Data Source – If you are connecting to an Access database, this should be set to the file and complete path of the file. This should have the machine name when connecting SQL Server.

Initial Catalog – This attribute should have the database name when connecting to SQL Server.

Recordset Object

This object is important because it is used to manipulate databases. It is a memory allocation in a client computer or server that has a set of rows of one table or more of the database. With Recordset, you can add, edit, delete and update records of the actual table in the database.


Command Object

This object has the information about the command to be executed. The command object could have a Query (SQL) command, Table, View or Stored Procedure.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.

Creating MS Access Database File using DAO

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)

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.