[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6201
  • Last Modified:

Hide and Lock connection setting in Excel

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?
Thanks.
0
bluebellgroup
Asked:
bluebellgroup
1 Solution
 
weellioCommented:
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.
0
 
bluebellgroupAuthor Commented:
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.
0
 
weellioCommented:
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
http://www.fontstuff.com/vba/vbatut03.htm

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mark1perCommented:
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
0
 
weellioCommented:
connectionstrings.com

this would require some discussion on how you want it added.. you may want to create a new question regarding it.
0
 
seediffCommented:
hi
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now