CODEWITHSUNDEEP
excelvbaparameters
user1153109
user1153109

Reputation: 11

excel vba - creating paramaters and loop in a web query

I am new to programming and i am trying to work through my web query.

The code i have so far goes like this...

Sub webquery1()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://dealoz.com/prod.pl?cat_id=&op=buy&op2=&lang=en-us&search_country=us&shipto=us&cur=usd&zip=&nw=y&class=&pqcs=&pkcs=&quantity=&shipping_type=&sort=&catby=&query=[""089203579X""]&ean=[""9780892035793""]&mpn=&asin=&rcount=2" _
        , Destination:=Range("A1"))
        .Name = _
        "prod.pl?cat_id=&op=buy&op2=&lang=en-us&search_country=us&shipto=us&cur=usd&zip=&nw=y&class=&pqcs=&pkcs=&quantity=&shipping_type=&sort=&catby=&query=[""089203579X""]&ean=[""9780892035793""]&mpn=&asin=&rcount=2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "10"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False

    End With

End Sub

I am trying to add parameters to this web query such that the website will take isbn numbers from a list that i have on another excel sheet. Each isbn number will go into the web query and the information will be input into a cell. The code will then repeat until it goes through all the isbn numbers on my list. Thanks for any help!!!

Upvotes: 1

Views: 2121

Answers (1)

klausnrooster
klausnrooster

Reputation: 560

Without doing the whole thing and testing it (no Excel on my Ubuntu box) you can start thus

Sub webquery1(byval isbn10 as string, byval isbn13 as string)

then replace

089203579X""]&ean=[""9780892035793

with

& isbn10 & chr(34) & chr(34) & "]&ean=[" & chr(34) & chr(34) & isbn13 &

you may have to fiddle with balancing all the literal double-quotes and chr(34)s, but you'll get there. Then you need a sub to iterate through the isbns on the other sheet and call this sub. Do you really need both kinds of isbn? Is one of them sometimes missing?

Sub iter_thru_isbn_sheet_calling_webquery()
    dim range_isbns as range
    dim c as range
    set range_isbns = sheets("whateveritscalled").range("addressgoeshere")
    For each c in range_isbns
        if not c.value = "" then    '<---- better way to use regexp but you can research
            call webquery1(c.value, c.offset(0,1).value)   '<---if 10s and 13s side-by-side
            doevents    '<---- query can take awhile. I don't do web queries in vba, so ??
        end if
    next c
End Sub

You need to work out how to collect both isbns and prevent iterating over and passing an isbn13 or 10 that you already bundled with it's "twin". Also get in the habit of writing an error handler for every sub, if only to log details to a text file. For Excel stuff go to these guys first: ozgrid, mrexcel, Pearson. I'd post links but it's time to turn in. Good Luck.

Upvotes: 1

Related Questions