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.

A Niche Of My Own

As I was doing some research for what kind of information the internet has about Excel and VBA, I have learned that many websites have already been giving the kind of information that I had in mind when I started this weblog. This is a little bit frustrating, nevertheless challenging to come up with a niche of my own. I have decided to narrow down my objectives to provide specific example of business solutions using Excel and/or Excel VBA. In my future articles, I will be posting basic Excel and VBA functions, and some articles will tackle combinations of these functions to develop or solve a certain business or financial solutions, real examples to solve your number-crunching problems.

Business solutions means vital information is needed to be dug up from a haystack of data residing in your DBMS servers. I know that some of you would say that most of these DBMS, like Oracle, have their own report builder and there’s no need for Excel, thus there’s no need for this weblog. It is correct that reports can be done using the DBMS report builder. But for ad-hoc reports that you need to provide at once, I don’t think that you have the time to ask for your IT guys to create one for you immediately especially when officer approval is needed for your new-report request. Excel comes in handy with this kind of situation. As long as you have the resource to have an extracted data file from the DBMS server or you have an authority to extract them yourself, you can create the report that your boss needs and fast.

Knowing to generate information from a raw data is the skill you need to link business and technology.

To all my readers, this Excel VBA weblog is your BUSINESS TOOL. Enjoy!

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.

Blog for MS Excel VBA Programming

This blog is all about Microsoft Excel Visual Basic for Application. Excel has been around for more than a decade and many businesses have used it as a tool to do business and financial computation. During my 5 years of Excel macro programming, I have seen companies that have problems in customizing their Excel workbooks and they came to me for consultation. This blog covers knowledge I gained during those years. My objective is to help those who are looking for ideas or sample codes to complete their programming tasks.

I am not claiming that I know everything about Excel VBA. There are still lots of things that I don’t know about it. In my future posting, if you see errors or incomplete codes, please feel free to inform and correct me.

In the future, I will include in this blog links of different VBA websites that I will find in the internet. If you already know existing links about Excel VBA, I will appreciate if you give it to me and I will include it to my list.

With the help of the readers of this blog, they will eventually find myriad of information resources and it will give quickest tricks to get them out the monotonous work of analysis and make macro programming fun and easy.

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.