On the need for conditional formatting

I just realized that these posts are getting a bit lengthy, so I'm considering setting up some conditional formatting to only display a preview of the posts on the main page. I'll have a link to the post pages so that you can read further.

Stay subscribed.

Mining data from 3,000 separate Excel files

One of the consulting projects I worked on was for the finished product exports department at a global consumer products company. The company produces products that get shipped all around the world, but this particular group concentrated on North America.

Because of the North American Free Trade Agreement (NAFTA), the company was able to save millions of dollars each year by taking advantage of tariff exemptions for products shipped among the U.S., Canada, and Mexico. Prior to my joining the project, the company had discovered from their legal department that they needed to provide documentation that these products were indeed exempt.

The regulations describe raw materials that compose the finished products, so we needed to look at the constituent level for every product the company manufactures. I was informed that the company had close to 3,000 Excel spreadsheets for various products that listed the composition of each. Additional information would be forthcoming in the near future after I had started on the project.

I looked at the Excel files and discovered that the composition was not in a flat layout (headings and rows), and thus, I wouldn't readily be able to import the information into a database table. I would need to manipulate the data into such a layout and then import it into a database.

Instead of going brute force into all 3000 of these files, I opted to automate the process. I decided to build a conversion tool in Excel that would search for the relevant contents of each file and copy and paste them into a new spreadsheet in a table layout. I wrote VBA code in a standalone Excel workbook to do the job. In addition, I also wrote code to record errors and other anomalies in a log.

I also wrote some VBA code in Microsoft Access to launch the Data Conversion tool in Excel, along with some use of the Microsoft Office object interface. I put all the directories where the files were located into a Lookup table and had the program go through each directory, performing the conversion on each Excel file, importing each Excel spreadsheet's information to a master database table.

I wound up being able to click a button and watch as the program performed the conversion. I had set up a flag in the Lookup table to indicate whether files were imported successfully. Then I would check the log in Excel for anomalies, handle them appropriately and retry.

I'll share more about this project in future updates.

Fixing the client's 401(k) reporting application to make it run better

The company didn't have a process in place to make sure that this one client receives their 401(k) information on a regular basis. I had developed an application last fall for this specific client to produce the information, but there was no one set up specifically to run the application on a regular basis.

I was contacted earlier today to set up this application on several people's computers. Interestingly enough, everyone could get to the application, since it exists on a networked drive, but, to make things as easily accessible as possible, I prefer to put a shortcut to the application on users' desktops.

After I finished that, I sent an email to everyone affected, letting them know about the application, and letting them know how to get the report.

About 15 minutes later, I was summoned to a user's computer because there was a problem with the report details. She was running the report for last week, but the Last Pay Date for several employees was for this week.

As I looked into the program, I realized that all the other details were correct except for this Last Pay Date, which gets updated whenever a payroll is run. I began brainstorming other ways to get the correct date.

One option was to look at the last pay check date for the date range specified, which is from the first of the month to the date the user selects on a form. That would make sure that date for employees who are getting paid this week is the date the user selected.

As I thought about that, I realized I was not finished. What if we run this report a month from now, and an employee had been terminated this month? The client would still want that employee to show up on the list.

I continued brainstorming, and wrote some mock VBA code on my marker board. Satisfied with that, I created a function, tested it out with various scenarios, and eventually came to a solution that will either use the last pay date for the reporting range OR the Last Pay Date field from the other data table.

I generated the report and sent it off to the client service representative, who will then forward it on to the client.

Helping 525 employees get paid

As you may have read before, I've written the bridge applications that prepare clients' payroll for a program we use to batch load into our payroll management system. Whenever I and now my coworker, whom I'm mentoring, finish one of these bridge applications, we update a master list of all the clients we've set up and email it to the other people in the organization that need it.

The person who normally runs the batch-loading program wasn't available today, because she's printing W-2's for all of our client employees. So, the payroll specialist contacted me, and I ran it for seven of our clients. I estimate that it pulled payroll transactions for about 525 employees.

I love being able to help out in a pinch!

Welcome 2000 Bloggers Readers!

I just found out that I'm 948th among a list that Tino is compiling of 2000 bloggers. It's a bit mind-boggling to look through all the pictures of bloggers and instantly recognize everyone's individuality even though we each blog. It's a crazy concept. The collage of photos is even being considered as a potential book cover. And, as of this writing, there's still time to add yourself to the group.

As for me, you can check out the blurbs about me in the profile section where I have delineated some of my super powers. All that and more is available on the sidebar.

Related tags: 2000 bloggers 2000bloggers daniel johnson jr

Revised payroll layout forces changes to bridge application

In an earlier post I described some changes to a client's payroll journal. In this one I'll share how I worked those changes into the corresponding bridge application we use to automate the payroll entry.

