Hi - I am trying to get addresses for all NHS Trusts (UK Healthcare
organisatins) using web queries.

Thus I have built a loop which changes the url for each iteration.

While is kind of works... the problem is:

a) I get a load of other junk back I don't need
b) Unless I specify a really high row value (using jj) each on each
itteration, the output seems to be shifted along a number of columns

Anyone have any views as to what I can do to make this work a little better.

Examples of the urls a

http://www.nhs.uk/root/localnhsservi...ite.asp?id=RTV
http://www.nhs.uk/root/localnhsservi...ite.asp?id=RGT
http://www.nhs.uk/root/localnhsservi...ite.asp?id=REM


The last 2 chars change for each trust of which there are 264

I have the following code:

Sub test1()

Dim hVar, tName As String
Dim ii, jj As Integer

jj = 2

For ii = 2 To 5 /*
testing - this will become about 300 */

hVar = Sheets("nhs_List").Range("C" & ii) /* takes url from a
worksheet cell */
tName = Sheets("nhs_List").Range("A" & ii) /* Takes trust name from a
worksheet cell */

Range("E" & jj).Value = tName /* puts name in cell
e2, e82, e162... etc

With ActiveSheet.QueryTables.Add(Connection:= _ /* web query which
parses hvar and gets data from each web location*/
"URL;" & Trim(hVar), _
Destination:=Sheets("nhs_list").Range("E" & jj))
.Name = "5 Boroughs Partnership NHS Trust"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

'range("E2").Select /* test code...
*/
'ActiveCell.SpecialCells(xlLastCell).Select
'Range("R300").Select

jj = jj + 80 /* increment
row where next query will be put by 80 */

Next
End Sub

Send me your workbook and I will have a look. I do this sort of thing with
stock tickers.
However, I will be out for awhile today.

Just what info are you trying to get and where do you want it?
The way to do this is use a dummy page to import, use a macro to get what
you want & do it again.


--
Don Guillett
SalesAid Software

"Andreww" wrote in message
...
Hi - I am trying to get addresses for all NHS Trusts (UK Healthcare
organisatins) using web queries.

Thus I have built a loop which changes the url for each iteration.

While is kind of works... the problem is:

a) I get a load of other junk back I don't need
b) Unless I specify a really high row value (using jj) each on each
itteration, the output seems to be shifted along a number of columns

Anyone have any views as to what I can do to make this work a little
better.

Examples of the urls a

http://www.nhs.uk/root/localnhsservi...ite.asp?id=RTV
http://www.nhs.uk/root/localnhsservi...ite.asp?id=RGT
http://www.nhs.uk/root/localnhsservi...ite.asp?id=REM


The last 2 chars change for each trust of which there are 264

I have the following code:

Sub test1()

Dim hVar, tName As String
Dim ii, jj As Integer

jj = 2

For ii = 2 To 5 /*
testing - this will become about 300 */

hVar = Sheets("nhs_List").Range("C" & ii) /* takes url from a
worksheet cell */
tName = Sheets("nhs_List").Range("A" & ii) /* Takes trust name from a
worksheet cell */

Range("E" & jj).Value = tName /* puts name in cell
e2, e82, e162... etc

With ActiveSheet.QueryTables.Add(Connection:= _ /* web query which
parses hvar and gets data from each web location*/
"URL;" & Trim(hVar), _
Destination:=Sheets("nhs_list").Range("E" & jj))
.Name = "5 Boroughs Partnership NHS Trust"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

'range("E2").Select /* test
code...
*/
'ActiveCell.SpecialCells(xlLastCell).Select
'Range("R300").Select

jj = jj + 80 /*
increment
row where next query will be put by 80 */

Next
End Sub

Don - thanks for the offer. I've actually pretty much done the web query
part of the proble, I took the approach you suggested and drew data into a
dummy page and am now at the point of reformatting into something sensible.

I would very much appreciate your view of what I have done so will send you
the xls when I have got a liitle further.

Cheers

Andrew
www.jmdata.co.uk


"Don Guillett" wrote in message
...
Send me your workbook and I will have a look. I do this sort of thing with
stock tickers.
However, I will be out for awhile today.

Just what info are you trying to get and where do you want it?
The way to do this is use a dummy page to import, use a macro to get what
you want & do it again.


--
Don Guillett
SalesAid Software

