dimanche 19 avril 2015

For Next Loop not working in VBA

So I've done SOME work with VBA in the past with moderate success, but am still very much an amateur. I am attempting to automate the pull of data from multiple pages of a website into a workbook. On one sheet titled "Events" I have 3 columns, the third of which (column C) is where I want the data pulled from, in the format "URL;http://ift.tt/1DrzSyj". I attempted to use the following code to go down my 108 rows, fetching all data:

Sub GetData()
For x = 1 To x = 108
mystr = Cells(x, 3)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Cells(x, 2)
With ActiveSheet.QueryTables.Add(Connection:= _
mystr, Destination:=Range( _
.Name = "rankings"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next x
End Sub

unfortunately, upon running it, nothing happens. I've attempted to isolate it, and by setting x to any single number, it will work and create a single sheet. When attempting the run the loop however, as I said, I don't get a result. Any suggestions would be VERY appreciated.

BONUS question: I'm sure this wouldn't likely be TOO difficult, but if instead of creating individual sheets for each new import, I wanted the data from all webpages to go into the same sheet, how might I go about that? (All data will be in the same format, (that is to say, same number of columns, though the amount of rows differes from page to page.

Thank you so much for any help you can provide!

Aucun commentaire:

Enregistrer un commentaire