Hmmm gives me an error when I try to use urlString within the query (1004 object error)
Option Explicit
Option Base 0
'http://www.vbaexpress.com/forum/showthread.php?t=36830
Sub AddSheets()
Dim element As Variant, aURLs() As Variant, aNames() As Variant
Dim wsName As String, i As Long
aURLs() = WorksheetFunction.Transpose(Range("URLs"))
aNames() = WorksheetFunction.Transpose(Range("Names"))
Application.DisplayAlerts = False
For i = LBound(aURLs) To UBound(aURLs)
If WorkSheetExists(CStr(aNames(i))) Then Worksheets(aNames(i)).Delete
Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = aNames(i)
MyQuery CStr(aURLs(i))
Next i
Application.DisplayAlerts = True
End Sub
Sub MyQuery(urlString As String)
'MsgBox urlString
With ActiveSheet.QueryTables.Add(Connection:= _
urlString _
, Destination:=Range("$D$1"))
.Name = _
urlString
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
'WorkSheetExists in activeworkbook:
Function WorkSheetExists(aSheetName As String) As Boolean
Dim ws As Worksheet
On Error GoTo notExists
Set ws = Worksheets(aSheetName)
WorkSheetExists = True
Exit Function
notExists:
WorkSheetExists = False
End Function