Copy / Paste from web page

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ted Metro - 11 Feb 2008 02:21 GMT

I'm having problems with the formatting of a web query inside of a macro.
Maybe this is best posted in the programming group, so I apologize if that is
the case.

When I go to a web page -- http://finance.yahoo.com/q/is?s=HD&annual -- and
select all (CTRL-A) and then 'Paste Special' as text into A1 I get everything
in column A, which is what I want.

When I create my external web query I choose to have no formatting but the
results are still broken out in several columns.

How can I write a macro to simply go to a website select all and copy that
site and then paste special as text into my excel sheet? Is that possible?

Have a good day,

Ted

Reply to this Message

Don Guillett - 11 Feb 2008 13:25 GMT

I went to your url>imported into excel>recorded a macro while doing
data>import external data>edit query>selecting table>selecting option to use
html and got this
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/11/2008 by Donald B. Guillett
Range("A4:B4").Select
With Selection.QueryTable
.Connection = "URL;http://finance.yahoo.com/q/is?s=HD&annual"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "15"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
'========
Now, since your query is established you may now just use
Sub refreshdata()
With ActiveSheet.QueryTables(1)
.Connection = "URL;http://finance.yahoo.com/q/is?s=HD&annual"
.Refresh BackgroundQuery:=False
End With
End Sub
=======
PERIOD ENDING 28-Jan-07 29-Jan-06 30-Jan-05
Total Revenue 90,837,000 81,511,000 73,094,000
Cost of Revenue 61,054,000 54,191,000 48,664,000

Gross Profit 29,783,000 27,320,000 24,430,000

If there are rows/columns you don't want, simply hide them
If you ask, OFF list, I can send you a workbook

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> I'm having problems with the formatting of a web query inside of a macro.
> Maybe this is best posted in the programming group, so I apologize if that
[quoted text clipped - 17 lines]
>
> Ted

Reply to this Message

Don Guillett - 11 Feb 2008 13:48 GMT

If you want other than HD and want it to be automatic, just put this into
the sheet module of the fetch sheet and when you type in hd or ibm or msft,
etc into cell a1 it will refresh for that symbol

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
With ActiveSheet.QueryTables(1)
'.Connection = "URL;http://finance.yahoo.com/q/is?s=HD&annual"

.Connection = "URL;http://finance.yahoo.com/q/is?s=" & Target & "&annual"
.Refresh BackgroundQuery:=False
End With
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I went to your url>imported into excel>recorded a macro while doing
>data>import external data>edit query>selecting table>selecting option to
[quoted text clipped - 58 lines]
>>
>> Ted

Reply to this Message