Yesterday was a fun day.

We generate a good amount of our largest customers invoices through MS Access.  Yes.  That's right, MS Access.  Don't get me started.  Once the invoices are generated, there's a website that lets my group review and release the invoices to the customers.  It does some nice things - lets them decide if it should be excel or pdf, printed and/or emailed, and sends a copy to our document storage website.

I got the database running yesterday, and then set my focus on moving the website over to our new win 2003 server.  Previously, it was being hosted on a desktop pc and we were always facing issues with too many users trying to log on at once.  I had expected a quick transition, but it was a day of constant debugging.  Everyone time I resolved an error, I moved two steps ahead and hit another issue.  It's a good testiment to documentation, because I'm sure I've had to troubleshoot half of the issues previously but couldn't remember what I had done, or what downloads I had needed previously to resolve.

Anyways, I got that up and running, only to find the invoices were printing w/o the company logo.  The font wasn't installed.  Doh.  Then the page numbers were printing incorrectly - wrong version of excel was installed.  Double-Doh.

Finally, the kicker - after about a third of the invoices had been released, we finally noticed the pivot table we put on the front page wasn't there. 

Now, the code for this access database hadn't changed from the previous month, and we haven't installed any new software on the computer that is running the database.  Is this the result of some software update pushed to the computer?  I don't know - What I finally found was the issue was the way we declared the range objects

"Dim myrange as range" suddenly had ceased to work.  I changed it to "Dim myrange as excel.range" and we were back in business.  WTF.

It was also a nice lesson in error handling because I found that previously the code had been set up to skip the pivot table generation and go on it's happy way if an error appeared.  Hence why we were unaware of the issue.

4/3/2008 8:08:57 AM (Central Standard Time, UTC-06:00)    Comments [0] Trackback

I can't say I've ever had to recover from spring break due to shoveling/snowblowing too much, but I guess there's a first.

The Lab 3 assignment of refactoring the code was especially timely.  For the last month I have been working on recreating excel vba macros, and an access database, after making significant changes to the way we take work into the department.  It was great in that it finally gave me the change to create the code from the ground up, after struggling for the past year with hand-me down processes.  On the other hand, I had completely underestimated the amount of time it would take to accomplish this.

Almost three weeks over due, I got the tools back up just in time for our quarter close.  As streamlined as I tried to make it - it's still a messy process.

We have a website where we can go to pull reports - there are 10 reports that have to be pulled every hour.  I'm using HP's QuickTest Pro to automatically pull these.

Those 10 reports get sent to an email account and I have Outlook VBA that grabs the file attachments and saves them with the correct file names.

Those 10 reports then have to get run through the ringer of consolidating the data and determining all sorts of turn-around-time and processing metrics.  I created my own Excel Add-Inn that is able to accomplish this.

Then, everything gets pulled in to an Access Database, crunched through a number of queries and key data points get spit back out as another excel file.

Then I've got a software called Automate that copies the charts out of excel and pastes them into MS Paint as jpg files that are displayed on two large flat screens in the department and through an intranet accessible website.

Automate also is what we use to tie all of these pieces together and set up the hourly schedule.

It's a ridiculous assortment of programming and automation softwares that were a pain to put together, but work beautifully now that I can set my watch, sit back, and watch work without my touching a thing.

3/25/2008 5:50:09 PM (Central Standard Time, UTC-06:00)    Comments [0] Trackback

I've been bad about blogging lately - there's been so much going on at work!

This weekend was spent rolling out new workflow forms for the department.  The department is a local department that receives work requests from across the country, and the workflows are online forms that are set up to have the required pieces of information from the field and are able to bounce from person to person through all of the steps that are required in order to process.

We have an in-house team that has created an amazingly powerful web portal tool that can accomplish this (amongst many other features).  Unfortunately, the tool has no test environment, and any change I make to a form affects all of the existing requests.  In addition, while I love the tool, it's also known to be very glitchy - so I never really know if what I've updated is going to work until it's already been changed.  It makes for a fun time.  The changes usually happen on a Sunday and I spend the rest of the week fielding complaints and questions, and trying to update all of the many automation and reporting tools to handle the change.

