PO Editor Final Project

To whom it may concern:

For my project, I wanted to automate the tedious task of PO (purchase order) preparation. At my work, I am responsible for POs that have been submitted to vendors, and create a number of excel documents from each order. These documents include instructions for our warehouse, and templates to upload information to QuickBooks, Amazon, and our store’s point of sale.

Since those documents require much of the same information, but each has its own unique template, the process involved a lot of copying and pasting. I decided that macros would be more efficient and accurate. The rest of this write-up will cover my implementation, the difficulties I encountered, and features I wish to add in the future.

*Note: My program accesses files from a Dropbox account. As such, the program will crash on any computer without access to that Dropbox account.

Implementation

When starting this project, I first decided what the program needed to accomplish in order to be more efficient than manual work.

The requirements I selected are found below.

·         Open the original excel based PO

·         Open the different template files

·         Collect essential information from the original PO, including

Ø  Item ID

Ø  Description

Ø  Item instructions

Ø  Quantity ordered

Ø  Order date

Ø  Supplier

·         Recognize when a product was new, as that requires an extra process

·         Insert the information collected into the different documents

·         Save the resulting files in their specified Dropbox folder

I then created the main excel document. The layout of this document is very simple. There is only one sheet in the document. On this page there is a header in A2, with the button I have created to the right. Starting in A3, you will enter the name of the PO excel document exactly as it appears in the file (without the end extension of .xlsx). The program first opens the files that all POs will add to, such as the Amazon and point of sale template. Since the other templates need to be unique for each PO, those are opened individually

The program then starts a loop starting in A3. The loop does the following

1.      Opens the file with the name in the active cell, along with the template files

2.      From the PO file, it gathers the order information such as supplier, order date, terms, and PO number

3.      For each item on the PO, it identifies if it is a new item, which is defined by the word “New” being found in column N, or a price in column O

4.      If it is a new item, it identifies it as being for the retail store or for Amazon, identified respectively by “New Store” or “New Flip”

5.      Copies and pastes the needed information, such as ID, Description, ASIN, Order Quantity, and Order Note, into the corresponding files

6.      Saves the corresponding files into their proper locations, and closes them

7.      Repeats the process for the next PO

Difficulties

I believe that when making a program, it’s not a question of if you will have bugs and problems, rather, when you will find those problems. When implementing my program I quickly found some unforeseen problems that I needed to fix.

The first problem was that my program originally would copy and paste each piece of information individually. This required a lot of switching between open excel files, which slowed down the process and was dizzying to watch. Upon learning about arrays in class, I made each variable an array, so that I could collect all of the information in one run, and then insert the information all at once.

Second, I wanted all the other employees to be able to use the program. That meant I needed to make the program access files from any computer. My program was initially set up with my computer’s specific name for the file path. I changed this to be a variable that would be set at the beginning of the program.

Third, the other employee in charge of POs worked differently than I did. Since he was more involved in the buying process than I was, he made instruction files as he went along. The only file he wanted generated was the template for QuickBooks uploads. In addition, there were times where one or more of the files didn’t need to be generated. As such, it was unnecessary for my program to make those files. To fix this, I created a Userform that appears at the beginning of the program. The Userform goes through each of the POs in the queue and asks for you to check the boxes of all the different files that need to be generated.  The boxes are checked by default, since most of the POs need all of the files generated.

Fourth and finally, I had to deal with the problem of incomplete information. Sometimes by accident, essential fields would be left blank on the original PO. This would cause problems because the files would be generated, but with incomplete information. I resolved this issue in two different ways. The first was specifically for the item description and order date fields. There are two cells where this information could be entered. As such, I merely have the program look in the alternate cell when the original cell is left blank. For the fields such as order note and cost, I implemented an input box to ask for the information when the cell is blank.

Future Additions

There are a number of things that I still want to add to this program. One of the options I would like to include is an “Exit” option when information isn’t present. As the program currently is, if the information is blank it requests the information from you. This is fine if you know, or if there are only a few items. But on larger purchase orders it might be better to skip the PO for the time being and start later. There is no option for that now, the only possibility would be to deliberately crash the program to get out.

I also would like to have the information automatically upload to Amazon. This would involve the browser manipulation that was briefly demonstrated in class. Currently, the program just puts the information into the file. I feel it would be beneficial for the program to take the information as far as it can.

Finally, I would like to have the program close out of all the programs at the end. It is currently set up to automatically close the PO files and the QuickBooks templates as each is created, but the Amazon and point of sale templates remain open at the end of the program.

Conclusion

This project was a good learning and application experience for me. Not only that, but it has been immensely helpful at work. The time I have spent modifying these POs has been cut by 50 to 75 percent.

If there are any questions, feel free to contact me at the email below.

Grant Baugh

My Fun Family Report Manager

Corderonegro21@gmail.com