mark1per
asked on
connection string
Hi,
I woulld like to use excel to connect to SQL and pull data in. What I would like to know is how can I put the connection string in an excel add in? I want it hidden. If that not the way to do it. what is the correct way? Keep in mind the excel spread sheet should have the ability to update the data via a refresh button and again the connection string is never exposed.
I woulld like to use excel to connect to SQL and pull data in. What I would like to know is how can I put the connection string in an excel add in? I want it hidden. If that not the way to do it. what is the correct way? Keep in mind the excel spread sheet should have the ability to update the data via a refresh button and again the connection string is never exposed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
weellio, that is a really cool file. Thanks for sharing.
ASKER
That is great and works perfect. thanks
Mark1Per, I have the same issue. I need a simple way to be able to hide connection strings from end-users in Excel. I'm working on Excel files that contain multiple connections, and all have parameters. To have to manage all of this in VBA is cumbersome and time consuming.
There are many articles written (including by Microsoft) about the importance of securing connection strings. If this is so important they should provide us with a simple way of hiding the string in Excel.
I'm currently searching for solutions to this problem myself. If I discover a method other than through solid VBA I'll post here.
There are many articles written (including by Microsoft) about the importance of securing connection strings. If this is so important they should provide us with a simple way of hiding the string in Excel.
I'm currently searching for solutions to this problem myself. If I discover a method other than through solid VBA I'll post here.
ASKER
great thanks much appreciated as will I.
Mark1Per, if you're using Excel 2003 you can disable the "Data Properties" and "Edit Query" menus by inserting the below code into a standard VBA module.
This doesn't appear to work in Excel 2007. I'm currently looking for this same type of solution for 2007. Will let you know what I find.
This doesn't appear to work in Excel 2007. I'm currently looking for this same type of solution for 2007. Will let you know what I find.
Sub DisableMenu_DataProperties()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=1951)
Ctrl.Enabled = False
Next Ctrl
End Sub
Sub DisableMenu_EditQuery()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=1950)
Ctrl.Enabled = False
Next Ctrl
End Sub
The simplest solution I could come up with for 2007 is to disable access to the DataRangeProperties, Connections, and ConnectionProperties menu buttons in the ribbon. Modifications to the ribbon in Office 2007 is to be done via the 'Office 2007 Custom UI Editor'. Here are the instructions:
1. Download and install the 'Office 2007 Custom UI Editor' open source software. This can be found at http://openxmldeveloper.org/archive/2006/05/26/CustomUIeditor.aspx. If you'd rather link to it directly from Microsoft's site go to the following microsoft page and scroll to the very bottom. http://msdn.microsoft.com/en-us/library/cc508991(office.11).aspx. In the section 'Additional Resources' you will see a link to 'UI Editor.
2. After installed, open the UI Editor and choose File / Open and select the Excel file that you want to disable the Connection Properties buttons.
3. In the main browser screen of the UI Editor type in the XML code you see in the below code snippet.
4. Click the 'Checkbox' icon to ensure the code works, then click the Save icon.
5. Now open your Excel file and the buttons that lead to the connection strings are disabled.
Only problem with this method of securing the connection string, users can get around it by 'Copying' all the cells within the connected data range, and then paste those cells into a new workbook. By pasting they not only copy the data, but they also copy the connection string info associated with it. Oh well, I suppose if you search hard enough you'll always find a way around security.
Good luck.
1. Download and install the 'Office 2007 Custom UI Editor' open source software. This can be found at http://openxmldeveloper.org/archive/2006/05/26/CustomUIeditor.aspx. If you'd rather link to it directly from Microsoft's site go to the following microsoft page and scroll to the very bottom. http://msdn.microsoft.com/en-us/library/cc508991(office.11).aspx. In the section 'Additional Resources' you will see a link to 'UI Editor.
2. After installed, open the UI Editor and choose File / Open and select the Excel file that you want to disable the Connection Properties buttons.
3. In the main browser screen of the UI Editor type in the XML code you see in the below code snippet.
4. Click the 'Checkbox' icon to ensure the code works, then click the Save icon.
5. Now open your Excel file and the buttons that lead to the connection strings are disabled.
Only problem with this method of securing the connection string, users can get around it by 'Copying' all the cells within the connected data range, and then paste those cells into a new workbook. By pasting they not only copy the data, but they also copy the connection string info associated with it. Oh well, I suppose if you search hard enough you'll always find a way around security.
Good luck.
<customUI onLoad="ribbonloaded" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<commands>
<command idMso="DataRangeProperties" enabled = "false" onAction="DisableDataRangeProperties"/>
<command idMso="Connections" enabled = "false" onAction="DisableConnections"/>
<command idMso="ConnectionProperties" enabled = "false" onAction="DisableConnectionProperties"/>
</commands>
</customUI>
ASKER
Cool thanks