Trouble with QueryTables

[Sergikus]

I have a trouble. In one Excel document in one macro of this one document I use QueryTables object. Macro is linked to Workbook_Open(). When I run this macro in debug mode QueryTables object successfully retrieves data from DB. And when I run this macro without debug mode (normal mode; running by opening Excel document) Excel stops and asks me to select some ODBC connection from ODBC connections list. I need silence performing of macro with no questions from Excel.

Why does'nt Excel asks me in the first case and asks in the second case?

[lucas]

Could you post your Workbook Open code here?

[Sergikus]

Sorry, but I forgot notice, that I ran WorkBook without debug mode by *.bat file ("EXCEL.EXE summary.xls"). Below this
code:


[VBA]Private Sub Workbook_Open()
Call SUB_1()
End Sub

Sub SUB_1()
...
...
Dim Select_exp As String
Connection_BD = "ODBC;DSN=...;UID=...;PWD=..."
' "..." - values deleted

Select_exp = "select ... from ... where ... order ..."
With ActiveSheet.QueryTables.Add(Connection:=Connection_BD, Destination:=Range(Cells(col, 1), Cells(col, 1)))
.CommandText = Select_exp
.Name = "TR_range"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
' just after that Excel asks to make choice
End With
...
...
End Sub[/VBA]

[Jan Karel Pieterse]

SOmetimes it helps to call your sub_1 like this:

Application.Ontime Now(),"Sub_1"

[Sergikus]

Thanks for this advice (it's helpful function ), but I need run Excel each time with periods 5-10 minutes. I plan to use one *.bat file, that will perform Excel macro document by Scheduled Tasks manager. Using function Application.Ontime we need know when it will be run.

[Jan Karel Pieterse]

If the scheduler is opening that file, the ontime method will work as I wrote it.

[Sergikus]

Ok… Now I will try to explain clearly my problem I confront with. As I have understood, this problem doesn’t have any attitude to QueryTables object and has an attitude to Excels security, but for all that the QueryTables object becomes a sticking point. Following my thout… By now temporally forget about Workbook_Open() function, but let’s agree that one workbook WB_1.xls has macro SUB_1(), that uses QueryTables object for it’s performance. Let’s view two cases:
1) We open WB_1.xls and press Alt+F8. We select a macro in appeared window and press button Run. By the script of these actions from the side of user (i.e. myself), macro is performed successfully (silently) and retrieves data from DB.
2) We write WSH the following script:

    var objXL = WScript.CreateObject("Excel.Application");
    objXL.Visible = true;
    objXL.Workbooks.Open(path + " WB_1.xls");
    var xxx = objXL.Run("WB_1.xls!SUB_1()");
    objXL.Quit();

After the script is launched Excel is run and begins the performing macro SUB_1().When macro’s performing reaches the line «.Refresh BackgroundQuery:=False» (retrieve’s performing data from DB), Excel immediately outs ODBC connections list window with the request to chose appropriate ODBC-connection.

We have the following question: What should we do to make Excel silently perform macro SUB_1() in the second case do the same in the first case??