Solved: Read the URL in column G then paste the company name from website in column H

[Feelingcute]

Hi Folks,

Im working on a spreadsheet with around 1300 entries and basically what i need to do is get the company name from a webpage that is in link found inside the spreadsheet.

So the link is found in column G of my spreadsheet, and ideally the script would grab the company name from the linked webpage and paste the result in column H.

I've attached a sample of the data i am working on, if this is possible to do, your help would be much appreciated.

cheers.

[patel]

It's very slow but I can not do better
Sub Web_Query()
Dim cell As Range
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
LR = ws1.Cells(Rows.Count, "A").End(xlUp).Row
LR = 3
Application.DisplayAlerts = False
For Each cell In ws1.Range("G2:G" & LR)
Application.ScreenUpdating = False
hl = cell.Hyperlinks(1).Address
Set WB2 = Workbooks.Open(hl)
cname = Range("A4")
WB2.Close False
Application.ScreenUpdating = True
cell.Offset(0, 1) = cname
Next
Application.DisplayAlerts = True
End Sub

[snb]

Add a querytable in cell A20.
After that:
Sub M_snb()
For Each hp In Sheets(1).Hyperlinks
With Sheets(1).QueryTables(1)
.Connection = "URL;" & hp.Address
.Refresh False
End With
hp.Range.Offset(, 1) = Sheets(1).Cells(23, 1).Value
Next
End Sub

[patel]

I tried with querytable befor the above solution, but without succes

[Kenneth Hobs]

I tried a winhttp method last night but the page source text comes back encrypted in parts though manual page source text shows the proper data.

If not solved by noon or this evening, I may try an MSIE object method though it will be slower than my first idea.

[snb]

I can't believe you. ;)
See the attachment.

@ KH

What is 'noon' ?

[Kenneth Hobs]

Maybe I should have said during lunch break. See the first definition for noon in this case.

Noon -
1. midday (http://dictionary.reference.com/browse/midday).

2. twelve o'clock in the daytime.

3. the highest, brightest, or finest point or part: the noon of one's career.

4. Archaic. midnight: the noon of night.

[snb]

And now applied to my timezone ?

[Kenneth Hobs]

SNB, if you subtract my time of posting in my time from 12:00 PM, you can see how many hours it will be. It is the same for yours. Of course to be noon there on today for you, you need a time machine.

The query table was corrupted in your file snb. I don't know if mine will be any better but I have attached it. I made the query overwrite the previous query and put your code into a Module.

I suspect that my method might have worked. I didn't notice last night that the first entry had a blank title. In my method, I made it a sheet code to execute if the link was changed. To make it work for existing entries, one just cut and pasted to refresh the entries.

[patel]

@Patel
I can't believe you. ;)
See the attachment.
I'm sorry but ther'is a misunderstanding, I tried to add a query table via vba without success, now adding it manually your code works well, slow but well.

[snb]

I replaced the earlier attachment by a new one, hopefully this one is not corrupt.

[Feelingcute]

Awesome, thanks for the responses everyone.

Was able to get it working! the querytable was a great idea!