Copy/close web pages
 

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?

Copy/close web pages
 

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?

Copy/close web pages
 

No I haven't tried webqueries but since your reply I looked into them.

On comment I read of note is that they are one of the least publicized
features of Excel.



That said I started to look into working with them. But it's all new to me
at this point.

So if you wouldn't mind, could you explain a few lines of your Sub()?

And if you could refer me to some text or site where I could learn, that
would be even great



As to the code:

I am correct in assuming your 1st "With/End With group of commands" is the
"loop" to close the last link?



I'm confused about the lines:

.Connection = "URL;http://www.devguru.com/home.asp"
.Name = "DevGuru"

What 's the purpose of .Connection if the Set qt is .Add(Connection:="URL;",
Destination:=Range("A1"))

What's the purpose of .Name



BTW, that's for the intro & the help.


"keepITcool" wrote in message
...
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?

Copy/close web pages
 

With object
.property1 = value
.property2 = value
.method

end with
... a syntax to reference an object once, then manipulate several
properties or methods on the object.

in my example I split the ADD line and the line where you set the actual
url. No use really other then to demo that you can change the url for an
existing query...

For your original purpose you could have 1 wq.
With code you could iterate an array of connectstrings.
and dump retrieved data in another sheet.



the .Name is set so you can later refer to the table by it's name.

With Activesheet.QueryTables("MultiPurposeWebQuery")
.Connection = "URL;http://support.microsoft.com"
end with



keepITcool

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


"AG" wrote:

No I haven't tried webqueries but since your reply I looked into them.

On comment I read of note is that they are one of the least publicized
features of Excel.



That said I started to look into working with them. But it's all new
to me at this point.

So if you wouldn't mind, could you explain a few lines of your Sub()?

And if you could refer me to some text or site where I could learn,
that would be even great



As to the code:

I am correct in assuming your 1st "With/End With group of commands" is
the "loop" to close the last link?



I'm confused about the lines:

.Connection = "URL;http://www.devguru.com/home.asp"
.Name = "DevGuru"

What 's the purpose of .Connection if the Set qt is
.Add(Connection:="URL;", Destination:=Range("A1"))

What's the purpose of .Name



BTW, that's for the intro & the help.


"keepITcool" wrote in message
...
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?