I am having trouble with the following code
It is stalling on
Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=Range("D" & m))
But I cant seem to figure out why.
I have a list of websites in column A and this is desinged to pull info from each of these sites and put it into column D in the corresponding row.
Any help would be appreciated.
I know I probably havnt described this very well but I have made a very similiar macro work for me.
Thanks
Impossible is Nothing
Code:
Sub getitems()
Dim WSD As Worksheet
Dim WSW As Worksheet
Dim QT As QueryTable
Sheets("Sheet1").Select
For m = 1 To Range("C1").Value
Select Case m
Case Else
mystr = m
End Select
MyName = "Query" & m & "A"
ConnectString = Range("A" & m).Value
' On the Workspace worksheet, clear all existing query tables
For Each QT In ActiveSheet.QueryTables
QT.Delete
Next QT
' Define a new Web Query
Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=Range("D" & m))
With QT
.Name = MyName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With
' Refresh the Query
QT.Refresh BackgroundQuery:=True
Next m
End Sub
Set QT = ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=Range("D" & m))
But I cant seem to figure out why.
I have a list of websites in column A and this is desinged to pull info from each of these sites and put it into column D in the corresponding row.
Any help would be appreciated.
I know I probably havnt described this very well but I have made a very similiar macro work for me.
Thanks
Impossible is Nothing