vba module
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA module

VBA module

resolvedResolved · Medium Priority · Version 2016

Celine has attended:
Excel Intermediate course

VBA module

I need help to create a VBA module so I can extract information from a list of links I have. Basically, I have a list of links that have information in Spanish but these links have a link to original information in English. So what I'm trying to do is to extract the English links from the Spanish links.

This is how far I have gone, but it is giving me an error and is not working for me. Could you help please?

Sub GetEnglishLinks()
Dim ie As Object
Dim html As Object
Dim mylinks As Object
Dim myLink As Object
Dim result As String
Dim myURL As String
Dim LastRow As Integer

Set ie = CreateObject("InternetExplorer.Application")

LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

myURL = Sheet1.Cells(1, 1).Value

ie.navigate myURL

ie.Visible = False

While ie.busy Or ie.readyState <> 4
DoEvents
Wend

result = ie.Document.body.innerHTML

Set html = CreateObject("htmlfile")

html.body.innerHTML = result

Set mylinks = html.getElementsByClassName("a")

For Each myLink In mylinks

If myLink = "href" Then
Sheet1.Cells(i, "B").Value = myLink
End If
Next myLink
If i = LastRow Then
ie.Quit
End If
Next i
End Sub

RE: VBA module

Hi Celine,

Thank you for the forum question.

Can you please let me know which error message you get and which line return the error.

Thanks


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: VBA module

thanks for your quick response Jens. I guess I'm finding it hard to specify the element from the html file. I pressed Debug and the run-time error is "438", saying Object doesn't support this property or method. And there is a line highlighted in yellow

Set mylinks = html.getElementsByClassName("a")

The other problem is every time I run the module, a message pops up saying a cookie needs to be allowed (not sure if this is to do with internet explorer)

Hope this helps.

Thanks again,

Celine

RE: VBA module

Good Morning Celine,


Unfortunately I haven't been able to find and answer, but I have sent the issue to one of my colleagues and maybe he can help out.

About the message "a cookie needs to be allowed"

If it is Excel which give you the message you can bypass it by adding the line just after Sub GetEnglishLinks()

Application.DisplayAlerts=False

and another line just before End Sub

Application.DisplayAlerts=True

You will here from us later and hopefully we can help you.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: VBA module

Good Morning Celine,


Unfortunately I haven't been able to find and answer, but I have sent the issue to one of my colleagues and maybe he can help out.

About the message "a cookie needs to be allowed"

If it is Excel which give you the message you can bypass it by adding the line just after Sub GetEnglishLinks()

Application.DisplayAlerts=False

and another line just before End Sub

Application.DisplayAlerts=True

You will here from us later and hopefully we can help you.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: VBA module

Thanks for looking into this Jens.

RE: VBA module

Hi Celine,

Unfortunately we cannot help you with your question.

We do not have the knowledge unfortunately, but we can point you in a direction, which may help you.

The list below is just some thoughts and questions, which came to our minds Googling and searching for answers.

Does the HTML source actually contains a class called "A"? We do not know the HTML code from the website or the HTML version.

To reference the HTML on the website can you work with late binding? Declare the variables as objects.

Your variables are declared as objects and I assume that you have not activated the HTML object library and you use late binding.

If you activate (if you haven't done it) the HTML Object library and change the code to early binding, will this solve the issue?

I have found some links which may help you.

https://stackoverflow.com/questions/34826631/how-to-get-html-element-with-vba-in-excel

https://www.encodedna.com/excel/extract-contents-from-html-element-of-a-webpage-in-excel-using-vba.htm

Sorry that we cannot help you with this one


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: VBA module

Thank you so much Jens - I found your answers really helpful. I still have to figure out the html element. I guess I am nearly there :)

Many thanks again,
Celine

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
advanced+excel+training+in+london and other training courses.

Excel tip:

Calculate difference between two times

For presenting the result in the standard time format (hours : minutes : seconds . Use the subtraction operator (-) to find the difference between times, and the TEXT function to format the returned value to text in a specific number format.

Hours never exceed 24, minutes never exceed 60, and seconds never exceed 60.

=TEXT(B2-A2,"h")
Hours between two times (4)

=TEXT(B2-A2,"h:mm")
Hours and minutes between two times (4:55)

=TEXT(B2-A2,"h:mm:ss")
Hours and seconds between two times (4:55:00)

Where B2 and A2 must hold the end time and start time respectively formatted as a time format

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.08 secs.