• Category Archives Specials
  • Hot specials!

  • Returning GPS Coordinates, and calculating Driving distance in Excel 2007

    I recently had the mispleasure of having to code a vbscript to allow me to take advantage of the Google Distance Matrix API and the GPS encoding function of Maps. Luckily, I found a wonderful article on the Police Analyst blog. One part of the chore was completed! It even walked me through how to use the VB script editor for the purpose.

    Utilizing my programming prowess (which is pretty poor) and some amazingly smart coder friends, we hammered out how to get a return on the Driving Matrix API as well. Two new functions were born in Excel: GoogleDistance and GoogleGeocode.

    Use is pretty simple. In the cell, place your formula like this:

    =GoogleGeocode(CELL)

    The CELL is the reference cell for where you want the gps coordinate for. It will do specific addresses, or cities.

    =GoogleDistance(CELL1, Cell2)

    CELL1 is the originating location. C ELL2 is the destination. You can use GPS coordinate, address, or city for either.

    To implement the functions in your Excel 2007, just follow the directions at the Police Analyst post, get your own Google Maps Distance Matrix API and use the following cut and paste instead of the Police Analyst one:

    Function GoogleGeocode(address As String) As String
    Dim strAddress As String
    Dim strQuery As String
    Dim strLatitude As String
    Dim strLongitude As String

    strAddress = URLEncode(address)

    ‘Assemble the query string
    strQuery = “https://maps.googleapis.com/maps/api/geocode/xml?”
    strQuery = strQuery & “address=” & strAddress
    strQuery = strQuery & “&sensor=false”

    ‘define XML and HTTP components
    Dim googleResult As New MSXML2.DOMDocument
    Dim googleService As New MSXML2.XMLHTTP
    Dim oNodes As MSXML2.IXMLDOMNodeList
    Dim oNode As MSXML2.IXMLDOMNode

    ‘create HTTP request to query URL – make sure to have
    ‘that last “False” there for synchronous operation

    googleService.Open “GET”, strQuery, False
    googleService.send
    googleResult.LoadXML (googleService.responseText)

    Set oNodes = googleResult.getElementsByTagName(“geometry”)

    If oNodes.Length = 1 Then
    For Each oNode In oNodes
    strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
    strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
    GoogleGeocode = strLatitude & “,” & strLongitude
    Next oNode
    Else
    GoogleGeocode = “Not Found (try again, you may have done too many too fast)”
    End If
    End Function
    Function GoogleDistance(origin As String, destination As String) As String
    Dim strAPIKey As String
    Dim strOrigin As String
    Dim strDestination As String
    Dim strQuery As String

    ‘Set API Key
    strAPIKey = “<INSERT YOUR API KEY HERE>”

    ‘URL Encode Origin and Destination
    strOrigin = URLEncode(origin)
    strDestination = URLEncode(destination)

    ‘Assemble the query string
    strQuery = “https://maps.googleapis.com/maps/api/distancematrix/xml?”
    strQuery = strQuery & “origins=” & strOrigin
    strQuery = strQuery & “&destinations=” & strDestination
    strQuery = strQuery & “&key=” & strAPIKey

    ‘define XML and HTTP components
    Dim googleResult As New MSXML2.DOMDocument
    Dim googleService As New MSXML2.XMLHTTP
    Dim oNodes As MSXML2.IXMLDOMNodeList
    Dim oNode As MSXML2.IXMLDOMNode

    ‘create HTTP request to query URL – make sure to have
    ‘that last “False” there for synchronous operation

    googleService.Open “GET”, strQuery, False
    googleService.send
    googleResult.LoadXML (googleService.responseText)

    Debug.Print googleService.responseText

    Set oNodes = googleResult.getElementsByTagName(“distance”)

    If oNodes.Length = 1 Then
    For Each oNode In oNodes
    GoogleDistance = oNode.ChildNodes(0).Text
    Next oNode
    Else
    GoogleDistance = googleResult.ChildNodes(1).Text

    End If

    End Function
    Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
    Dim StringLen As Long: StringLen = Len(StringVal)

    If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = “+” Else Space = “%20”

    For i = 1 To StringLen
    Char = Mid$(StringVal, i, 1)
    CharCode = Asc(Char)

    Select Case CharCode
    Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
    result(i) = Char
    Case 32
    result(i) = Space
    Case 0 To 15
    result(i) = “%0” & Hex(CharCode)
    Case Else
    result(i) = “%” & Hex(CharCode)
    End Select
    Next i
    URLEncode = Join(result, “”)
    End If
    End Function

    You can also download the code as a text file from here: Google GPS conversion and Distance


  • Buying a used Cell phone? Make sure it’s not stolen!


    Are you thinking of buying a used cell phone from Ebay, Kijiji, or some other source? Make sure you check the phone’s IMEI number against the Canadian Blacklist to make sure it isn’t someone else’s lost or stolen phone!

    ProtectYourData.ca features a convenient tool that allows you to enter the IMEI number of a wireless device and find out immediately if that device has been blacklisted in Canada. If the IMEI number has been blacklisted, that device will not be able to be used on participating Canadian networks. The database includes blacklisted devices that have been reported as lost or stolen as of September 30, 2013 and beyond.

    Phones that have been blacklisted will not be able to connect to any of the participating Canadian Carrier’s networks. Keep in mind it can take up to 48 hours for the device to appear on the blacklist after it has been reported. The list does not include older CDMA devices.


  • Special alert!

    Special Ended – July 28, 21010

    If you are still interested in projectors, please contact us for info and pricing!

    NEC NP110 DLP Projector

    Projection Technology:      DLP
    Projector Type:      Business Projector
    Native Resolution:      SVGA (800 x 600)
    Brightness:      2200 ANSI Lumens
    Contrast Ratio:      2000:1
    Native Aspect Ratio:      4:3 Standard
    Display Technology:      DLP
    Data Signals:      NTSC4.43, PAL, PAL-M, PAL-N, SECAM, NTSC, PAL-G, PAL-H, PAL-I, PAL B, PAL-D,
    Inputs:      S-Video, PC Input, RCA Jack
    Audio:      7-Watt Speaker
    Lens:      F=2.41 to 2.55, f=21.83 to 24mm
    Zoom:      1:1.1 Manual Zoom
    Image Size:      33″ – 300″ diagonal
    Throw Distance:      1.95 to 2.15
    Keystone Correction:      +/- 40 degrees
    Power Supply:      100 – 240V AC, 50/60 Hz
    Audible Noise:      34 dB
    Lamp Type:      180W
    Lamp Life Expectancy:      3000/5000 Hours
    Remote Control:      Included
    Dimensions:      12.2″ x 3.7″ x 9.7″
    Weight:      5.5 lbs.

    This is an excellent business class projector. Built for video presentations, it is capable of projecting a nice bright image in a fully lit room. It’s able to correct the image for mounting to a ceiling or floor.

    I can currently get this from my supplier for $385.00 plus taxes and S&H*. Please reference number N124-2444 when inquiring!

    * Only GST is charged. Shipping charges are dependent on how many units purchased.


  • Website Update

    Posted on by Jess

    I realize the website doesn’t look much different since I put it up, but there are things going on in the background. DTC Consulting believes in creating an environment where the regular person can keep control of their spending by understanding what they are buying.

    We focus on educating our customers and letting them pick who and where they purchase their products. By providing some links to several suppliers, we further assist out customers in this choice. Any links on this website will have been investigated, and checked for the quality or the software, hardware or services.

    For example, our web hosting is provided by 1and1.com. If we trust them with our face to the world, we can honestly promote them to our customers.

    If you have any questions regarding our partner links, please do not hesitate to email a question to us!