.IQY loop in VBA?

[SteveG]

I am using Excel 2000 and XP. I have just learnt how to use an .iqy
file to extract data from the web. To really benefit from automation
I want to create a loop using slightly different web adresses each
time. I have recorded the query as a macro and get:

Sub Macro1()
With Selection.QueryTable
.Connection = "URL;http://www.skyscanner.net/gbp/flights/ABZ/
LTN"
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub


Ok - so far so good. By varying ABZ and LTN in the web address I get
a
different set of results.


I have a table containing these two variables in a 25R x 2C table
called 'sectors' on a separate sheet 'OAG'


How do I create a loop to work systematically down the 'sectors'
table, recording the inputs and creating an output for each input
pair?


The output data block is 11R x 7 C so I will have to jump 12 rows
before each loop unless there is a neat way to put the data into a
single row.

Any ideas?

Steve

 

[Don Guillett]

This is the sort of thing I do daily for my clients. Once your query is
established all you need is the loop to refresh it with the variables and
then copy elsewhere. You do NOT need the other lines in the query. I would
even hide the fetch sheet and just have this assigned to a button on the
destination sheet. If you need more help send me your workbook to my private
email below.

Sub getvariables()
For Each c In Sheets("oag").Range("a2:a14")
v1 = c
v2 = c.Offset(, 1)
With Sheets("sheet1").QueryTables(1)'chg to your fetch sheet name
.Connection = "URL;http://www.skyscanner.net/gbp/flights/" & v1 & "/" & v2
.Refresh BackgroundQuery:=False
End With
'copy your data elsewhere
Next c
End Sub

 

[SteveG]

Don,

Thanks for your e-mail - I should have followed-up with a post much
earlier.

Yes, I managed to get the routine to work and recovered about 2800
data points in a few minutes! As always the interpretation of the
data will take longer!

I have just bought 'Excel VBA for Dummies' and am ploughing my way
through. I have also signed-up for a course but am still unsure when
that will happen.

Many thanks for your help. I have used Excel for years but have only
just begun to understand how import it is to use VBA.

Again many, many thanks

Steve

 

[Don Guillett]

Glad I was able to help