Al

have you ever tried webqueries?
(data/import external/new webquery)

once you've got that working then
it's very easy to automate
(you just change the connectstring and refresh the query)

Note that when you add a querytable it also adds a range name,
which is not cleared when you delete the query.

Also note the importance of backgroundquery...
if you set that to TRUE your code will continue before the query
has completed..




Sub Demo()
Dim qt As QueryTable
Dim i As Integer

With ActiveSheet.QueryTables
For i = .Count To 1 Step -1
With .Item(i)
On Error Resume Next
.Destination.EntireRow.Delete
On Error GoTo 0
.Delete
End With
Next

Set qt = .Add(Connection:="URL;", Destination:=Range("A1"))
End With
With qt
.Connection = "URL;http://www.devguru.com/home.asp"
.Name = "DevGuru"
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

MsgBox "Done"


End Sub





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Al <none wrote:

I've a workbook called "Wheels".
It contains multiple sheets.
One sheet named "Values" & is setup such that Col A has hyperlinks and
the remaining columns are to contain the data from a web page related
to that link.

Another sheet is called "Import".

I've written code (let's call it "Sub Transfer()" )that opens the web
page from the link using:

Selection.Hyperlinks(1).Follow NewWindow:=False,
AddHistory:=True


I then MANUALLY do a SELECT ALL & COPY in IE.

My code then pastes the web page, as text, onto Sheet2 using:

Columns("A:A").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False,
_
DisplayAsIcon:=False

From this sheet, I manipulate the data & paste the required info onto
the sheet "Values".

I then MANUALLY close the web page. (I've got over 1K of links so I
can't keep them open in IE)

My code then selects the next hyperlink & I repeat the process.

How might I use VBA to automate the MANUAL steps?