Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Problem with importing text data from website by macro

0

Hi, I need help with adjusting the macro to the website: https://euroesi.mazda.co.jp/esicont/eu_eng/mazda6/20120903161818/html/id0102h4801500.html .

I am using the code "ByClassName" ,but I cannot get the title "DTC TABLE[SKY..." to the excel spreadsheed from this service page, after my attempts it loaded wrong data or didn't load any. I am enclosing the code below.

       
Sub url_test2()
Dim act_name As String
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim title As Variant
act_name =
"https://euroesi.mazda.co.jp/esicont/eu_eng/mazda6/20120903161818/html/id0102h4801500.html"
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", act_name, False
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response
title = html.getElementsByClassName
End Sub("servinfo-head")
excel
posted by: Ryszard90 (rep: 2) on Thu May 5, 2022 at 10:56 am
Answer
Discuss

Discussion

Hi Ryszard and welcome to the Forum.

I'm not near my (PC for several hours yet) but in the code above, End Sub is in the wrong place -  it should be on its own line (and signals the end of the macro). 

Try changing the lines last two lines to:
title=html.getElementsByClassName("servinfo-head")
MsgBox title
End Sub

If you get a blank message, there's more to fix. Please respond.
John_Ru (rep: 6142) May 5, '22 at 11:23 am
Im sory its my mistake on copy code, macro runs fine, but still title of the website is not load to message.
Ryszard90 (rep: 2) May 5, '22 at 12:06 pm
Add to Discussion

Answers

0
Selected Answer

Ryszard

In the case of that website, you can get that data by changing your last two lines to those in bold below (then replace the MsgBox and Range("A1").... lines by ones to place the string in another cell say):

Sub url_test2()
Dim act_name As String
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim title As Variant

act_name = "https://euroesi.mazda.co.jp/esicont/eu_eng/mazda6/20120903161818/html/id0102h4801500.html"
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", act_name, False
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response

title = html.nameProp

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

End Sub

In the attached file, I ran the Module 1 code to paste the title into cell A10 then changed that code so it will put it in cell A1 when you run it.

This need the correct add-ion. If anyone has problems, in the VB Explorer menu pick Tools/References. If Microsoft HTML Object Library is not ticked (checked) in the list then search down to find and tick that. Then run the macro again. 

Hope this helps.

posted by: John_Ru (rep: 6142) on Fri May 6, 2022 at 3:57 am
Discuss

Discussion

Unfortunately it still shows a blank message
Ryszard90 (rep: 2) May 6, '22 at 5:01 am
Odd, it worked for me and the message box said "DTC TABLE..." 

Did you use Select All (above the code box)  to copy the code correctly?

I assume you have the correct Add-in (otherwise you'd get an error at the Dim html... line).

I'll test it again when I get home (in a couple of hours) 
John_Ru (rep: 6142) May 6, '22 at 5:47 am
yes, I copied all the code into a new module.
The add-ons "Microsoft HTML Object Libary" and "Microsoft Internet Controls" have been enabled.
Ryszard90 (rep: 2) May 6, '22 at 6:25 am
Please see my revised answer (and attached file).

I added a note about the necessary reference (for others). If you don't get the correct result, I can only think that you have a network issue at present.
John_Ru (rep: 6142) May 6, '22 at 8:23 am 
Still the same situation, but I ran the code on the second computer and it works, I have no idea why it doesn't show data on the first computer.
To work around this, I wrote a more complicated but working code.
Sub url_get_name()
Dim act_name As String
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim name As Variant

act_name = "https://euroesi.mazda.co.jp/esicont/eu_eng/mazda6/20120903161818/html/id000000800100.html"
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", act_name, False
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response

end_text = InStr(Left(html.body.innerText, 150), Chr(10)) - 2
name = Left(html.body.innerText, end_text)
MsgBox (name)
End Sub

Ryszard90 (rep: 2) May 7, '22 at 5:46 pm
Okay Ryszard, glad you got it to work. Your new code just looks for the position of the Line Feed character in the start of html body and extracts the leftmost text before that. So long as it works, it's a valid solution ...
John_Ru (rep: 6142) May 8, '22 at 3:38 am
Thanks for selecting my answer Ryszard! 
John_Ru (rep: 6142) May 8, '22 at 7:35 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login