How to hide Excel data query authentication settings.

Sometimes it's easier (lazier?) to use an Excel query for simple reports.  One problem with this is that the query authentication properties are viewable to anyone who has access to the report.  While this is like teaching a dog algebra for the average user, an advanced user could, conceivably, use this connection data to create their own, unauthorized queries.

While there is no way that I know of to hide the query information, you can create a set of protected procedures that:

1. adds the authentication (connection string)
2. runs the query
3. then removes it again.

This is a simple example of what it could look like:

1. First you'll want to create a new VBA module in your Excel worksheet with procedures that look something like this:
   
   Sub AddConnect()
       'this will add the connection string to
       'all connections in the active workbook
       For Each CN In ActiveWorkbook.Connections
           CN.ODBCConnection.BackgroundQuery = False 'this line is optional
           CN.ODBCConnection.Connection = _
           "ODBC;DRIVER={Progress OpenEdge 10.1C Driver};" & _
           "UID=YOURUSERNAME;PWD=YOURPASSWORD;" & _
           "HOST=YOURHOST;PORT=YOURPORT;DB=YOURDATABASE;" & _
           "DefaultIsolationLevel=READUNCOMMITTED"
       Next
   End Sub
   
   Sub RemoveConnect()
       'this will remove the connection string from
       'all connections in the active workbook
       For Each CN In ActiveWorkbook.Connections
           CN.ODBCConnection.Connection = "ODBC;"
       Next
   End Sub
   
   Sub ListConnections()
       'this will list all query connection strings
       'in the active workbook
       For Each CN In ActiveWorkbook.Connections
           MsgBox (CN.ODBCConnection.Connection)
       Next
   End Sub
   
   Sub RefreshAll()
       'this will refresh all the data connections in the active workbook
       AddConnect 'add the connection strings
       ActiveWorkbook.RefreshAll 'refresh all connections
       RemoveConnect 'remove the connection strings
   End Sub



2.  You can then password protect the module by right-clicking it and selecting properties.
   
3. Then check "Lock project for viewing" and enter your password.  Finally, click OK.
4. You can then add a command button to your worksheet with the macro assigned to RefreshAll().