Take the 2-minute tour ×

I have a general question and I'm not sure where to begin, literally. Can you please leave any direction at a fairly high level so I have the chance to try to figure it out for myself? Here's my initial plan, but here's the issue! I don't know where to start. Where would I begin with this? Would it be an Excel module? I need some direction guys!! Let me give you a quick run down of what we're trying to accomplish.

We start out with 2-3 customer specific spreadsheets with an account number + [static] file name.xlsx and about 7 generic template excel files where we use the data from the customer specific spreadsheets and save a copy of each of the template files in the customers store folder.

What I'm trying to figure out if VBA is the direction that I need to be going with this or if there is another method that would accomplish the same goal or be more efficient at getting it done. My thinking at this point is open one of the customers files, save the active workbook file path as a string (this would contain the file directory, division, customer number and customer name), split the string of the file path and save the information that I need as variables, open the appropriate template files based on division number, probably run a couple recorded macros then re-save the files using the account number in the appropriate directory. If there's anything else I left out for this to make more sense, please let me know and I'll re-edit the post. Thank you in advance for any guidance!!

share|improve this question
You should at least consider moving to Access for this. You can still connect with Excel and pull the data but for storing information from multiple people and places a database is best. –  Jesse Mar 6 '12 at 0:20
Would you ever want to update all the customer-specific spreadsheets when you update the main templates? –  sarnold Mar 6 '12 at 0:30
Honestly, I had to reread this couple of times to understand what you wanted. :) I think I finally understood what you want. Yes, you can use an Excel Module to achieve what you want. –  Siddharth Rout Mar 6 '12 at 5:18
add comment

2 Answers

up vote 2 down vote accepted

I don't think you should use Access or VSTO - I'll explain why, then offer a practical way forward.

Access is way over the top for what you want to do: most people find it much easier to write code in Excel VBA (I speak as a trainer with 20 years of experience, who is a guru on both packages).

Visual Studio Tools for Office was Microsoft's attempt to get the business world to stop using VBA in Excel, and use the much more robust VSTO within Visual Studio. It's a great idea, but unfortunately not many people seem to be adopting it. In computing it's rarely a good idea not to go with the flow (and anyway, the learning curve for VBA is shorter than that for VSTO, IMHO - hope that's enough acronyms in one sentence!).

As to the practical solution: I've just finished writing a fairly large online tutorial in Excel VBA, which is at exactly the level you need, I think.

Good luck!

share|improve this answer
+1. The issues with VSTO are two fold. a) Requires a license for Visual Studio which few Excel users have, b) Deployment is so difficult and convoluted that makes you wonder WTF is Microsoft thinking. VBA is great because it deploys with the worksheet and it always works for everyone. –  ja72 Mar 7 '12 at 15:04
Andy, I've been referencing the link to the WiseOwl blog you provided!! Thank you for the assistance! –  CSharp821 Mar 15 '12 at 14:47
CSHarp821 - glad you liked it. ja72 - I wonder that sometimes with Microsoft products. Some are great, but then you get others where you think: surely you could see this wasn't going to take off? –  Andy Brown Mar 16 '12 at 8:55
add comment

Since you are dealing with XLSX files then you are in Office 2007 or 2010, so you could use Visual Studio Tools for Office. This will allow you to use the .Net framework and C# or VB.Net in Visual Studio to get a better development experience than VBA. You could add your own ribbon and buttons to Excel to help accomplish your tasks.

share|improve this answer
add comment

Your Answer


