How to find definition & example 4 AutoIt process, Help

[meatsack]

When I refresh my excel data I am blocked and a pop up window titled "SQL Server Login" comes up, will not close down till I have typed password and selected the "ok" button. Fortunately the "Login ID:" box stay persistent and I don't have to reenter that each time. So what is the method for auto entering the password on this SQL pop up when your code is opening and manipulating the Excel application? I have tried to identify how this works but was not successful. This is what I tried so far:

1. Search the name of the process in the C: drive : $oExcel.ActiveWorkbook.RefreshAll = no results

When I try a known process such as _ExcelBookOpen I can then easily access its location in the AutoIt Help section under UDF User Defined Functions Reference

2. If no results under 1 then I go to step 2 Search online wiki at http://www.autoitscript.com/wiki/Main_Page

3 If no results under 1 or 2 then I go to step 3 Search online google I can find "activeWorkbook" properties in the Microsoft Visual Basic Help section and also in the Object Browser when in Microsoft Visual Basic "editor" but I don't find any globals or classes that are titled .RefreshAll

4. 3 leads me to 4, search MSDN. They say that "Workbook.RefreshAll" is a "method" and give the coding example : 'Declaration

Public Overridable Sub RefreshAll

5. Suggestion of what to do next?

Bottom line question, I still don't know where this method came from nor do I have a example presented for its standard use. Since I don't know I just speculate that stating $oExcel.ActiveWorkbook. enables you to incorporate almost any process procedure you want to use from Visual Basic and/or Visual Basic for Applications.

Since I don't know how to use it I was hoping to find something like the following that defines and gives an example for the method, In this example the method is _ExcelBookOpen:

Function Reference

_ExcelBookOpen

--------------------------------------------------------------------------------

Opens an existing workbook and returns its object identifier.

#Include <Excel.au3>
_ExcelBookOpen($sFilePath [, $fVisible = 1 [, $fReadOnly = False [, $sPassword = "" [, $sWritePassword = ""]]]])

Parameters

$sFilePath Path and filename of the file to be opened

$fVisible [optional] Flag, whether to show or hide the workbook (0=not visible, 1=visible) (default=1)

$fReadOnly [optional] Flag, whether to open the workbook as read-only (True or False) (default=False)

$sPassword [optional] The password that was used to read-protect the workbook, if any (default is none)

$sWritePassword [optional] The password that was used to write-protect the workbook, if any (default is none)

Return Value

Success: Returns new object identifier

Failure: 0 and set @error

@error: 1 - Unable to create the object

2 - File does not exist

Remarks

None

Related

_ExcelBookAttach

Example

; ***************************************************************
; Example 1 - Open an existing workbook and returns its object identifier.
; *****************************************************************#include <Excel.au3>
$sFilePath1 = @ScriptDir & "\Test1.xls" ;This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1)If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

I can find no Function Reference for $oExcel.ActiveWorkbook.RefreshAll:

Function Reference

1. $oExcel.ActiveWorkbook.RefreshAll

--------------------------------------------------------------------------------

2. Refreshes an existing workbook?

3. #Include <Excel.au3>

4. Parameters

UNKNOWN

5. Return Value

UNKNOWN

6. Success:UNKNOWN

7. Failure: 0 and set @error? UNKNOWN

8. Remarks

UNKNOWN

9. Related

UNKNOWN

10. Example

UNKNOWN

Thanks in advance for any help or suggestions.

[MrMitchell]

Don't you have the option to save the password in the connection file? You could also try to edit the connection string and add it there...

Otherwise I think the only way to handle that password prompt is similar to the way the Outlook UDF handles the Security Warnings...you'd probably need to write up something if someone hasn't already.

[MrMitchell]

Here's an example for my second suggestion...you'd have to run this script BEFORE you run your .RefreshAll() method:

$password = "password"
While 1
WinActivate("SQL Server Login")
Sleep(100)
If WinActive("SQL Server Login") Then
  If ControlSetText("SQL Server Login", "", "Edit3", $password) Then
   Sleep(250)
   If ControlClick("SQL Server Login", "", "Button3") Then
    ExitLoop ;Comment this out if you want to continuously check for the password prompt
   EndIf
  EndIf
EndIf
WEnd
Exit

I suggest you run it now within Scite or compile then run it (after changing the password) then open your spreadsheet, click Refresh All then see if the password is entered automatically...

Edit/add: This worked for me in Outlook 2007. Another thing I just thought of, if the SQL server admin can add your login to the SQL server then you have Excel use your Windows credentials to log in to SQL and shouldn't prompt for a password or anything. The spreadsheet should also be in a trusted location.

Edited October 6, 2011 by MrMitchell

[meatsack]

Another thing I just thought of, if the SQL server admin can add your login to the SQL server then you have Excel use your Windows credentials to log in to SQL and shouldn't prompt for a password or anything. The spreadsheet should also be in a trusted location.

Thanks much. I will try it both ways. This security thing gets so complicated. I logon with xp with a different user and password from the Excel SQL data connection. The user name is persistent in the popup box, but the password is not. There is a box on that popup to check "trusted connection" but it resets each time that file is reopened. I will post my results soon.

[MrMitchell]

The user name is persistent in the popup box, but the password is not. There is a box on that popup to check "trusted connection" but it resets each time that file is reopened. I will post my results soon.

Trusted Connection is not what I was referring to...

From the Data Quick Access Toolbar click Connections. The shortcut for me is ALT, then A, then O.

Choose your connection in the "Workbook Connection" box. Click Properties.

In the Connection Properties tab change to the Definition tab.

The "Save Password" check box is right under the connection string.

[meatsack]

Ok, great Mr. MrMitchell, I have it. Since it was Excel 2003 Basic Edition it was a little different. I had to click on my "data formula or whatever" Righ click, that brings up a pull down menu. I select "Data Range Properties. . ." Then I select "Saved Password" under the Section titled "Query definition" That has now made my query have a persistent password, whereas before it only had my logon id persistent in the pop up window. Now I now longer have a pop up window to deal with. I am greatly indebted to you. I will try and pay it forward Like old Ben.