.iqy files repeating the first Query only in Excel 2007

[MarkE]

Hi.

I have .iqy files which I have been using in an automated fashion in Excel 2003 for years. Now in Excel 2007, when I try to run multiple .iqy queries, the first repeats over and over. The only way I've found to get to the 2nd, 3rd, etc. is to shut down and restart Excel.

The same problem is described in this thread from early last year:

http://www.mrexcel.com/forum/showthread.php?t=464196

I've found that if I move the .iqy files to the local computer's hard drive, they work fine. But if they're on the network, the FIRST query repeats, but the 2nd, 3rd, etc. are never retrieved.

Does anyone know a solution to this?

The code, which works fine in Excel 2003, is posted below.

Thanks,
Mark




Sub sbQry(stName As String, stQry As String, stRepOwner As String, Optional stMessage As String = "", Optional blUseParameters As String = False, Optional ByRef arParamsToUse)
'dimension variables

Dim shQry As Worksheet
Dim qryQry As QueryTable
Dim i As Integer


'add a new worksheet

Worksheets.Add Before:=Sheets(1)
ActiveSheet.Name = stName
Set shQry = ActiveSheet
Set qryQry = shQry.QueryTables.Add(Connection:="FINDER;" & stQry, Destination:=shQry.Range("a1"))


'set parameters

qryQry.Parameters(1).SetParam xlConstant, frmInfo.txtUsername.Value
qryQry.Parameters(2).SetParam xlConstant, frmInfo.txtPassword.Value
qryQry.Parameters(3).SetParam xlConstant, frmInfo.txtDatabase.Value
qryQry.Parameters(4).SetParam xlConstant, frmInfo.txtResponsibility.Value


'add additional parameters

If blUseParameters Then

For i = 0 To UBound(arParamsToUse, 1) Step 1

qryQry.Parameters(arParamsToUse(i, 0)).SetParam xlConstant, frmInfo.Controls(arParamsToUse(i, 1)).Value

Next i

End If

'display a wait message

If stMessage <> "" Then

frmMessage.lblMessage.Caption = stMessage

End If

frmMessage.Show
DoEvents


'retrieve the data
qryQry.Refresh BackgroundQuery:=False
qryQry.Delete

'name the data range

shQry.UsedRange.Name = stName

'unload message form

Unload frmMessage


'check if the query returned

If Range("a3").Value = "Invalid Web Query. Workbook does not exist" Then

MsgBox "Excel was not able to access the Discoverer report." & Chr(10) & "Please contact " & stRepOwner & " and ask him to share the Discoverer report with you."
End

ElseIf Range("a3").Value = "Authentication Failed. Failed to connect to database - Unable to connect to Oracle Applications database: invalid username/password." Then
MsgBox "Excel was not able to access the Discoverer report." & Chr(10) & Chr(10) & "Invalid username/password."
End

End If

'set freeze panes

Range("a2").Select
ActiveWindow.FreezePanes = True
End Sub

 

[MarkE]

I have been able to work around this problem. I noticed that the .iqy files do work, if they are stored on the local drive, not on the network.

So, I edited the code so that it copies the files local, and then runs the local file.

Microsoft has acknowledged a product defect - the Microsoft guy got the same results - as soon as he ran a networked .iqy, the returned data continued to be that data set.