Multiple URL's to Get External Data

[rhysm144]

I Have column A filled with ~300 URL's (each in a new row).

I want to turn each of these URL's into a get external data query each in a new worksheet so they refresh consistently.

The only way I know how to do this is to save each url as a .iqy and to manually run a get external data on a new worksheet for each.

I was wondering if there was any way (likely vba) to automate this process?

Thank you!

[mancubus]

record a macro while manually doing this with one of the URLs.
then post the recorded code here (see my signature).

if the websites are publicly available sites upload your workbook (see my signature).

[rhysm144]

17317

I have attached my workbook,

I am poor with vba and am only able to record code moving the url to a new work book but unable to get external data and then loop

Thanks

[mancubus]

i cant post code. strange.

[snb]

Don't worry: crossposted at mrexcel &
http://www.excelguru.ca/forums/showthread.php?6846-Multiple-URL-s-to-Get-External-Data

[mancubus]

rhysm144

as per forum(s) rules when posting your question to multiple boards pls provide links to the threads in different forums.

[mancubus]

i cannot test the code as this site is blocked as per our corporate internet policies. but it should work.




Sub vbax_57423_import_from_web_multiple_URLs()

Dim i As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

For i = 1 To ws.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Left(ws.Range("A" & i).Value, 31)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & ws.Range("B" & i).Value, _
Destination:=Range("$A$1"))
.Name = Replace(ws.Range("A" & i).Value, " ", "_")
.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

End Sub


to refresh all connections in the workbook hit in Data/Connections
or
copy below to ThisWorkbook code modüle



Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
End Sub

[mancubus]

Mac?

we also have a dedicated forum for mac users?

[snb]

@Mancubus

You'd better use an array instead of reading from the worksheet.
Most of the Querytablearguments are defaults, so no need to incorporate them in the code.

A oneliner suffices:


Sub Macro1()
Sheet1.QueryTables.Add("URL;https://classic.startpage.com/ned", Range("$A$20")).Refresh
End Sub

[mancubus]

@snb

i just posted it because i have adopted it before seeing your post about cross-posting.

yeah you are right.
most of the time i post solutions taking the OP's expertise level into account. :dunno :)

:beerchug:

[snb]

@Manc

I prefer improving the expertise level (also of actual & later visitors). :whistle:

[mancubus]

sometimes so do i, sometimes don't. :devil2:

[mancubus]

lets do it then.



Sub Macro1()

Dim i As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

For i = 1 To ws.Cells(Rows.Count, 2).End(xlUp).Row 'last non blank cell's row num in Column B
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Left(ws.Range("A" & i).Value, 31)
ActiveSheet.QueryTables.Add("URL;" & ws.Range("B" & i).Value, Range("$A$1")).Refresh
Next

End Sub

[snb]

Let's 'improve' ( ;) ) it:


Sub M_snb()
sn=sheet1.cells(1).currentregion

For j = 1 To ubound(sn)
with sheets.Add(,sheets(sheets.Count))
.Name = Left(sn(j,1), 31)
.QueryTables.Add("URL;" & sn(j,2), .cells(1)).Refresh
end with
Next
End Sub

[rhysm144]

Thanks both for your help, all working now.

Apologies for cross-posting, I was unaware of the standard etiquette.

[mancubus]

Let's 'improve' ( ;) ) it

oh, i forgot the array.
:omg2::omg2:

you are right.

:hide:

[mancubus]

Thanks both for your help, all working now.

you are welcome.
pls mark the thread as solved from threadtools for future references.

[vlanier]

Hi all! I'm new to VBA, I'm doing something similar to the original poster however I have several thousand URLs I need to run through the external data query. I also only need two tables from each URL.

I have been successful by tweaking some of the code above. Thanks!! :thumb I ran my first 1000 successfully!I didn't know that excel could populate 1000 worksheet in 1 workbook!

I wanted to see if it is possible to allow my web-extract to populate below the preceding tables instead of a new sheet each time. My extract is only 15 rows, is there a way to alter the code to make the destination every 16th row?


Sample Extract 17943

CODE:
Sub vbax_57423_import_from_web_multiple_URLs()
Dim i As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

For i = 1 To ws.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Left(ws.Range("A" & i).Value, 31)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & ws.Range("B" & i).Value, _
Destination:=Range("$A$1"))
.Name = Replace(ws.Range("A" & i).Value, " ", "_")
.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 = "3,6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next


End Sub

Thank You!!

[offthelip]

Yes just change the line:

Destination:=Range("$A$1"))

to

Destination:=Range(cellls(1+((i-1)*16),1),cells(1+((i-1)*16),1))

This assumes your download is exactly 16 lines every time.
You also need to delete the worksheet.add line

[vlanier]

Thanks for responding so quickly. I tried to alter the code but i keep getting error messages. (syntax error, expected list separator, etc. )

Just to be clear, I do want one sheet to populate with the extraction code and I need my column A to be adjacent to the extraction results. Ideally it will look something like this 17947

copy of my code:

Sub vbax_57423_import_from_web_multiple_URLs() Dim i As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

For i = 1 To ws.Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & ws.Range("B" & i).Value, _
Destination:=Range(Cells(1+((i-1)*16),1),Cells(1+((i-1)*16),1))
.Name = Replace(ws.Range("A" & i).Value, " ", "_")
.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 = "3,6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next


End Sub