PDA

View Full Version : Webquery



uaku
02-24-2012, 12:23 PM
I wrote this webquery, its working great. It pulls the data I want and places it on sheet2 column A.
however when the loop runs the second time, it shifts the data A to the right and puts the new connection data on column A.
instead of getting one column at the end, I am getting data in 20 columns.
How to fix this issue? Thanks for your help

Sub getwebdata()
'

Dim sutraNo As String
Dim QT As QueryTable
Sheets("Sheet3").Select

For Each CVal In Sheets("Sheet3").Range("D6:D27")
sutraNo = CVal.Value

Sheets("Sheet2").Select
ActiveSheet.QueryTables(1).Delete

' On the Workspace worksheet, clear all existing query tables


nURL = " A working url here " & sutraNo
Set QT = ActiveSheet.QueryTables.Add(Connection:=nURL, Destination:=Range("$A$1"))
With QT
.Name = "1-1-1_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

End With



Next CVal
End Sub

uaku
02-24-2012, 01:20 PM
Nevermind, I got it. I had to change it to this ".RefreshStyle = xlOverwriteCells"