Microsoft Excel - Create a macro to pull down data from web to excel

Asked By Nassa C on 10-Feb-09 02:53 PM

Hi all,

Would you please guide me how to write a macro that can pull some information from web to excel?

Thanks so much...

Cheers

Use XMLHTTP - Panji Tengkorak replied to Nassa C on 10-Feb-09 04:27 PM

You can use XMLHTTP object to do it, here is the function and the implementation example:

Sub Button1_Click()
    Dim strURL As String
    strURL = "http://www.eggheadcafe.com/default.aspx"
    ActiveSheet.Cells(4, 3) = GetRemoteData(strURL)
End Sub

Function GetRemoteData(strURL As String) As String
    Dim objXMLHTTP As New MSXML.XMLHTTPRequest
    objXMLHTTP.Open "GET", strURL, False
    objXMLHTTP.send
    GetRemoteData = objXMLHTTP.responseText
End Function

Do not forget to refer "Microsoft XML, version 2.0" into your Excel VBA project.
______________

Daniel

How to create Macro - Nassa C replied to Panji Tengkorak on 10-Feb-09 04:38 PM

Hi Daniel


Thanks so much.

What do you mean by "refer "Microsoft XML, version 2.0""? Would you please explain?

Thanks again for your help.

Nasim

VBA - Tools - Preferences - Panji Tengkorak replied to Nassa C on 10-Feb-09 07:02 PM

From the Microsoft Visual Basic window (where you edit you Excel VBA code) select [Tools] menu then click [Preferences]. Find "Microsoft XML, version 2.0" on the list then check the checkbox...
Important and urgent - Nassa C replied to Panji Tengkorak on 10-Feb-09 09:12 PM
Thanks. I can run this code now, but It is not the Thing that I am looking for... I am looking for a macro (EXCEL VBA code) which allow me to pull down the table on web into the Excel. Would you please help me in this regard? Thanks so much Cheers
urgent - Nassa C replied to Panji Tengkorak on 10-Feb-09 09:14 PM
Daniel, This code gives me the source code of the web page.... I am looking for the content of the web page which is a table. Would you please help me in this subject? Thanks Nasim
re - Panji Tengkorak replied to Nassa C on 10-Feb-09 10:44 PM
First, the code I gave you is an EXCEL VBA Code, second what do you mean by "table" there? is it an HTML table or a database table?
urgent - Nassa C replied to Panji Tengkorak on 10-Feb-09 11:43 PM
It's a HTML table...
you know what exactly it is....
we have a http address, ok?, and there is a table in it(HTML table). I want to paste the data in HTML table into Excel. not by using Import, I want a macro for it.
Thanks so much for your help....
Nasim
Excel VBA - Nassa C replied to Panji Tengkorak on 10-Feb-09 11:49 PM
You are right the code that you gave me is an Excel VBA code and I run in on VBA, however it doesnt give me the thing that I am looking for. It creates the source code of the webpage and put it on C4. would you please tell me what that means?
thanks
READ THIS - C_A P replied to Nassa C on 11-Feb-09 05:11 AM
why not save it as an xlt? and send it to them, then create a
macro to pull the data back into the original shee
TRY THIS LINK - C_A P replied to Nassa C on 11-Feb-09 05:12 AM
http://www.mcpressonline.com/tips-techniques/microsoft/techtip-automatically-load-data-into-excel.html
WebBrowser Control - Panji Tengkorak replied to Nassa C on 11-Feb-09 07:32 AM
If what you're looking for is to display the html table as it is without the need to calculate the value later you can use WebBrowser Control, do this to add the WebBrowser control on a Sheet:

  1. In Excel open the form in Design view.
  2. Right-click the menu bar and then click Control Toolbox.
  3. In the toolbox, click the More Controls tool. A menu appears that lists all the registered ActiveX controls in your system.
  4. On the menu of ActiveX controls, click Microsoft WebBrowser Control.
  5. On the sheet, click where you want to place the control.
  6. Move and size the control to the area you want to display
Then put this line of code anywhere you want it, it can be on a button or on your workbook event. In the following example I use button click:

Sub Button3_Click()
    ThisWorkbook.ActiveSheet.WebBrowser1.Navigate2 "http://www.eggheadcafe.com/default.aspx"
End Sub

BUT IF you want to perform a further calculation using the value of your HTML table within your Excel environment, then you need to use the previous code I gave you before, grab the HTML source and parse it to be inserted into your worksheet cell. I haven't had time to do it since it's going to be a very complex one depending on the HTML structure of your HTML table. Keep watching, I will do it when I have time...



Paste Special - Panji Tengkorak replied to Nassa C on 11-Feb-09 08:10 AM
Nasim, you can also use paste special to put the table into your sheet, here is the code:

Sub Button1_Click()
    Dim strURL As String
    Dim DataObj As New MSForms.DataObject
   
    strURL = "http://yoursite.com/table.html"

    DataObj.SetText GetRemoteData(strURL)
    DataObj.PutInClipboard
    Dim myRange As Range

    Set myRange = Range("A1:A1")
    myRange.PasteSpecial
End Sub

Function GetRemoteData(strURL As String) As String
    Dim objXMLHTTP As New MSXML.XMLHTTPRequest
    objXMLHTTP.Open "GET", strURL, False
    objXMLHTTP.send
    GetRemoteData = objXMLHTTP.responseText
End Function


Do not forget to refer "Microsoft XML, version 2.0" and "Microsoft Forms 2.0 Object Library" into your Excel VBA project.

Microsoft Forms 2.0 Object Library  is not likely found on the reference list so browse for it. CLick the browse button on the reference window then find "C:\WINDOWS\System32\FM20.DLL"

re - Nassa C replied to Panji Tengkorak on 11-Feb-09 08:31 AM

Thanks so much Panji...

This one is the thing that I am looking for...

By the way, would you please let me know what I can do with the previouse one to open the table in Excel, because as I told you before it shows HTML source code of the web page on one Cell nothing more.Should I do anything special?

Thanks so very much for your help.....

Thanks all esp Panji - Nassa C replied to Panji Tengkorak on 11-Feb-09 08:48 AM

Thanks so very much....

It works the way that I want...

I do really appreciate it Panji...

Cheers

Urgent inquery - Nassa C replied to Panji Tengkorak on 11-Feb-09 03:57 PM

Panji,

I have another question . If I want to give this code to different sheets, for example for sheet1 the url is different from sheet2. Then what I should do?

Thanks

Try this command - Rasoul Khoshravan replied to Nassa C on 12-Feb-09 04:13 AM
Although little bit late in responding to your question, but try this one as well:
Go to: Data |  Import External Data | select New Web Query.
Put your http address in relevant box and click go when it opened the site, click the import button.
HTH
re - Nassa C replied to Rasoul Khoshravan on 12-Feb-09 08:02 AM

Thanks... I haev tried this before, but becasue my http address is too long it gives me error... I tries Panji's solution. It works but I want it for different sheets and it is just for one specific sheet.


come on Nasim :D - Panji Tengkorak replied to Nassa C on 12-Feb-09 08:29 PM
Set myRange = Sheet3.Range("A1:A1")
myRange.PasteSpecial
Problem with running macros - Nassa C replied to Panji Tengkorak on 18-Mar-09 03:56 PM

I have 2 macros that create charts for one file, I can not run both macros one after another.

Would you please tell me what I am suppose to do to run these macros?

I also write the following macro to run both but it gives me an error:


Public Sub GenerateCharts()

      Call Module1.Macro1
    
    Call Module2.Macro2

End Sub