0

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!!

Improve this question
3
  • 1
    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. Commented Mar 6, 2012 at 0:20
  • Would you ever want to update all the customer-specific spreadsheets when you update the main templates? Commented Mar 6, 2012 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. Commented Mar 6, 2012 at 5:18

2 Answers 2

Reset to default
2

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

+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.
Andy, I've been referencing the link to the WiseOwl blog you provided!! Thank you for the assistance!
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?
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.