Excel Queries
 

I created an excell file, and I am refreshing the excell queries using
ActiveWorkbook.RefreshAll function, but everytime I need to enter the
username and password, is there a way to avoid enter the username and
password for each refreshed query, or just enter one time only??

Excel Queries
 

I create a routine to test the connection, I already add the UID and password
to the connection string, but it still asking me for the UID and PWD, what
am I doing wrong? (does anybody help me?)

:(



Sheet1.Activate
ActiveSheet.Range("B2").Select

With Selection.QueryTable
.Connection = = "ODBC;DSN=MS Access Database;DBQ=C:\Information.mdb;
DefaultDir=C:\;DriverId=25;FIL=MS Access; MaxBufferSize=2048;PageTimeout=5;
uid=user; pwd=password ;"
.Refresh BackgroundQuery:=False
End With


"Anselmo" escribió:

I created an excell file, and I am refreshing the excell queries using
ActiveWorkbook.RefreshAll function, but everytime I need to enter the
username and password, is there a way to avoid enter the username and
password for each refreshed query, or just enter one time only??

Excel Queries
 

I know it's possible not to be prompted for user id and password.
I can only suggest you check the userid and password carefully - no
extra spaces etc.

Try macro-recording the creation of another query to the same database
and check the connection string recorded against yours.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Excel Queries
 

Bill,

I was trying to get the string connection of the query, and suddenly I
realize that is a MSaccess query, but.... I was getting information form a
Oracle DB, so I made some changes to macro and this is what I got:


Sub walle()
Dim constr, constr1, SQL_CorteFact_a As String
Sheet1.Activate
ActiveSheet.Range("B2").Select

constr = Selection.QueryTable.Connection
€˜
€˜ This is the connection string I got
€˜
€˜ <"ODBC;DSN=MS Access Database;DBQ=C:\Information.mdb;
€˜ DefaultDir=C:\;DriverId=25;FIL=MS Access;
€˜ MaxBufferSize=2048;PageTimeout=5;

€˜ Then I read about Oracle strings connections, and I used the following
constr_o = "ODBC; DSN=mybapro; UId=awalle; Pwd=awalle;"

€˜ I applied to query, and the query doesn´t ask me for the username and
password,
€˜ but now I got a 1004 error

With Selection.QueryTable
.Connection = constr_o
.Refresh BackgroundQuery:=False
End With

End Sub


What else can I do???

Kind Regards



"Bill Manville" escribió:

I know it's possible not to be prompted for user id and password.
I can only suggest you check the userid and password carefully - no
extra spaces etc.

Try macro-recording the creation of another query to the same database
and check the connection string recorded against yours.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Excel Queries
 

I suggest you create a new query to that Oracle database via Data /
Import External Data / New Database Query and copy its connection
string into your code.

If the connection string is OK then maybe the SQL of your existing
query is not.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup