Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel Queries using Access data | Excel Discussion (Misc queries) | |||
Importing multiple XML queries into Excel | Excel Discussion (Misc queries) | |||
Moving / Sharing excel files with queries | Excel Discussion (Misc queries) |