Richie(UK)
MrExcel MVP
- Joined
- May 17, 2002
- Messages
- 3,329
Hi all,
I've finally decided that its about time I became more familiar with using Excel to extract information from the Web. Specifically, I'm looking to get some P/E ratios for various categories of shares.
OK, I'm fine with actually importing the data. And I'm fine with refreshing the query. What I have had problems with is if I decide to delete an existing query and then use the same name for a new query.
The code I am using at the moment is as follows:
The first time that the code is executed a QueryTable with the name "Banks" is created. The next time I run it I would have anticipated a new QueryTable with the same name. However, what I get is "Banks_1". If I run it a third time then there is no "Banks" to delete and I get both "Banks_1" and then new "Banks_2".
Anybody come across this before? Better yet, does anybody know how to do it correctly?
I've finally decided that its about time I became more familiar with using Excel to extract information from the Web. Specifically, I'm looking to get some P/E ratios for various categories of shares.
OK, I'm fine with actually importing the data. And I'm fine with refreshing the query. What I have had problems with is if I decide to delete an existing query and then use the same name for a new query.
The code I am using at the moment is as follows:
Code:
Sub Test_Bank()
Const strMain As String = "URL;http://www.telegraph.co.uk/money/main.jhtml;"
Dim strSessionID As String, strMenuID As String, strMItemID As String, _
strView As String, strTRule As String, strGrid As String, _
strSearch As String, strURL As String
Const strQTName As String = "Banks"
Dim qt As QueryTable
On Error Resume Next
With Sheet5
For Each qt In .QueryTables
If qt.Name = strQTName Then
.Names(strQTName).RefersToRange.Clear
.Names(strQTName).Delete
qt.Delete
End If
Next qt
End With
On Error GoTo 0
'remove old
strSessionID = "sessionid=B2IF4CIIALBBHQFIQMFSM54AVCBQ0JVC?"
strMenuID = "menuId=243"
strMItemID = "menuItemId=2841"
strView = "view=SHARERESULTS"
strTRule = "targetRule=5"
strGrid = "grid=M2"
strSearch = "search=sectors/banks"
strURL = strMain & strSessionID & strMenuID & "&" & strMItemID & _
"&" & strView & "&" & strTRule & "&" & strGrid & "&" & strSearch
Call NewQT(strURL, strQTName, Sheet5.Range("A1"))
End Sub
Sub NewQT(strURL As String, strName As String, rngDest As Range)
With Sheet5.QueryTables.Add(Connection:=strURL, Destination:=rngDest)
.Name = strName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "12"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
The first time that the code is executed a QueryTable with the name "Banks" is created. The next time I run it I would have anticipated a new QueryTable with the same name. However, what I get is "Banks_1". If I run it a third time then there is no "Banks" to delete and I get both "Banks_1" and then new "Banks_2".
Anybody come across this before? Better yet, does anybody know how to do it correctly?