You will recall that I made two significant changes:

1. Added a new payroll item and
2. Removed totals columns

I will discuss each of these in turn, along with some other things I've discovered while working on this application.

New Payroll Item

The client wanted to be able to track Training, so I added it to their payroll journals. In the bridge application, I added the payroll item in the respective classes to recognize it.

Totals Columns

The client tracks both weeks of their pay period individually, and the payroll journal was originally set up to provide totals for each week and for both weeks. The totals columns for Week 1 were set up immediately after Week 1's payroll items. The totals columns for Week 2 and for both weeks were set up after Week 2's payroll items.

When I removed the totals columns, I moved all of Week 2's payroll items. The bridge application contains a VBA class where I've mapped the payroll item columns on the spreadsheet to elements of a tab-delimited array. I revised the code to make sure the each element is mapped appropriately.

Pitfalls while working off a network

Early Friday afternoon the network where I develop the applications became frozen, and I had to shut down the application from the Task Manager. Knowing this corrupts the application file, when I came in this morning, I started over again, but this time off a local copy, in case something would happen to the network again. Since I'd made the changes one time already, it didn't take long to do.

Testing it like the user would

When I tested the application to make sure the changes worked, the output looked odd. I contacted the payroll specialist and found out that she runs the bridge application for one location at a time, and after testing the application that way, everything worked out fine.

Client pleased by changes made to payroll journal

You might remember that I mentioned recently working with a client to answer questions about their payroll journal.

Last Friday I contacted them again to clarify some of the changes they had requested and provide some ideas to make it more useful to them. I had already provided an instruction worksheet when I initially designed the payroll journal. These instructions included how to add and delete employees, among other things.

The client wanted me to add a Training payroll item on the journal, stating that they would be paying their employees minimum wage during training sessions. Since this change would affect some of the calculated totals, I contacted them and asked if they still needed them. They replied that they didn't really need them, and, since we didn't really need them either, she said we could remove them.

I changed the layout on the spreadsheet for each payroll journal they use and sent them off to the client. I contacted them later to verify they received the new versions, and they really liked how they looked. The changes in effect made the entire journal look more legible, in their opinion.

In a separate post I'll share about how I've managed changes to the related bridge application we use to process this client's payroll.

Waiting for the internets

I just registered danieljohnsonjr.com to point here, and I'm waiting for the domain forwarding to propagate throughout all the internet.

Update: It looks like it works.

Client relationship strengthened with assistance on payroll journal

One of our clients called me to ask for help with a payroll journal we had set up for her. I enjoyed talking with her, since it had been several months since our last phone conversation. I asked her to email me the payroll journal she was working on so that I could see the problem as she saw it, since we're at different locations. She tends to be wary of making any changes herself, and I've often had the tremendous opportunity to help her out.

After looking at the payroll journal, I was able to help the client fix the problem she was having over the phone. We also talked about some additional changes her company wanted to make and what would be involved in getting them set up, including changes to the respective bridge application we use to automate the data entry.

After we ended our conversation, I went upstairs to talk with the payroll specialist about this new change - specifically, a new payroll item - will be implemented. It turns out that implementing this additional item will be relatively easy to do. She told me that we will wait until they payroll for the client is run this week to implement the changes to the payroll journal.

One hour of unexpected work saves another several hours of work!

Two hours ago... I got an instant message from our HR Manager. She's trying to balance out the 401(k) plans for 2005 and was missing information from a client who was put in a database with other clients who only opt to use us to do their payroll. The desktop application I had developed for her was not set up to get this information readily.

She wanted to know if putting together a spreadsheet manually with the information was her only option. I asked her to give me 30 minutes while I investigated what I could do. She told me that, yes, of course, the information was needed "yesterday".

I made a backup copy of the application and began to see if it was as simple as changing some of the queries. It looked as though that would be the case, so I contacted her and told her I'd be able to get her the information.

When I tried to tweak the queries, I wasn't able to get them to run appropriately. I decided to scrap the copy I was working on and start over, this time looking at the VBA code that generates the queries at runtime. A couple months ago, I had set the program up so that she could select a single client for which to obtain information. It turned out to be much simpler to revise that one section of code and then run the application as usual.

She said she needed the reports run for all 12 months in 2005, so after generating the files, I zipped them up and e-mailed the zip file - except that MS Outlook doesn't allow zip files to be sent as attachments. I "masked" the file name by changing the extension to "txt", and in the body of the message gave instructions to change the extension back after saving it.

I just received an instant message from her, "thank you DAN!!! You are the best".

What I was able to do in an hour saved her HOURS of time if she'd tried to do it herself. Being a superhero is great.

Confirming what we already know

This morning I spent some time with a coworker, going over some changes I had made to the bridge applications we worked on last week. I also showed him what I did to make the other trucking company's application work.

Afterward, he and I went upstairs to payroll to introduce him to the other payroll specialists, since he would be starting to work with them as well. We confirmed that a spreadsheet one of the specialists designed is going to be put into use by the client this week; so we'll be able to develop an application to handle that payroll, too.

We also found out, from the gleeful expression on another payroll specialist's face, that the bridge applications for the trucking companies went off without a hitch.

New bridge applications for trucking companies to significantly reduce payroll processing time

Whew! It's been a busy day. I spent some of my morning interviewing payroll specialists about potential client candidates for a bridge application. Over the next month or so, we'll be adding several new clients, so anything the IT group can do to better facilitate payroll entry has been greatly appreciated.

Some clients use the web-based system to enter their payroll, which automagically shows up in the system. Other clients, however, fax a timesheet, either handwritten or otherwise. We may want to look at some way to use OCR for some of these payrolls.

But there were a few good candidates, and I'll be working on the bridge applications for them in the next week.

Most of my day today has spent diagnosing the application my coworker and I were developing yesterday and building a similar one for another client. The clients are trucking companies, and they have close to 200 active employees, most of which have been manually entered up to now.

One client is organized in such a way that they only have drivers. It turned out that, for the bridge application we were working on yesterday, we'd done everything but assign the number values to the individual payroll items. After adding code to do that, it worked fine.

The other client is similar to the first one, but they also have hourly employees that they report on via a separate file. The challenge here was to be able to set the program up with the flexibility and sophistication to know which file it was looking at, and subsequently, which layout to use.

Setting that up was a bit more of a challenge and required more customization than I've had to do in awhile, but I figured out what needed to be done, and, after testing it on the development and production side, everything worked.

Whenever I've set one of these bridge applications up, I always sent out an email to the relevant people to let them know. They all have gone home for the weekend, so it will hopefully be a very welcoming message they receive when they return!

Mentoring a coworker while building the bridge application

Today I showed a coworker how I develop a payroll bridge application. We both went through a development checklist I'd developed, and he said it really helped him a lot to understand the code a lot better than having looked it over all by himself.

We added everything in and even took care of some of the program errors, even to the point of making the program ready for the user population to use. But on a hunch, I decided to look at the output/transaction file to verify it was all in order.

We found that the transactions were not added to the file, so we'll need to diagnose and fix it in the morning.

Once he gets up to speed in understanding how we build one of these applications, then he and I will be able to "divide and conquer" the next group of candidates.

Troubleshooting warnings to make bridge application run smoother

Little things. We ran the new bridge application I mentioned earlier this week for the client's payroll. When our integration manager program got a hold of the results, it returned warnings that were not at first easy to understand.

When I talked about it with the payroll specialist, she mentioned that two people were set up with a particular transaction code in the system, but the integration program didn't recognize it. There were also two warnings in the report stating that the transaction code was "truncated at 6".

I looked at the transaction file that the bridge application generated. Sure enough, the particular transaction code was 7 characters long. I mentioned that to the payroll specialist, who then looked up the correct transaction code and gave it to me.

She had already hand-keyed these three particular transactions and she told me the entire process of doing the payroll has gone from three hours down to just one hour. She's fired up!

Exposing SQL tables so that user can indicate 401(k) participation

A little bit more about the corporate 401(k) application, along with a discussion about how our SQL Server databases are set up.

We have clients set up that for different business reasons we've put into separate databases. We have a dedicated server for all the individual SQL server databases. As the company continues to grow, we've added new databases.

One of the requirements for 401(k) reporting involves looking at participation. When I first developed the application earlier last year, I set it up to define participation as when an employee of a client contributes to the 401(k). I found out that many clients sign a participation agreement often a month or more before any of their employees start contributing. So I needed to change how the program defines participation.

To that end, we decided that it was best to have a single table in each database where we would indicate a client's participation. That way, we would look at all employees for those clients in those tables.

In order to expose these tables to the user, I needed to set up an ODBC link. I had to do this for each SQL Server database individually so that each 401(k) participation table would be exposed. Access provides this utility through its Linked Table Manager.

One thing I realized in doing this, is that the settings are machine-specific, which means that every machine using the functionality has to be set up individually. After doing this a couple of times, I documented the process for when we'd need to do it again.

So, once I'd established the ODBC links, I developed a form with a tab control. Each tab shows each table, and this makes it really easy for the user to maintain the participation information for the clients in the different databases.

In her own words, "have I told you how happy I am that you are back????" Nice.

All of this makes sense as I'm writing this, but maybe it doesn't as you're reading it. Let me know if that's the case.

Corporate 401(k) application now flexible to better handle year change

