Excel Queries Anselmo 12.07.2006 01:33:02 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??

RE: Excel Queries Anselmo 12.07.2006 17:37:02 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ó: [Quoted Text] > 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??

Re: Excel Queries Bill Manville <Bill-Manville[ at ]msn.com> 13.07.2006 22:30:05 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

Re: Excel Queries Anselmo 14.07.2006 01:44:01 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ó: [Quoted Text] > 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 > >

Re: Excel Queries Bill Manville <Bill-Manville[ at ]msn.com> 14.07.2006 22:45:44 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