Web query

Sub yahoo() URLYahoo = <a href=""http://de.rd.yahoo.com/finance/quotes_download/?http://de.finance.yahoo.com/d/quotes.csv?s=DE"">"http://de.rd.yahoo.com/finance/quotes_download/?http://de.finance.yahoo.com/d/quotes.csv?s=DE"</a> symbole = ActiveCell & ".F&f=sl1d1t1c1ohgv&e=.csv" URL = URLYahoo + symbole ActiveCell.Cells(1, 2).Select With Sheets("Data").QueryTables.Add(Connection:="URL;" & URL, _ Destination:=ActiveCell) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True ActiveCell.Select Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True End With End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Web query

17.06.2004 18:59:28

Otto

Tippfehler

17.06.2004 19:02:41

Otto

AW: Tippfehler

18.06.2004 10:58:14

Frank

AW: Tippfehler

20.06.2004 06:01:11

Björn

Hallo Frank,
Meine Testumgebung: Excel 9.0 SR-1, Multilingual unter Windows 2000 SP-4
was hältst Du von
Option Explicit


Sub Haupt()
Call Yahoo
End 

Sub

Sub Yahoo()
Dim rngQuelle As Range
Dim rngZiel As Range
Dim strUrlYahoo As String
Dim strSymbol As String
strUrlYahoo = "htt" & "p://de.rd.yahoo.com/" & _
"finance/quotes_download/?" & _
"htt" & "p://de.finance.yahoo.com/d/quotes.csv?" & _
"f=sl1d1t1c1ohgv&e=.csv&s="
With ThisWorkbook.Worksheets("Data")
While .QueryTables.Count > 0
.QueryTables(1).Delete
Wend
Set rngQuelle = .Cells(1, 1)
While Not IsEmpty(rngQuelle)
Set rngZiel = rngQuelle.Offset(0, 1)
strSymbol = rngQuelle.Value & ".F"
With .QueryTables.Add(Connection:="URL;" & strUrlYahoo & strSymbol, _
Destination:=rngZiel)
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
rngZiel.TextToColumns Destination:=rngZiel.Offset(0, 1), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, xlSkipColumn), Array(2, xlSkipColumn), _
Array(3, xlSkipColumn), Array(4, xlSkipColumn), _
Array(5, xlSkipColumn), Array(6, xlSkipColumn), _
Array(7, xlGeneralFormat), Array(8, xlSkipColumn), _
Array(9, xlSkipColumn))
Set rngQuelle = rngQuelle.Offset(1, 0)
Wend
End With
End 

Sub
Gruß Otto

Sub Yahoo() Dim rngQuelle As Range Dim rngZiel As Range Dim strUrlYahoo As String Dim strSymbol As String strUrlYahoo = "htt" & "p://de.rd.yahoo.com/" & _ "finance/quotes_download/?" & _ "htt" & "p://de.finance.yahoo.com/d/quotes.csv?" & _ "f=sl1d1t1c1ohgv&e=.csv&s=" With ActiveSheet While .QueryTables.Count > 0 .QueryTables(1).Delete Wend Set rngQuelle = .Cells(1, 1) While Not IsEmpty(rngQuelle) strSymbol = rngQuelle.Value & ".F" With .QueryTables.Add(Connection:="URL;" & strUrlYahoo & strSymbol, _ Destination:=rngQuelle.Offset(0, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With rngQuelle.Offset(0, 1).TextToColumns Destination:=rngQuelle.Offset(0, 1).Offset(0, 0), _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _ Comma:=False, Space:=False, Other:=False, _ FieldInfo:=Array(Array(1, xlSkipColumn), Array(2, xlSkipColumn), _ Array(3, xlSkipColumn), Array(4, xlSkipColumn), _ Array(5, xlSkipColumn), Array(6, xlSkipColumn), _ Array(7, xlGeneralFormat), Array(8, xlSkipColumn), _ Array(9, xlSkipColumn)) Set rngQuelle = rngQuelle.Offset(1, 0) Wend End With End Sub

Beliebteste Forumthreads (12 Monate)

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread