Help importing data from a website into Excel?

You use Web queries for that.

Here's a macro that works in 97,2000 and XP that retrieves the entire file, you could just delete the columns that you don't need.

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> GetWebData()
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    ActiveWorkbook.Worksheets.Add
    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://global-view.com/fxhist.TXT", Destination:=Range("A1"))
        .Name = "fxhist"
        .FieldNames = <SPAN style="color:#00007F">True</SPAN>
        .RefreshStyle = xlOverwriteCells
        .SavePassword = <SPAN style="color:#00007F">False</SPAN>
        .SaveData = <SPAN style="color:#00007F">True</SPAN>
        .Refresh BackgroundQuery:=<SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Range("A1", Cells.Find(What:="History", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(-1)).EntireRow.Delete
    Range("A:A").TextToColumns Range("A1"), xlDelimited, Space:=<SPAN style="color:#00007F">True</SPAN>
    Cells.EntireColumn.AutoFit
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Juan,

Thank you for the code. That is a fine solution. I can import all the data and then work within Excel. Many thanks.

However, something appears to need adjusting in the code. When I run the macro, I get this error message:

System error & H80070057[-2147024809], The Parameter is incorrect

BTW, 'm using Excel 2000.

Your help is greatly appreciated.

Regards,

chaos

Hi Master Juan - Nice code

Chaos,

I don´t get any errormessage when I´m running the procedure.

How are You connected to the net?

Kind regards,
Dennis

Hi Juan, XK-Dennis,

Got it working now. Works great!!

Thanks,

chaos

Hi again Juan, AL-Dennis,

Did you notice that, in the data that loads into Excel, the columns shift to the right one, two or three cells from Nov. 1 - Nov 8 and Dec 2 - Dec 9?

Scroll down and you will see. The original data seems to be orderly in its columns.

Any idea how to fix this, either in the import code or with a subsequent macro? Your help is appreciated.

Regards,

chaos

Thanks Dennis, I enjoy working with web queries a lot !

And chaos, try this:

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> GetWebData()
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    ActiveWorkbook.Worksheets.Add
    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://global-view.com/fxhist.TXT", Destination:=Range("A1"))
        .Name = "fxhist"
        .FieldNames = <SPAN style="color:#00007F">True</SPAN>
        .RefreshStyle = xlOverwriteCells
        .SavePassword = <SPAN style="color:#00007F">False</SPAN>
        .SaveData = <SPAN style="color:#00007F">True</SPAN>
        .Refresh BackgroundQuery:=<SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Range("A1", Cells.Find(What:="History", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(-1)).EntireRow.Delete
    Range("A:A").TextToColumns Range("A1"), xlDelimited, Space:=True, ConsecutiveDelimiter:=<SPAN style="color:#00007F">True</SPAN>
    Cells.EntireColumn.AutoFit
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    Range("A:A").SpecialCells(xlCellTypeBlanks).Delete xlShiftToLeft
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>


</FONT>

That was tested on 2000.

Juan,

You're awesome!!!

Works perfectly now.

Regards,

Chaos

Juan Pablo González said:

Thanks Dennis, I enjoy working with web queries a lot !

And chaos, try this:


Sub GetWebData()
****Application.ScreenUpdating = False
****ActiveWorkbook.Worksheets.Add
****With ActiveSheet.QueryTables.Add(Connection:= _
********"URL;http://global-view.com/fxhist.TXT", Destination:=Range("A1"))
********.Name = "fxhist"
********.FieldNames = True
********.RefreshStyle = xlOverwriteCells
********.SavePassword = False
********.SaveData = True
********.Refresh BackgroundQuery:=False
****End With
****Range("A1", Cells.Find(What:="History", After:=ActiveCell, LookIn:=xlFormulas, _
********LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
********MatchCase:=False).Offset(-1)).EntireRow.Delete
****Range("A:A").TextToColumns Range("A1"), xlDelimited, Space:=True, ConsecutiveDelimiter:=True
****Cells.EntireColumn.AutoFit
****On Error Resume Next
****Range("A:A").SpecialCells(xlCellTypeBlanks).Delete xlShiftToLeft
****Application.ScreenUpdating = True
End Sub




That was tested on 2000.

Click to expand...

Hello,

Please help to get a macro in excel.
Step1:
Get company codes from excel sheet
say for eg:
1)DAAWAT
2)SUNPHARM etc
Replace XYZ in the url http://www.buzzingstocks.com/in/index.pl?t=XYZ&f=2 with each company code ie DAAWAT
ie http://www.buzzingstocks.com/in/index.pl?t=DAAWAT&f=2
Step2:
Acces the web page and get the last line of the trading simulation
<TABLE width="100%"><TBODY><TR><TD bgColor=#f5f5f5>2009-08-14</TD><TD bgColor=#f5f5f5>57.70</TD><TD bgColor=#f5f5f5>No trade</TD><TD bgColor=#f5f5f5>2104</TD><TD bgColor=#f5f5f5>121443.12</TD><TD bgColor=#f5f5f5>0.00</TD><TD bgColor=#f5f5f5>121443.12</TD><TD bgColor=#f5f5f5>21.44</TD><TD bgColor=#f5f5f5>HOLD</TD></TR></TBODY></TABLE>.

Your help is highly appreciated.

I think rather than coding scripts, you can do it using a software. Like Automation Anywhere.

It's a cool software in web data extraction. Get a free trial and see how it helps you.

Cheers!