It is frusterating - this department is big on change, and is very data driven, at the same time as there is a struggle between needing to capture the right information vs someone having to take the time to provide it.  How do you increase your reporting abilities while decreasing the amount of effort required to fill out a form?  How do you balance what is required for the back end (a form going from point a to be) while still keeping the tool user friendly? 

It makes me realize how valuable CAP and project management skills are - yet I can't help thinking that I don't even have enough time to manage the technical aspects of a project, much less the people side.  It's something I hope the MIS degree will help me learn how to better manage.

3/4/2008 12:19:57 PM (Central Standard Time, UTC-06:00)    Comments [1] Trackback

I saw Taryn's comments about how to remember all of this code, and so here's my take on it.

Memorizing code is great if you're programming in a crunch and need to get things done as fast as possible, but it is more important to know that the commands exist, or at least have an idea that there must be a command out there to do what you're doing.

You want to know the basic concepts - If/Then statements, Loops, variables, arrays - and how the tools can help you solve problems.  Next, learn how to read syntax documentation. 

After that, you're usually only a google search away from the answer.  A lot of programming is copying and pasting from projects you've done before, or code you've found online.  It's slower than memorizing the code, but it's just another resource to use and another way to learn.

2/19/2008 4:07:49 PM (Central Standard Time, UTC-06:00)    Comments [1] Trackback

I've been thinking about the way I program and how the methods I use affect the productivity in my department.  In many cases, the first solution I can come up with is the one that goes live and the amount of time it takes for the process to run is not a consideration.  The thought is, if it takes an extra click, or if it's easier to take a few extra seconds and switch screens a couple of times, then okay, let's do that.  It's still going to be faster and more accurate than someone manually making the keystrokes.  I tend not to consider if a program I'm writing is going to be used numerous times a day by numerous people in the department, or if the program is going to be used for a 5 line file or a 5000 line file.  It can all add up.

Then there's my current project.  We've determined that the download of a document off one server, converting the document from the current blob format, back into a 'real' file, and then uploading it to a new server, should on average take 4 seconds.  My coworker told me this, and I thought 'hell, that's pretty good'.  Then he reminded me that there are almost a half-million files that we are going to have to run through this process, and that, by his calculation, it will take >9 days of a computer working 24x7.  Doh.

As a lot of people in class are busy focused on learning the programming basics, I'm interested in fine-tuning the skills that I have.  How can I be an efficient programmer?  What are the advanced tools that I can take advantage of?  How can I build programs that are not only easy to use, but are also easy to update and maintain.

2/12/2008 4:07:59 PM (Central Standard Time, UTC-06:00)    Comments [0] Trackback

On the first day of class, Marc made a comment about how if, as a programmer, we haven't had to learn XML, we will at some point.  That hit home with me, because earlier that day I had hit that point.

At work, we're looking at upgrading our online document storage tool.  Currently, we get almost everything into the department in electronic format, yet we still have to print the documents and then scan them into tiff files that get uploaded to an online database and a searchable website.  It's a lot of wasted paper and wasted time.

We've found that the submission and workflow tool that we use has the ability to automatically store the documents - but then we run into the issue of having to replace our website with something that is compatible with the new system. 

The new system is able to provide us an XML feed of our content, and it supports something called XSLT.  XSLT is a formatting language that lets you place a customized skin over the XML feed.  It gives complete control over what information is displayed from the XML and in what format it is displayed in.  This is going to let us create our own user interface, complete with dynamic links and advanced search capabilities.

My success last week was creating my first working template.  It was very basic - displaying my XML records with some simple HTML formatting.  It was the end result of a days worth of trial and error.  The logic to how it applies the formatting is the complete opposite of what I'm used to in programming.  In the basic coding, the data determines the order and the format follows.  This week, the project is moving forward.  I've been able to add a search form and display the results.  I had to brush off some very rusty javascript skills to get that to work.

It's just a start though, and I look forward to learning more.  I love the idea of being able to be in control over how my data is being shown and what can be done with it.

1/31/2008 5:22:16 PM (Central Standard Time, UTC-06:00)    Comments [0] Trackback