VBA to copy url from excel sheet and open in webpage and copy source to notepad

[dreamer_1458]

Hi,

I got a task in hand to solve with VBA. Lets say i have a excel file with file name 12234 which contains 50 url's from A1:A50.
Want to copy each url in webpage (chrome, firefox or IE), copy source code to notepad & close the webpage & loop the same for all url's in file.

Here i reached so far :-
Problem with this code is, i have to mention url in the code itself & want to navigate it from excel file itself.



Any help is highly appreciated.

Thanks
Dreamer_1458

[Kenneth Hobs]

Welcome to the forum!

If I understand, you want to get the source text for all the urls in A1:A50.

Sub Main()
Dim r As Range, c As Range, sPath As String, sFile As String

sPath = Environ("temp") & "\" 'Include trailing backslash.

'Add dummy urls in a new sheet.
Range("A1").Value2 = "http://www.vbaexpress.com/forum/showthread.php?60247" _
& "-VBA-to-copy-url-from-excel-sheet-and-open-in-webpage-and-copy-source-to-notepad"
Range("A2").Value2 = "https://msdn.microsoft.com/en-us/library/aa265018(v=vs.60).aspx"

Set r = Range("A1", Range("A1").End(xlDown))
For Each c In r
'Uniquely name the file to contain the text from the url.
sFile = sPath & c.Address(False, False) & ".txt"
URLsourceToFile c.Value2, sFile
Next c

MsgBox "Files located in: " & sPath
End Sub


Sub URLsourceToFile(url As String, toFile As String)
'requires reference to winhttp.dll in Microsoft WinHTTP Services, version 5.1
Dim Request As New WinHttpRequest, s As String
Dim sa() As String

Request.Open "GET", url
Request.Send
Request.WaitForResponse

'Get text
s = Request.ResponseText
'Write text toFile
CreateObject("Scripting.FileSystemObject").CreateTextFile(toFile).Write s
End Sub

[dreamer_1458]

Hi Kenneth,

Thanks for your reply !!!

Tried the code & got following error :-

compile error:


User-defined type not defined, Following is the image.
20006
Pls help.

Thanks.

[mdmackillop]

Set a reference to MicrosoftWinHTTP - see the line above your highlight.