Hide and Lock connection setting in Excel

Posted on 2007-08-11
Last Modified: 2011-01-31
Dear all experts,
      I am currently making up some Pivot Reports from Excel 2007.  Data is being pull directly from SQL Server 2000 through the connection setting that comes with Excel.  I want user can fully manage this Excel file except the connection setting (the connection string, data source setting).  I try to use the "Protect Workbook -> Protect Structure", it does lock the data connection setting but it also disable some excel functions like adding / deleting worksheet.  Therefore I would like to ask if there's a way I can simply hide or lock the connection setting from being modified while all other excel functions are still available to users?
Question by:bluebellgroup
    LVL 19

    Expert Comment

    save your settings in a seperate xla template.
    distribute the template to the users as an addin.  this way the addin will be locked down, but not their workbooks.
    LVL 4

    Author Comment

    Thank you for your advice, weellio. How do I exactly do that? (Actually I am not very familar with what xla is).  I will be appreicated if you can give me a bit detail on how do I approach for that, thanks.
    LVL 19

    Accepted Solution

    set up a spreadsheet with the macros, jsut like you want it, then protect it and save it as a excel template file.

    file - save as -
    save as type - microsoft xla add-in

    here is a step by step example


    Expert Comment

    I know this was answered to a point.  But is there a way to put a connection string in an addin?  If so can someone spell it out for me please?  thanks
    LVL 19

    Expert Comment


    this would require some discussion on how you want it added.. you may want to create a new question regarding it.

    Expert Comment

    i want to know how to hide connection settings/password in MSoffice 2010,
    even saving a template when it reopens, it asks for the database password, i want to bypass that at the same time hide connection settings from user

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now