Hosted Excel macro

Question

What I have:

An Excel workbook that uses a VB macro to calculate the price of a product based on a complicated Bill of Material set of instructions.

How the workbook is designed:

The workbook has two worksheets: one with material costs and constants and the other with the product configuration. There is a button that when clicked, calculates the prices of a specific product configuration using the costs and constants in the other worksheet.

What I would like to design:

I want to be able to have a centrally-hosted Excel file/page where our sales reps can enter the product configuration, click a button and get the price of a product. However, the sales reps must NOT have access to the constants and costing page - this is a key constraint.

Any suggestions?

Answer 1

This sounds like what the Excel Services part of Microsoft SharePoint was designed for.

Answer 2

You need to create an addin containing the constraints and constants page.

As an addin it will be hidden.

Note, that if they know Excel trickery they may be able to unhide it, but you may (not tested) be able to put a password on it. You can also do more Excel tricks like put all the constants in white font, but there's no perfect system if it can't be locked.

As far as hosting goes, then you'll have to bring in some other program like SharePoint. Vanilla Excel wasn't designed for this.

Answer 3

If you provide a template they use for the product configuration, a passworded sheet would work, but if security is a big issue, why not make this a webform that outputs an excel file?

You can keep all your calculations totally private, distribute what the salesman needs easily and still in a format, that ultimately didn't require you to worry about exposing the costing.