Copy / Paste from web page
 

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

Copy / Paste from web page
 

I went to your urlimported into excelrecorded a macro while doing
dataimport external dataedit queryselecting tableselecting 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
Microsoft MVP Excel
SalesAid Software

"Ted Metro" wrote in message
...
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

Copy / Paste from web page
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
I went to your urlimported into excelrecorded a macro while doing
dataimport external dataedit queryselecting tableselecting 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
Microsoft MVP Excel
SalesAid Software

"Ted Metro" wrote in message
...
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