0 Replies - 514 Views - Last Post: 25 September 2012 - 12:54 PM Rate Topic: -----

#1 nexus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 21-April 08

Excel VBA

Posted 25 September 2012 - 12:54 PM

Not 100% sure if this is the right place to put this under but here goes.

I'm trying to grab data from a website and bring it into my excel sheet. I had it working with one search but since I've moved to trying to grab multiple inputs I've run into a '1004' runtime error. I'm am triggering this code via a button. What it should do is go down column a starting at cell two looking up the data related to what I'm searching. Such as 'vanilla bean.' It will go search for it and return back the sell and buy order prices for me.

Any help would be greatly appreciated. I have attached what I've done below.


Sub Update()

Dim i As Integer
Dim IE As New InternetExplorer

'IE.Visible = True

For i = 2 To 100

'Error happens at the next line    
    IE.navigate "http://www.gw2spidy.com/search/" & Range("a", 2).Value
    
    Do
     DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE
    
    Dim Doc As HTMLDocument
    Set Doc = IE.document
    Dim buy As String
    Dim sell As String

    buy = Trim(Doc.getElementsByTagName("td")(5).innerText)
    sell = Trim(Doc.getElementsByTagName("td")(4).innerText)
    
    IE.Quit

    
    splitBuy = Split(buy, " ")
    splitSell = Split(sell, " ")
    
    Dim buyNum As Integer
    Dim buySell As Integer
    Dim goldBuy As Integer
    Dim silverBuy As Integer
    Dim copperBuy As Integer
    
    If InStr(splitBuy(0), "g") > 0 Then
    
        goldBuy = Val(Left(splitBuy(0), Len(splitBuy(0)) - 1))
        silverBuy = Val(Left(splitBuy(1), Len(splitBuy(1)) - 1))
        copperBuy = Val(Left(splitBuy(2), Len(splitBuy(2)) - 1))
        buyNum = goldBuy & silverBuy & copperBuy
        
        goldSell = Val(Left(splitSell(0), Len(splitSell(0)) - 1))
        silverSell = Val(Left(splitSell(1), Len(splitSell(1)) - 1))
        copperSell = Val(Left(splitSell(2), Len(splitSell(2)) - 1))
        sellNum = goldSell & silverSell & copperSell
        Range("b" & i).Value = buyNum
        Range("c" & i).Value = sellNum
    
    Else
    
        silverBuy = Val(Left(splitBuy(0), Len(splitBuy(0)) - 1))
        copperBuy = Val(Left(splitBuy(1), Len(splitBuy(1)) - 1))
        buyNum = silverBuy & copperBuy
        silverSell = Val(Left(splitSell(0), Len(splitSell(0)) - 1))
        copperSell = Val(Left(splitSell(1), Len(splitSell(1)) - 1))
        sellNum = silverSell & copperSell
        Range("b" & i).Value = buyNum
        Range("c" & i).Value = sellNum
    
    End If

Next i

End Sub




Is This A Good Question/Topic? 0
  • +

Page 1 of 1