how to get website data via excel - Xtreme Visual Basic Talk

12-02-2006, 07:20 AM

vinsie

Junior Contributor

Join Date: Apr 2005

Posts: 355

how to get website data via excel

Hello, I need via (Office EXCEL XP) to get Internet Data,
I use Database(D), Import other data(D), New web quote (W)
then give me my website, selected table to import. done.

Example: Quote Price
http://finance.yahoo.com/q?s=GOOG
http://finance.yahoo.com/q?s=DELL

Becuase doing this if need changed website link, but my question, can use EXCEL VB do this effect !?

TX1 = "http://finance.yahoo.com/q?s="
TX2 = A1 'GOOG ' 'DELL'...
WEBSITE = TX1 & TX2

Command1_Click = WEBSITE

Any suggest ? Please!

12-02-2006, 01:03 PM

littlepd

Newcomer

Join Date: Nov 2006

Location: Lewisville, TX

Posts: 10

Code:

    Dim strConnection as String, strName as String
    strConnection = "http://finance.yahoo.com/q?s=DELL"
    strName = "q?s=DELL"
    With ActiveSheet.QueryTables.Add(Connection:= "URL;" _
                     strConnection, Destination:=Range("A1"))
        .Name = strName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "10,13,15,17,19,20"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

12-02-2006, 11:33 PM

vinsie

Junior Contributor

Join Date: Apr 2005

Posts: 355

thank you. I try make this script.
If enter value in Sheet1.G1 ...example (GOOG,DELL)...
and CLICK command buttom.

why some problem...
(A) first click, is correct (show me A5,B5) , and changed value to "DELL"
and next click (A5,B5=DELL), but last data is move to (C5.D5=GOOG)
and next click again (A5,B5,C5,D5,E5,F5...)

what wrong ? can clean last quote value?

(B) can you tell me any method easy select table need. becuase I dull try more times..
.WebTables = 1 , wrong
.WebTables = 6 , wrong
.WebTables = 12 , wrong
.WebTables = 15 , correct

(C) if need Refresh, how to Refresh, I need use SEC, no MIN, any method ?
because I found excel, only 1 min (min), if 10sec,20sec is correct value ?

.RefreshPeriod = 1 'can change 1/2 = 30sec ?

please!

Code:

Sub gethtmltable()
    Dim sQUOTE As String
    Dim ds As Worksheet
    
    
    Set ds = Sheets("Sheet1")
    sQUOTE = ds.Range("G1").Value
    
    Dim strConnection As String, strName As String
    strConnection = "http://finance.yahoo.com/q?s=" & sQUOTE
    strName = "q?s=" & sQUOTE
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & strConnection, Destination:=Range("A1"))

        .Name = strName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 1
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = 15 
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        
    End With
 
   

End Sub

12-03-2006, 12:07 PM

littlepd

Newcomer

Join Date: Nov 2006

Location: Lewisville, TX

Posts: 10

I'm sorry. I have no idea what you are asking.