"Andreww" wrote in message
...
Hi - I am trying to get addresses for all NHS Trusts (UK Healthcare
organisatins) using web queries.

Thus I have built a loop which changes the url for each iteration.

While is kind of works... the problem is:

a) I get a load of other junk back I don't need
b) Unless I specify a really high row value (using jj) each on each
itteration, the output seems to be shifted along a number of columns

Anyone have any views as to what I can do to make this work a little
better.

Examples of the urls a

http://www.nhs.uk/root/localnhsservi...ite.asp?id=RTV
http://www.nhs.uk/root/localnhsservi...ite.asp?id=RGT
http://www.nhs.uk/root/localnhsservi...ite.asp?id=REM


The last 2 chars change for each trust of which there are 264

I have the following code:

Sub test1()

Dim hVar, tName As String
Dim ii, jj As Integer

jj = 2

For ii = 2 To 5 /*
testing - this will become about 300 */

hVar = Sheets("nhs_List").Range("C" & ii) /* takes url from a
worksheet cell */
tName = Sheets("nhs_List").Range("A" & ii) /* Takes trust name from
a
worksheet cell */

Range("E" & jj).Value = tName /* puts name in
cell
e2, e82, e162... etc

With ActiveSheet.QueryTables.Add(Connection:= _ /* web query which
parses hvar and gets data from each web location*/
"URL;" & Trim(hVar), _
Destination:=Sheets("nhs_list").Range("E" & jj))
.Name = "5 Boroughs Partnership NHS Trust"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

'range("E2").Select /* test
code...
*/
'ActiveCell.SpecialCells(xlLastCell).Select
'Range("R300").Select

jj = jj + 80 /*
increment
row where next query will be put by 80 */

Next
End Sub

Just send a copy of this along with it so I will know what I'm looking at.
I might be able to clean it up a bit for you.

--
Don Guillett
SalesAid Software

"Andreww" wrote in message
...
Don - thanks for the offer. I've actually pretty much done the web query
part of the proble, I took the approach you suggested and drew data into a
dummy page and am now at the point of reformatting into something
sensible.

I would very much appreciate your view of what I have done so will send
you
the xls when I have got a liitle further.

Cheers

Andrew
www.jmdata.co.uk


"Don Guillett" wrote in message
...
Send me your workbook and I will have a look. I do this sort of thing
with
stock tickers.
However, I will be out for awhile today.

Just what info are you trying to get and where do you want it?
The way to do this is use a dummy page to import, use a macro to get
what
you want & do it again.


--
Don Guillett
SalesAid Software

"Andreww" wrote in message
...
Hi - I am trying to get addresses for all NHS Trusts (UK Healthcare
organisatins) using web queries.

Thus I have built a loop which changes the url for each iteration.

While is kind of works... the problem is:

a) I get a load of other junk back I don't need
b) Unless I specify a really high row value (using jj) each on each
itteration, the output seems to be shifted along a number of columns

Anyone have any views as to what I can do to make this work a little
better.

Examples of the urls a

http://www.nhs.uk/root/localnhsservi...ite.asp?id=RTV
http://www.nhs.uk/root/localnhsservi...ite.asp?id=RGT
http://www.nhs.uk/root/localnhsservi...ite.asp?id=REM


The last 2 chars change for each trust of which there are 264

I have the following code:

Sub test1()

Dim hVar, tName As String
Dim ii, jj As Integer

jj = 2

For ii = 2 To 5 /*
testing - this will become about 300 */

hVar = Sheets("nhs_List").Range("C" & ii) /* takes url from a
worksheet cell */
tName = Sheets("nhs_List").Range("A" & ii) /* Takes trust name
from
a
worksheet cell */

Range("E" & jj).Value = tName /* puts name in
cell
e2, e82, e162... etc

With ActiveSheet.QueryTables.Add(Connection:= _ /* web query
which
parses hvar and gets data from each web location*/
"URL;" & Trim(hVar), _
Destination:=Sheets("nhs_list").Range("E" & jj))
.Name = "5 Boroughs Partnership NHS Trust"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

'range("E2").Select /* test
code...
*/
'ActiveCell.SpecialCells(xlLastCell).Select
'Range("R300").Select

jj = jj + 80 /*
increment
row where next query will be put by 80 */

Next
End Sub