Copy / Paste from web page

[Ted Metro]

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

 

[Don Guillett]

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

 

[Don Guillett]

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