Hi All,
having a real problem with the below which I cant seem to solve. For some reason it doesn't seem to delete the query table at the very end of the code and the data just stays there
Sub test()
Const SheetName = "Sheet1"
Dim QT As QueryTable
'Get rid of all the QueryTables you've created that begin with the text "WWW.X-RATES"
For Each QT In Sheets("Data Reports").QueryTables
If Left(UCase(QT.Name), 11) = "WWW.X-RATES" Then QT.Delete
Next QT
'Select the correct worksheet
Worksheets("Data Reports").Select
'With the desired worksheet
With Sheets("Data Reports")
'Add a NEW Query Table connection to the www.x-rates.com website
With .QueryTables.Add(Connection:="URL;http://www.x-rates.com", Destination:=Range("$AM$36"))
.Name = "www.x-rates_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
'Call the data and populate it into the desired cells
.Refresh BackgroundQuery:=False
End With
End With
'Copy and Paste Item 1
Range("AN40").Copy
Range("W4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copy and Paste Item 1
Range("AO40").Copy
Range("W5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Get rid of all the QueryTables you've created that begin with the text "WWW.X-RATES"
For Each QT In Sheets("Data Reports").QueryTables
If Left(UCase(QT.Name), 11) = "WWW.X-RATES" Then QT.Delete
Next QT
End Sub
having a real problem with the below which I cant seem to solve. For some reason it doesn't seem to delete the query table at the very end of the code and the data just stays there
Sub test()
Const SheetName = "Sheet1"
Dim QT As QueryTable
'Get rid of all the QueryTables you've created that begin with the text "WWW.X-RATES"
For Each QT In Sheets("Data Reports").QueryTables
If Left(UCase(QT.Name), 11) = "WWW.X-RATES" Then QT.Delete
Next QT
'Select the correct worksheet
Worksheets("Data Reports").Select
'With the desired worksheet
With Sheets("Data Reports")
'Add a NEW Query Table connection to the www.x-rates.com website
With .QueryTables.Add(Connection:="URL;http://www.x-rates.com", Destination:=Range("$AM$36"))
.Name = "www.x-rates_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
'Call the data and populate it into the desired cells
.Refresh BackgroundQuery:=False
End With
End With
'Copy and Paste Item 1
Range("AN40").Copy
Range("W4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copy and Paste Item 1
Range("AO40").Copy
Range("W5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Get rid of all the QueryTables you've created that begin with the text "WWW.X-RATES"
For Each QT In Sheets("Data Reports").QueryTables
If Left(UCase(QT.Name), 11) = "WWW.X-RATES" Then QT.Delete
Next QT
End Sub