Web Query
 

Hi Excel Genius's

I am not sure if this is possible with VBA but I'm sure
someone can point me in the right direction.

I have a list of 98 web address in sheet 1 column A

What i would like to do is add a new sheet for each web
address and then add a web query to pick up a table. The
table is allways in the same location on the web page but
I dont know how the web queries work (Name, loaction on
page, etc??)

Can anyone help me? If the web query bit cant be done is
it possible for someone to give me some code for adding
the sheets from the list in column A?

Thanks so much

Web Query
 

Give us a sample (4-5) of the url's involved. And, a bit more info of the
project would be helpful. You may be making this more difficult than it has
to be.


--
Don Guillett
SalesAid Software

"Jimbo2001" wrote in message
...
Hi Excel Genius's

I am not sure if this is possible with VBA but I'm sure
someone can point me in the right direction.

I have a list of 98 web address in sheet 1 column A

What i would like to do is add a new sheet for each web
address and then add a web query to pick up a table. The
table is allways in the same location on the web page but
I dont know how the web queries work (Name, loaction on
page, etc??)

Can anyone help me? If the web query bit cant be done is
it possible for someone to give me some code for adding
the sheets from the list in column A?

Thanks so much

Web Query
 

I am using Moneyextra.com to set up web queries for all UK
share prices by sectors.

I have the list off all the address's of the sectors in
Sheets 1 ie:
http://www.moneyextra.com/stocks/sectors/AEROSPACE.html,
http://www.moneyextra.com/stocks/sec...OLDMINING.html
and
http://www.moneyextra.com/stocks/sec...ERROUSMETALS.h
tml

Each of these pages has a table on it detailing all of the
Shares that fall in that Sector which I would like to set
up a web query to excel. Ideally I would like each sector
to be a seperate sheet.

Thanks so much and let me know if you need more info.


-----Original Message-----
Give us a sample (4-5) of the url's involved. And, a bit
more info of the
project would be helpful. You may be making this more
difficult than it has
to be.


--
Don Guillett
SalesAid Software

"Jimbo2001" wrote in message
...
Hi Excel Genius's

I am not sure if this is possible with VBA but I'm sure
someone can point me in the right direction.

I have a list of 98 web address in sheet 1 column A

What i would like to do is add a new sheet for each web
address and then add a web query to pick up a table. The
table is allways in the same location on the web page
but
I dont know how the web queries work (Name, loaction on
page, etc??)

Can anyone help me? If the web query bit cant be done is
it possible for someone to give me some code for adding
the sheets from the list in column A?

Thanks so much


.

Web Query
 

I have managed to do it with the code below for the examples you gave. I
suspect some of the QueryTables lines can be deleted but, being a newbie
myself, I don't want to mess around with them. Obviously you have to change
"For i = 3 To 1 Step -1" to "For i = 98 To 1 Step -1" or whatever number of
links you have in the spreadsheet. Also the name for the worksheet need to
be changed from "sheet1" to whatever it's called in your workbook. You can
also see that I've formatted some of the columns otherwise the imported
tables are not easy to read.

What I am not sure is if you can import 98 sheets without any problem or you
have to do it in several batches. Another thing is the imported tables are
not easy to read without being

---
Sub WebImport()

Dim hLink As String
Dim i As Long

For i = 3 To 1 Step -1
hLink = Worksheets("sheet1").Cells(i, 1).Text
Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:="URL;" & hLink, _
Destination:=Range("A1"))
.Name = "news.bbc.co.uk"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "11"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Columns("D:M").NumberFormatLocal = "0.00_ "
Next i

End Sub