• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find specific Data copy and paste into Cell Macro VBA

M

Mateusz

New Member
Hello.

here is the thing:

I would like to this code do sth like:
1. Ask me for a website address
2. After it gets it, search for HTML codes Like:
- title
- img url
-etc...


copy the content of title and paste it into the Cell ("X1)
the same with the rest of data.

Please, try to remake this code to do what I expect it to.

For now it can not find the content of scribes.

Code: 
Select Code copy to clipboard
Private Sub blahblah_Click()
Dim ie As Object
Dim WB As Workbook
Dim WS As Worksheet
Set WB = Workbooks("FInalVersion_06_03_14_03_55_Duzo makr_2_chrome_2")
Set WS = WB.Sheets("Arkusz4")
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate "http://www.wp.pl"
Do While ie.Busy
Application.wait DateAdd("s", 1, Now)
Loop
y = ie.Document.getelementbyid("content").innerHTML
WS.Range("A1").Value = y
End Sub
 
Hi ,

Can you give an example or two of what you mean by title , image URL , ... ; the site is a Polish site , and even after translation I am not able to understand what exactly you are looking for.

Narayan
 
I meant that it search by html content;

For example This site's title is
<title>Find specific Data copy and paste into Cell Macro VBA | Chandoo.org Excel Forums - Become Awesome in Excel</title>

So the macro we are talking about should find "title"
and paste the "Find specific Data copy and paste into Cell Macro VBA | Chandoo.org Excel Forums - Become Awesome in Excel" into the cell I pick
 
Hi ,

I think you are explaining what is easy to explain !

I have found the following in your site :

<title>Wirtualna Polska - www.wp.pl</title>

I can see that Wirtualna Polska - www.wp.pl should be put in a worksheet cell.

Can you explain what you mean by image URL ?

Narayan
 
Oh this Img Url was an example and it doesnt matter ;p My bad.
That's how you say- that's what I need :)
 
Hi ,

Try this :
Code: 
Sub Retrieve_Webpage_Title()
    Dim title As String
    Dim objHttp As Object
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    objHttp.Open "GET", "http://www.wp.pl/", False
    objHttp.Send ""

    title = objHttp.ResponseText

    If InStr(1, UCase(title), "<TITLE>") Then
      title = Mid(title, InStr(1, UCase(title), "<TITLE>") + Len("<TITLE>"))
      title = Mid(title, 1, InStr(1, UCase(title), "</TITLE>") - 1)
    Else
      title = ""
    End If

    MsgBox title
End Sub
Copied from : http://stackoverflow.com/questions/14514181/excel-vba-to-get-website-title-from-url

Narayan
 
Lovely, almost got it.

Now I customized it for myself a little bit and...:


Code: 
Sub Retrieve_Webpage_Title()
    Dim title As String
    Dim objHttp As Object
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    objHttp.Open "GET" = InputBox("Input website address"), False   'The URL does not use a recognized protocol'
    objHttp.Send ""

    title = objHttp.ResponseText

    If InStr(1, UCase(title), "<TITLE>") Then
      title = Mid(title, InStr(1, UCase(title), "<TITLE>") + Len("<TITLE>"))
      title = Mid(title, 1, InStr(1, UCase(title), "</TITLE>") - 1)
    Else
      title = ""
    End If

    MsgBox title
    Range("A1").Value = title
   
   
End Sub

The URL does not use a recognized protocol

What should I change?
 
Hi ,

The problem is that the GET statement requires a URL with a trailing slash / ; try these three statements instead of one , though you can combine all of them in one :

s = InputBox("Input website address")
If right(s,1) <> "/" then s = s & "/"
objHttp.Open "GET", s, False

Narayan
 
Hi ,

The problem is that the trailing slash "/" is required only for the main page , which has sub-pages under it ; the URL you have given is of the last page in the tree , below which there are no more sub-pages ; given this URL , if you remove the trailing slash from your code , it will work.

Narayan
 
Well I did what you said


Code: 
Sub Retrieve_Webpage_Title()
Dim s As String
Dim title As String
    Dim objHttp As Object
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    s = InputBox("Input website address")
If Right(s, 1) <> "/" Then s = s & "/"
objHttp.Open "GET", s, False

    objHttp.Send ""

    title = objHttp.ResponseText

    If InStr(1, UCase(title), "<TITLE>") Then
      title = Mid(title, InStr(1, UCase(title), "<TITLE>") + Len("<TITLE>"))
      title = Mid(title, 1, InStr(1, UCase(title), "<TITLE>") - 1)
    Else
      title = ""
    End If

    MsgBox title
    Range("A1").Value = title
   
   
End Sub


Unfortunetly, still does not work :/

Unless I missunderstood you in some point
 
Hi ,

What I meant was that the slash is required only if the URL is somewhat like :

http://www.lazienkiabc.pl/

where if you enter http://www.lazienkiabc.pl , you can add the trailing slash.

When you enter a URL such as :

http://www.lazienkiabc.pl/Bateria-umywalkowa-Grohe-EUROSMART-32925001-4228.html

we cannot have the trailing slash.

What you can do is instead of checking for the presence of or absence of the trailing slash , using the following statement :

If Right(s, 1) <> "/" Then s = s & "/"

you can have the following lines :

If Right(s, 3) <> "htm" And Right(s, 4) <> "html" Then
If Right(s, 1) <> "/" Then s = s & "/"
End If

Narayan
 
Tkank you very much,

And do you know maybe how to put
<meta name="description" content=
sth like this into


f InStr(1, UCase(title), "<TITLE>") Then
title = Mid(title, InStr(1, UCase(title), "<TITLE>") + Len("<TITLE>"))
title = Mid(title, 1, InStr(1, UCase(title), "-") - 1)
between " "
?
Because "" are not making things easier... and I ger an error
 
From this

<meta name="description" content="Bateria umywalkowa GROHE Eurosmart 32925001 chrom błyszczący typ baterii:
I want to to have "Bateria umywalkowa GROHE Eurosmart 32925001 chrom błyszczący typ baterii"
From "description" content=" to ":"
But "" are imposiblle to write in VBA, beacuse they have another function.
So how can I bypass it?
 
I would also like to for each next macro using all "ranges" increase by 1.

Range("C2").Value = title
Range("N2").Value = description
Range("A2").Value = product_code
Range("D2").Value = price

This is for first use
In the secound Macro use It should be

Range("C3").Value = title
Range("N3").Value = description
Range("A3").Value = product_code
Range("D3").Value = price

How Can I do that?

Also please check this:
http://chandoo.org/forum/threads/finding-an-image-and-its-title.15236/

It 's the same code, a little bit modified and the rest is described in this topic
 
Last edited:
You must log in or register to reply here.
Back
Top