Query Fedex and/or USPS for Shipping Rates within Excel

[yourguide]

Windows 7 Excel 2010
I am attempting to query Fedex and USPS via their API and return the rates I get back into excel.
I have a PHP application on an internal server that when given a URL that contains address and package data it returns a value in a single table.
I am able to successfully get the data back via a Web Query within one cell for one line of package and address data.

What I need is the ability to dynamically query the PHP application based on values from the current row I am in. Right now all my attempts at creating a macro that uses a Web Query have the cells hardcoded within the Macro so it won't scale to other fields.
Perhaps a web query is not even the best way to achieve this?
Ideally we would like to create a "Formula" that goes and gets the data based on the cells around it. Like an actual Excel Formula does.
Could anyone suggest how I could accomplish this?

The reason for not just downloading FEDEX rates, etc. is that we have an account with Fedex that gives us a discount and we want to run analysis based on the actual rates we are getting.
Thank you for any and all suggestions.

 

[yourguide]

Here is my attempt so far:

Function FedexRate(weight1, length1, width1, height1, zip1, city1, state1)


    www = "URL;http://192.168.0.80/rocketship/index.php?weight=" _
          & weight1 & "&length=" & length1 & "&width=" & width1 & "&height1=" & height1 & "&zip=" & zip1 & "&city=" & city1 & "&state=" & state1
          
    With ActiveSheet.QueryTables.Add(Connection:=www, Destination:=FedexRate)
        .Name = "Fetching Value"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """pitching_gamelogs"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With


End Function

This does not work... am I trying to return it back incorrectly or am I just completely off??

Thanks for any help.

 

[yourguide]

Could anyone please nudge me in the right direction?
I am a web developer and can create a PHP web page that processes requests via GET or POST... Just know nothing of Excel. Am I even on the right track?
Please offer any advice. Thanks!

 

[yourguide]

* bump *
Bueller.... Anyone.... Anyone....

 

[yourguide]

I figured it out by using MSXML2.ServerXMLHTTP
I can now return the value from a GET Http Request into excel.... like this:

Function FedexRate(length, width, height, weight, zip, service)

    FedexRate = "http://127.0.0.1/rocketship/fedex.php?weight=" _
          & weight & "&length=" & length & "&width=" & width & "&height=" & height & "&zip=" & zip & "&service=" & service
          
Dim objHttp As Object
Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
Call objHttp.Open("GET", FedexRate, False)
Call objHttp.Send("")
FedexRate = objHttp.ResponseText


End Function

Fedex.php is a PHP page that uses the excellent RocketShip.IT product to pull rates from the shipper API's

I hope this helps someone.

So far the only problem I have now is that the returned value doesn't seem to be a number that I can run calculations on.... if I try to use the value in addition to another cell I get "#VALUE"
Any ideas?
Thanks.

 

[yourguide]

Ok got it!

I just modified the last line:

USPSRate = Val(objHttp.ResponseText)

Val() changes the string to a number!

 

[Panavision]

Is there anyway to modify this to extract tracking information?

 

[yourguide]

That was not my goal... but I have seen other posts that grab tracking information via Excel's webquery function... though I have never needed to set it up for myself.