Passing ODBC Connection to Excel

[RedMoo]

Hi,
I have multiple worksheets which each call a separate MSQUERY statement. I recorded a Macro and I have placed a refresh button in the spreadsheet to call this macro automatically refresh the document but it asks me to enter my username and password to the SQL Server connection for every worksheet that calls a MSQuery.

An example of the calls in the macro would be.

Sheets("Accomodation Items").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Consumables").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Other Services").Select
Range("A3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("B31").Select


I wish to be able to pass the connection details to the ODBC in here so that I do not have to log into every worksheet to get a result.

Regards,
Redmoo

 

[SkipVought]

Hi,

Please post VBA (macro) questions in Forum707.

This code-related question will not be discussed in this forum.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

As a general principle, you need to set up the ODBC connection to retain the password.

Once you have set up the connection without a password, you will need VBA to set it up so as per Skip, please post in the VBA forum

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question