Copy paste text from a list of URLs

[Stephan de Wit]

Hi all,

I am new here and I have one question, probably the most simple one ever but I got stuck.

For hours I've been searching on the web but all answers are too complicated for what I want.

I have a list of 50.000 urls in column A of the worksheet. All urls are the same structure and I would like to copy all data of the page and copy it in a column.

Sheet URLs - in column A a list of all URLs.
Sheet data - in column A all text of cell A1(URLs) , in column B all text of cell A2(URLs) etc.

I have a mac and Excel 2016 and unfortunately there's no option anymore "Get external data by web". So I can't record a macro to see how it looks like for the first URL.

Another option I tried: I paste one url in a Word doc and saved it as a txt file in the Queries folder. In Excel I opened the saved Query but it didn't show any data.

Who can help me with this issue? Thanks in advance!



I found a similar macro:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub Macro1()
'
' Macro1 Macro
'
'
Dim Erw, Frw, Lrw
Drw =1
Frw =1
Lrw = Range("A"& Rows.Count).End(xlUp).Row
For Erw = Frw To Lrw

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;"& Range("A"& Erw).Value, Destination:=Range("G"& Drw))
.Name =""
.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 ="6"
.WebPreFormattedTextToColumns =True
.WebConsecutiveDelimitersAsOne =True
.WebSingleBlockTextImport =False
.WebDisableDateRecognition =False
.WebDisableRedirections =False
.Refresh BackgroundQuery:=False
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">EndWith
Drw = Drw +80
Next Erw

EndSub</code>

</code><code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">The record stops at this row "</code>.PreserveFormatting =True "