I'll probably want to share about the corporate 401(k) application I developed in further detail in a separate post, but I wanted to point out a much-needed change to the application that I've done. Once again, it's a Microsoft Access database, with VBA that interacts with SQL server using pass-through queries generated at runtime.

I'm sure I'll be discovering other types of applications affected by the year change; this is just one of them. The user requested that the application filter out employees who were terminated prior to the current year so that they do not show up on the report that we send to our 401(k) manager.

As I thought about the need, I realized that there may be a situation in which she'd want to run the report for the previous year, even for a single client. I therefore wanted to provide that sort of flexibility to the system.

The application's main form contains a Calendar control in which the user selects the Start and End Dates. I opted to use the year of the End Date in order to tell the system which employees to include on the report.

For example, if she wanted January 2007, she's select 1/1/07 and 1/31/07 on the form. The system would filter out employees who were terminated before 2007.

To accomplish this, I've added code that will generate the SQL at runtime, since the End Date is now part of the criteria.

Blogging and Podcasting

Readers of this blog may also know about other blogs I produce, but, just in case, here are the main ones available to the public:

• Get That Job!, a job hunting and career development resource;
  
• Journey Inside My Mind Blog, my oldest blog/journal;
• Journey Inside My Mind Podcast, a mostly music podcast, with some other fun surprises;
• Homeschooler Podcast, a 10-minute podcast about homeschooling;
• QuotesBlog, a repository for quotations
  

Master application wraps up all bridge applications for simplicity in access

I mentioned in an earlier post that I developed a bridge application to help facilitate payroll entry. I've actually developed several of these applications for different clients, based on the unique layouts of the individual payroll files.

We typically evaluate a candidate for such an application based on the following criteria:

• The client is not using our web-based method of submitting payroll
• The client has 40 or more active employees
• The client submits their payroll as an attachment via electronic mail

We have assigned people who normally run the payrolls for each client, and hence also run these applications. There are other occasions, however, when that payroll specialist is out sick or on vacation, that someone else needs to run the bridge application for that client.

To accomplish this, I created a "wrapper" application, a "master" application, in Microsoft Access, with a form containing a list of all the clients who have one of these applications. The payroll specialist double-clicks the client's name, and that simultaneously launches the individual bridge application and closes the master/wrapper application. Each payroll specialist has a shortcut to this application on their desktop.

As I develop new bridge applications, I simply add a record with the required information to the table in the the wrapper application.

Client managing own 401(k) needed custom application

We have some clients that use someone else to manage their 401(k) program. Still, they need the financial information from our system. For us this means that we generate a report with the customized fields.

To get this report, I've first had to figure out the SQL needed to query our SQL Server database. Then I've needed to figure out how to generate this SQL at runtime based on user input. Specifically, the user selects the "Check Date". The program makes this date the ending date in the date range and uses the first date of that month as the beginning date.

Once again, I'm using Microsoft Access with Visual Basic for Applications (VBA) to develop the interface to the SQL Server database (via pass-through queries), along with forms for the user.

Today I needed to look at the application again because the client needed their report for December 2006. I realized after generating it that I needed to make the program more robust and self-sustaining.

What I mean to say is that I'd set up the program to look at the "current year", which meant that, in January of the next year, it wouldn't get some information from the previous year. So I was able to tweak the code to generate the additional queries at runtime.

All a user sees is a calendar control on a form with a couple of buttons and some text. He or she selects the date, clicks a button, waits awhile, and receives a message that the report has been generated to a Microsoft Excel file. It's a very simple experience for the user, mainly because all the work is done behind the scenes.

Payroll bridge application to achieve estimated ninety-one percent cut in processing time

Today I completed work on a Microsoft Access application that transforms payroll in an Excel file into a tab-delimited text file. The application serves as a bridge from a client's payroll file to a program that is used to batch load payroll transactions.

To make this happen, there are several class modules I created in Access VBA (Visual Basic for Applications), which are set up as an object model. Some of the modules include the following:

• Batch Information (lookup)
• Company (specific info about a client)
• ConvertXL (converts Excel file to tab-delimited text for processing)
• Employee (specific info about an employee)
• File (file name, beginning/ending, etc.)
• LineText (relates to information on a line of text in the payroll file)
• Lookup (does a lookup using a recordset)
• Outlook (used for Microsoft Outlook object manipulation)
• Payroll Item (info related to a specific payroll item)
• Payroll Items (a "pseudo" collection of payroll items)
• Persist Class (a base, abstract class implemented in other classes)
• Process (does most of the payroll processing)
• Section (info related to different sections on a payroll file)
• Sections ("pseudo" collection of sections)
  

Processing payroll before having an application for this client, I've been told, took about three hours. With this tool, I estimate it will take about 15 minutes, which is a 91 percent reduction!

I'll share more about this application in future posts, since this is one we've implemented for other clients.