auto select message prompt


Board Regular
Oct 30, 2003
Is there any way to automatically select one of the options on a message box when it pops up? I have a Login box that pops up, and it's always filled in with the correct information so I just have to hit okay, but I'd like to avoid having to click every time it prompts. Any options?

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
sorry, excel message box, pops up to confirm sql server login password (which is blank anyway). This login comes up every time a cell is updated, apparently to refresh data from the database from a sql query I have entered as an array formula
Upvote 0

indirect(W1) refers to a location with an sql query built in, but I've posted the query on the board a couple times trying to find out how to avoid the login box from coming up at all without any luck, so am looking for a way around having to interact with the pop up, if I can't avoid it
the query is pretty standard, select blah from blah where blah = blah, that part works fine, and I've used this format with several other sheets pulling different data (from the same source though) and have to deal with this popup issue on all of them
Upvote 0
Can't you provide the username and password information along with the DSN?

This is quoted directly from Microsoft Help

Microsoft Help said:

Connects with an external data source, and runs a query from a worksheet. SQL.REQUEST then returns the result as an array without the need for macro programming. If this function is not available, you must install the Microsoft Excel ODBC add-in program (XLODBC.XLA). You can install the add-in from the Microsoft Office Web site.</p>



Connection_string    supplies information, such as the data source name, user ID, and passwords, required by the driver being used to connect to a data source and must follow the driver's format. The following table provides three example connection strings for three drivers.</p><table><tr><th>Driver</th><th>Connection_string</th></tr><tr><td>dBASE</td><td>DSN=NWind;PWD=test</td></tr><tr><td>SQL Server</td><td>DSN=MyServer;UID=dbayer; PWD=123;Database=Pubs</td></tr><tr><td>ORACLE</td><td>DNS=My Oracle Data Source;DBQ=MYSER VER;UID=JohnS;PWD=Sesame</td></tr></table><ul>[*]

You must define the data source name (DSN) used in connection_string before you try to connect to it.</p>[*]

You can enter connection_string as an array or a string. If connection_string exceeds 250 characters, you must enter it as an array.</p>[*]

If SQL.REQUEST is unable to gain access to the data source using connection_string, it returns the #N/A error value.</p>[/list]

Output_ref    is a cell reference where you want the completed connection string placed. If you enter SQL.REQUEST on a worksheet, then output_ref is ignored.</p><ul>[*]

Use output_ref when you want SQL.REQUEST to return the completed connection string (you must enter SQL.REQUEST on a macro sheet in this case).</p>[*]

If you omit output_ref, SQL.REQUEST does not return a completed connection string.</p>[/list]

Driver_prompt    specifies when the driver dialog box is displayed and which options are available. Use one of the numbers described in the following table. If driver_prompt is omitted, SQL.REQUEST uses 2 as the default.</p><table><tr><th>Driver_prompt</th><th>Description</th></tr><tr><td>1</td><td>Driver dialog box is always displayed.</td></tr><tr><td>2</td><td>Driver dialog box is displayed only if information provided by the connection string and the data source specification is not sufficient to complete the connection. All dialog box options are available.</td></tr><tr><td>3</td><td>Driver dialog box is displayed only if information provided by the connection string and the data source specification is not sufficient to complete the connection. Dialog box options appear dimmed and unavailable if they are not required.</td></tr><tr><td>4</td><td>Driver dialog box is not displayed. If the connection is not successful, it returns an error.</td></tr></table>

If SQL.REQUEST is unable to execute query_text on the specified data source, it returns the #N/A error value.</p>[*]

You can update a query by concatenating references into query_text. In the following example, every time $A$3 changes, SQL.REQUEST uses the new value to update the query.</p>

"SELECT Name FROM Customers WHERE Balance > "&$A$3&".</p>

Microsoft Excel limits strings to a length of 255 characters. If query_text exceeds that length, enter the query in a vertical range of cells, and use the entire range as the query_text. The values of the cells are concatenated to form the complete SQL statement.</p>[/list]

Column_names_logical    indicates whether column names are returned as the first row of the results. Set this argument to TRUE if you want the column names to be returned as the first row of the results. Use FALSE if you do not want the column names returned. If column_names_logical is omitted, SQL.REQUEST does not return column names.</p>

Return Value</p><ul>[*]If this function completes all of its actions, it returns an array of query results or the number of rows affected by the query.[*]If SQL.REQUEST is unable to access the data source using connection_string it returns the #N/A error value.[/list]

Remarks</p><ul>[*]SQL.REQUEST can be entered as an array. When you enter SQL.REQUEST as an array, it returns an array to fit that range.[*]If the range of cells is larger than the result set, SQL.REQUEST adds empty cells to the returned array to increase it to the necessary size.[*]If the result set is larger than the range entered as an array, SQL.REQUEST returns the whole array.[*]The arguments to SQL.REQUEST are in a different order than the arguments to the SQLRequest function in Visual Basic for Applications.[/list]
Upvote 0
man, I've been looking for that for 2 months. Leave it to Microsoft to hide that in plain sight... While you were at it, you answered a question I had about the 255 character limit, I'll have to set that up too.

I had 2 other questions which I'll state for posterity, but will leave for a complete posting unless you're looking for something to do.

Thanks for the info so far, I'll give more info on the next questions if requested, otherwise you may have a head start when I send up a complete posting.

First, when I make the array large enough to return alot of rows, I only seem to be able to retrieve 31 rows of data, where, if the query is executed in a query analyzer it can return hundreds of rows (not more than a thousand or so, so it shouldn't be bottoming out).

Also, I'm looking for a way to not have Excel try updating the data after every cell update. I've set updating to false, and auto-calculate to off, but those affect other open workbooks as well.
Upvote 0
Well, why not use a VBA solution instead of a function solution? I think that you could resolve some of the issues you are having by exploring this method.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back