Help importing data from a website into Excel?

[chaos]

Hi all,

From inside Excel, I wish to surf to a website and retrieve the bottom entry in a column The column updates dynamically with a new number added to the bottom on a daily basis.

Here is the website URL: http://global-view.com/fxhist.TXT.

The column I am interested in is the second column from the left under the History section. The column contains close prices for the currency symbol EUR/$.

I hope this is clear. Any help would be appreciated.

Regards,

chaos

 

[Juan Pablo González]

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>

 

[chaos]

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

 

[XL-Dennis]

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

 

[chaos]

Hi Juan, XK-Dennis,

Got it working now. Works great!!

Thanks,

chaos

 

[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

 

[Juan Pablo González]

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.

 

[chaos]

Juan,

You're awesome!!!

Works perfectly now.

Regards,

Chaos

 

[bino_grace]

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.

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.

 

[techsmart]

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!