QueryTables.Add ohne Spaltenverschiebung

Sub Aktienkurse() 'Worksheets("Aktualisierung").Range("A9:C100").ClearContents With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;http://finance.yahoo.com/d/quotes.csv?s=NOVO-B.CO+fntn.de+utdi.de=X&f=snl1" _ , Destination:=Range("$A$9")) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;http://finance.yahoo.com/d/quotes.csv?s=DKKEUR=X+USDEUR=X&f=snl1" _ , Destination:=Range("$A$2")) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Alex,
so wie Du das machst, erstellst Du lauter Abfrage-Leichen, die zwar erstellt, aber nie wieder benutzt werden. Mit dem nachfolgenden Makro lösche erst mal alle Abfragen im Tabellenblatt 'Aktualisierung'.
Dabei wirst Du staunen, wieviele Abfragen sich bereits angesammelt habern:


Sub loeschenAlleQueries()
Dim qry As QueryTable, ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Aktualisierung")
For Each qry In ws.QueryTables
Debug.Print qry.Name
qry.Delete
Next qry
Set qry = Nothing
Set ws = Nothing
End Sub

Anschließend erstellst Du die Abfragen nach diesem Muster:



Sub Aktienkurse()

Dim qry As QueryTable, ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Aktualisierung")

ws.Range("A2:I100").ClearContents

On Error Resume Next

Set qry = ws.QueryTables("Yahoo_1")

On Error GoTo 0

If qry Is Nothing Then

'Abfrage existiert noch nicht

Set qry = ws.QueryTables.Add(Connection:= _

"TEXT;http://finance.yahoo.com/d/quotes.csv?s=NOVO-B.CO+fntn.de+utdi.de=X&f=snl1", _

Destination:=Range("$A$9"))

With qry

.Name = "Yahoo_1"

.FieldNames = True

.RowNumbers = False:    .FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False:  .SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 850

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

Else

'Abfrage existiert bereits

qry.Refresh BackgroundQuery:=False

End If

Set qry = Nothing

On Error Resume Next

Set qry = ws.QueryTables("Yahoo_2")

On Error GoTo 0

If qry Is Nothing Then

'Abfrage existiert noch nicht

Set qry = ws.QueryTables.Add(Connection:= _

"TEXT;http://finance.yahoo.com/d/quotes.csv?s=DKKEUR=X+USDEUR=X&f=snl1", _

Destination:=Range("$A$2"))

With qry

.Name = "Yahoo_2"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 850

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

Else

qry.Refresh BackgroundQuery:=False

End If

Set qry = Nothing

Set ws = Nothing

End Sub

Gruß von Luschi
aus klein-Paris

Beliebteste Forumthreads (12 Monate)

QueryTables.Add ohne Spaltenverschiebung

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread