lundi 6 avril 2015

Using For loop through string +


Vote count:

0




First, I want to say that this is my first attempt at building vba code. I am trying to extract data from the web using a web query .Add(Connection,Destination,sql). What I want my code to do is to loop through the string 'str' containing stock tickers to be inserted into my url using a for loop and pasting the table data in the active sheet.


In addition, it would be an extra if I could create a new sheet for every url queried with the corresponding NYSE name.


Currently my code does not run because it is not extracting the data. I think the error is in how I am specifying the url using the loop index NYSE(i).


Thanks for any responses, advice, and suggestions.



Sub URL_Get_Query()


Dim NYSE(1 To 22) As String
NYSE(1) = "APC"
NYSE(2) = "APA"
NYSE(3) = "COG"
NYSE(4) = "CHK"
NYSE(5) = "XEC"
NYSE(6) = "CRK"
NYSE(7) = "CLR"
NYSE(8) = "DNR"
NYSE(9) = "DVN"
NYSE(10) = "ECA"
NYSE(11) = "EOG"
NYSE(12) = "XCO"
NYSE(13) = "MHR"
NYSE(14) = "NFX"
NYSE(15) = "NBL"
NYSE(16) = "PXD"
NYSE(17) = "RRC"
NYSE(18) = "ROSE"
NYSE(19) = "SD"
NYSE(20) = "SWN"
NYSE(21) = "SFY"
NYSE(22) = "WLL"
For i = 1 To 22
Debug.Print NYSE(i)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://ift.tt/1NYM4yk", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Next i
End Sub


asked 27 secs ago







Using For loop through string +

Aucun commentaire:

Enregistrer un commentaire