Web Query Doesn’t Load Unless I Open It 1st

[AG]

I have queries saved in the file C:\Documents and Settings\Al\Application
Data\Microsoft\Queries\MStar.
Individually they all work fine.

However, when I open a workbook and use its VBA macro to run the queries no
data is found.

I find that if I then go to one of my stored queries, open it directly and
then go back to my workbook and re-run the macro everything works fine.

Am I missing something in my code?

VBA:
Sub Large()
'
' Copies MStar total returns for funds


Application.DisplayAlerts = False
On Error Resume Next
Sheets("Large").Select
Sheets.Add
ActiveSheet.Select
ActiveSheet. Name = "Sheet1"

IQYFile = "C:\Documents and Settings\Al\Application Data\Microsoft\" & _
"Queries\MStar\RGAEX.iqy"

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;" & IQYFile, Destination:=Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range("A3:E7").Select
Selection.Copy
Sheets("Large").Select
Columns("B:B").Select
Selection.Find(What:="RGEAX", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 10).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete The code continues on for other
queries.

A sample of one of the queries would be:

WEB
1
http://quicktake.morningstar.com/Fun...&fdtab=returns

Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

Thanks for any help.

 

[Don Guillett]

I don't use iqy anyomore. Try putting your url directly into the macro

Application.DisplayAlerts = False
On Error Resume Next

Sheets.Add
ActiveSheet. Name = "Sheet1"

'untested
With activesheet.QueryTables.Add(Connection:="URL;" _
& "http://quicktake.morningstar.com/Fun...&fdtab=returns", _
Destination:=activesheet.range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = true
.Refresh BackgroundQuery:=False
.SaveData = True
End With

with Sheets("Large")
x=.Columns("B").Find(What:="RGEAX", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 10).Range("A1").address
Range("A3:E7").copy .range(x)
end with

UN tested
Your other queries could probably be in a loop>extract desired data>goto the
next one using one macro and one data sheet.