Navigating Exel web page 1

[PavelGur]

I'm getting the Web page that is actually Exel7. The way I get it is:
Dim oIE
Dim sURL As String
Set oIE = New SHDocVw.InternetExplorer
sURL = "

http://ichart.finance.yahoo.com/table.csv?s=A&d=4&e=10&f=2006&g=d&a=10&b=18&c=1999&ignore=.csv"

oIE.Navigate sURL
Do Until oIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop

Now I do not know how to navigate the spreadsheet. I was trying
Dim Hist as Workbook
Hist = oIE.Document
It does not work.
I will be satisfied if I can read cells in the sheet w/o transfering to any other workbook and/or worksheet. But I have no idea how to do it.
Thanks, Pavel.

 

[comaboy]

Here's a little code that might help you get started. Add a Reference to the Microsoft Excel Object Library and see what this does - you should be able to hack it your needs. Also, it may help to record a Macro in Excel and copy/paste the code into VB (Again, you'll need to modify the pasted code).

Dim appXL As Excel.Application

Private Sub Form_Load()
    Set appXL = New Excel.Application
    Dim MyFile, MyPath, strWorkbook
    strWorkbook = Dir(strPath & "*.xls")
    Do While strWorkbook <> ""   ' Start the loop.
       ' Ignore the current directory and the encompassing directory.
       If strWorkbook <> "." And strWorkbook <> ".." Then
            ProcessWorkBook (strWorkbook)
       End If
       strWorkbook = Dir   ' Get next entry.
    Loop
    Set appXL = Nothing
End Sub

Sub ProcessWorkBook(strWorkbook As String)
    Dim wbk As Workbook
    Dim sht As Worksheet
    Dim SumRange As Range
    Dim lngScore As Long
    Dim lstItem As ListItem
    Set wbk = appXL.Workbooks.Open(strPath & strWorkbook, True)
    Set SumRange = wbk.Worksheets("World Cup").Range("P7:P89")
    lngScore = appXL.WorksheetFunction.Sum(SumRange)
    Debug.Print lngScore
    Debug.Print Left(strWorkbook, Len(strWorkbook) - 4)
    wbk.Close False
    Set wbk = Nothing
End Sub

Note that the code is copied from a current app I'm working on. It's totally untested, and will almost definately fail, but it should give you enough pointers (Pointers? In VB?;-)) to get you going.

 

[PavelGur]

Thank you very much comaboy!
I actualy resolved the problem. I noticed that the spreadsheet Web Page has a unique URL based on date and requested info. I was able to set up URL in my Excel macro and save data from spreadsheet in my Data Base.
Thanks again, Pavel.

 

[comaboy]

Glad you found a solution