How would you start automating my job? - Part 2

Question

(Followup to this question)

After surviving the first wave of incoming shipments (9 hours of copy/paste), I now believe I have all the requirements.

Here is the updated workflow:

• Monkey collects email attachments (4 Excel spreadsheets, 1 PDF)
• Monkey creates central database, does complex calculations (right now this is also an Excel spreadsheet)
• Monkey sends data to two bosses, who set the retail prices independently; first one to reply wins
• Monkey sends order form to our other warehouses, also Excel
• Monkey sends spreadsheets to VIP customers, carefully sanitized and formatted (4 different discount categories)
• Jurily enters the data into the accounting system. I've given up on automating this part, there's too much business logic involved, and the database is a pile of sh^W legacy

My question: What technologies would you use for a quick and dirty solution? I'm mostly sold on C#, but coming from a Linux/C++ background, I'm horribly confused about my choices in Microsoft-land.

For bonus points: How would you redesign the whole system from the ground up?

Clarification: I'm looking for basically anything that has the potential to get me reading the right things, just give me the keywords and a short description. Google will guide me from there.

P.S. in case you were wondering, my job title is System Administrator.

Answer 1

C#. Linus may b nice, but let me assume you run windows in the company anyway. THe main problem will be (1) (the attachments, PDF) - anything you do here will rely on them having a specific form, and I bet there is a monkey on the other end sending them that rgegularly changes the layout ;)

Anyhow, I would go with C# 4.0 totally. Depending on what you use server side... i may even bypass excel and go exchange direct to read the emails.

Answer 2

You can interact with excel via Com Interop. See this article for how to do this with C#. This is an ugly solution in the sense that you must run it on a system that has Excel. You are not creating spreadsheets, you are programmatically telling Excel to create spreadsheets. This has its own advantages and disadvantages.

It's reasonably easy to create spreadsheets yourself (Excel can open spreadsheetml files), though this makes it tougher in some instances; the easiest way to generate an xls file is to tell Excel to save one (treating excel as a database is pretty easy too, but that doesn't give you access to all Excel functionality).

Answer 3

actually I would do this in Outlook and Excel VBA, and in Access. Don't see why can't scrape the data out of the pdf