Database Query Macro- password prompt

I have created an Excel sheet that retrieves data from a ODBC source. I have created a macro using macro recorder to refresh the data by re-connecting to the database. However, the connection requires a password prompt and when the macro is run, you are still required to enter the password.

Is there anyway to make the macro so that the password is automatically entered and the user will just have to press a button to fully run the query update? Thanks for any help on this.

Hi

If you right-click in the data table (ie the returned results to the sheet) you should see an option for DataRange Properties (or something like that - I don't have Excel in fron of me at the moment). If you click on this, you should have a "Save password" option in there. You can also set the database to refresh on opening the workbook or every few minutes.

I guess I don't really undersatnd why you need a macro to refresh the data - one thing about the macro recorder and external queries is that it creates a new query table each time (based on the QueryTables.Add method. This is unnecessary, as if you are simply looking ot refresh the data, you just need to pass the Refresh method of the QueryTable object:

Please Login or Register  to view this content.

Richard