Click here to Skip to main content
14,488,104 members
Rate this:
Please Sign up or sign in to vote.
When I fetch data from a webpage to an Excel file using Macro it saves the data in the Excel file as it is supposed to be. My requirement is that it should save the data in the Excel with the hyperlinks in the webpage and If we click any hyperlink in the Excel it should go to the particular website and fetch the data and save the information in the excel file or sheet. I hope it makes sense. Any help will be greatly appreciated.

My Macro code below:


Sub GetTable()

Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim ieTable As Object
Dim clip As DataObject

'create a new instance of ie
Set ieApp = New InternetExplorer

'you don’t need this, but it’s good for debugging
ieApp.Visible = True

'assume we’re not logged in and just go directly to the login page
ieApp.Navigate "website link"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

'fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
    .user.Value = "UserNmae
    .Password.Value = "password"
    .submit
End With
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'now that we’re in, go to the page we want
ieApp.Navigate "final webpage link"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'get the table based on the table’s id
Set ieDoc = ieApp.Document
Set ieTable = ieDoc.all.Item("AutoNumber1")

'copy the tables html to the clipboard and paste to teh sheet
If Not ieTable Is Nothing Then
    Set clip = New DataObject
    clip.SetText "<html>" & ieTable.outerHTML & "</html>"
    clip.PutInClipboard
    Sheet1.Select
    Sheet1.Range("A1").Select
    Sheet1.PasteSpecial "Unicode Text"
End If

'close 'er up
ieApp.Quit
Set ieApp = Nothing

End Sub
Posted
Updated 14-May-13 22:46pm
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Have a look here: Hyperlinks.Add Method (Excel)[^]
   
Comments
Manas Bhardwaj 10-Aug-13 14:27pm
   
Looks helpful +5!
Maciej Los 10-Aug-13 14:27pm
   
Thank you, Manas ;)
Manas Bhardwaj 10-Aug-13 14:29pm
   
you are welcome :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100