Query Fedex and/or USPS for Shipping Rates within Excel

yourguide

New Member
Joined
Mar 11, 2014
Messages
7
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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

yourguide

New Member
Joined
Mar 11, 2014
Messages
7
Here is my attempt so far:

Code:
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

New Member
Joined
Mar 11, 2014
Messages
7
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

New Member
Joined
Mar 11, 2014
Messages
7

ADVERTISEMENT

I figured it out by using MSXML2.ServerXMLHTTP
I can now return the value from a GET Http Request into excel.... like this:
Code:
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

New Member
Joined
Mar 11, 2014
Messages
7
Ok got it!

I just modified the last line:
Code:
USPSRate = Val(objHttp.ResponseText)
Val() changes the string to a number!
 

yourguide

New Member
Joined
Mar 11, 2014
Messages
7
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,419
Messages
5,511,245
Members
408,835
Latest member
janetjordan

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top