Workbook Connections and Querytables

Hi all

I am trying to figure out the best way to pull the results of a SQL Server Stored Procedure in to an Excel table.

The easiest way I have found is to create a Data Connection using the ribbon in Excel, and then refreshing the data using VBA.

There is one main problem... I would like to send this workbook to a customer, however all they have to do is open the Data Connection properties window and they will have full access to the user credentials for our database (IP address, username and password etc.)

Would a better approach be to create a connection to the database and then delete it each time the VBA code runs? This way, the customer would not be able to view the Data Connection properties.

Any sample code for pulling in data from an OLE DB data source and executing a Stored Procedure would be greatly appreciated.

Yes, you could do it via ADO and paste the recordset returned into Excel. This is the way ive done it in the past. Put some decent error trapping around it though if its going out the the customer. Get some dummy errors with bad credentials to translate the VBA errors into nicer message box ones for the client.

password protect the VBA to.