Viktor
10-31-2013, 10:06 AM
Hi everyone,
Beginner in programming here.
I want to import data from roughly 2'000 URLs as part of my thesis. Since they are all different, I used Ctrl+U in my browser and copied all URLs into column A in one of my sheets ("Notification links").
Now I would like to import data into new and separate worksheets from each URL. i.e. Data from URL contained in cell A1 to be imported in the newly created Sheet1, A2 to Sheet2 etc.
Here is the code I did for inserting 2 entries. The points where I get errors, I have market with *'s
For i = 1 To 2
Sheets.Add After:=Sheets(Sheets.Count)
Set Link = Sheets("Notification links").Cells(i, 1)
' Importing the data from the variable "Link"
' *
Sheets("Sheet(i)").Select
' **
With ActiveSheet.QueryTables.Add(Connection:= _
"Link" _
, Destination:=Range("$A$1"))
.Name = "market-news-detail.html?announcementId=11724575"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next i
End Sub
*I keep getting a Run-time error 9: Subscript out of range
**Even if I remove the variable from the sheet name to resolve the first error, here I get another error Run-Time error 1004: Application defined or object defined error.
Beginner in programming here.
I want to import data from roughly 2'000 URLs as part of my thesis. Since they are all different, I used Ctrl+U in my browser and copied all URLs into column A in one of my sheets ("Notification links").
Now I would like to import data into new and separate worksheets from each URL. i.e. Data from URL contained in cell A1 to be imported in the newly created Sheet1, A2 to Sheet2 etc.
Here is the code I did for inserting 2 entries. The points where I get errors, I have market with *'s
For i = 1 To 2
Sheets.Add After:=Sheets(Sheets.Count)
Set Link = Sheets("Notification links").Cells(i, 1)
' Importing the data from the variable "Link"
' *
Sheets("Sheet(i)").Select
' **
With ActiveSheet.QueryTables.Add(Connection:= _
"Link" _
, Destination:=Range("$A$1"))
.Name = "market-news-detail.html?announcementId=11724575"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next i
End Sub
*I keep getting a Run-time error 9: Subscript out of range
**Even if I remove the variable from the sheet name to resolve the first error, here I get another error Run-Time error 1004: Application defined or object defined error.