Saturday, November 10, 2012

How to consume a web service in Microsoft Word using VBA

A couple of months ago I was asked to take on a project which would use a word document to place an order over the web. The word document would need to post the order to a web service and show a message box to the end user,  letting them know that the order was successfully submitted and return an order number or that an error has occurred and what the error was.

The requirements for my project were as such:
  • Create a word document that will capture several fields which will be populated by another proprietary software.(Using Word 2010)
  • Create a method that will take the data from the form and put it into an XML envelope to be sent via SOAP.
  • Keep the document as backward compatible as possible.
  • Submit the SOAP envelope to the web service and handle error or successful submission.
Since I have never consumed or worked with web services in Word or VBA the first thing I did was hit the search engines and found nothing but useless complicated outdated examples on how to consume a web service using VBA in Microsoft Word.

I see that Microsoft recommends using Web Service References Tool:

http://msdn.microsoft.com/en-us/library/office/aa140260(v=office.10).aspx

Which leads to a dead link.:

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/officedev.asp

Plus my concern with using the Web Service References Tool limits the version of Microsoft Word, and I would like to support back to at least Word 2000.

So after much research and trial and error I decided to use SOAP utilizing Microsoft XML 3.0. You could use the same code with Microsoft XML 2.0 also, but I do not have the library available on my current machine, so 3.0 will do.



For demonstration purposes, I will simplify the form for this example.

Here is the code to  create your XML Doc:

Private Function fnBuildXML(ByVal blnContin As Boolean, ByVal objParams As OrderParameters) As DOMDocument

   Set MyParams = New OrderParameters
   Dim objDom As DOMDocument
   Dim objRootElem As IXMLDOMElement
   Dim objRootElem2 As IXMLDOMElement
   Dim objMemberElem As IXMLDOMElement
   Set objDom = New DOMDocument
  
    With MyParams
       
        ' Creates root element
        Set objRootElem = objDom.createElement("myOrderParameters")
        objDom.appendChild objRootElem

 '<PlaceOrder xmlns="http://webservice.com/">
   Dim mystring As String
        ' Creates Member element
        Set objMemberElem = objDom.createElement("OrderTaxSearch")
        objRootElem.appendChild objMemberElem
        mystring = ff("Check1").Result
        objMemberElem.Text = CStr(ff("Check1").Result)
      
        Set objMemberElem = objDom.createElement("OrderAssessmentSearch")
        objRootElem.appendChild objMemberElem
        objMemberElem.Text = ff("Check2").Result
        
        Set objMemberElem = objDom.createElement("OrderUtilitiesSearch")
        objRootElem.appendChild objMemberElem
        objMemberElem.Text = ff("Check3").Result

End If
End With
Set fnBuildXML = objDom
Set objDom = Nothing
End Function

Now that was simplified to just give an idea how to create the XML doc for the SOAP envelope. Here is example code to communicate with the web service:

    Dim envelope As String
    Dim http As New MSXML2.XMLHTTP
    Dim URL As String

    URL = "http://WEBSERVICE.net/neworders.asmx?op=PlaceOrder"

    envelope = "<?xml version='1.0' encoding='UTF-8'?>" & _
    "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' " & _
    "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>" & _
    "<soap:Body>" & "<PlaceOrder xmlns='http://WEBSERVICE.com/'>" & CStr(objDom.XML) & "</PlaceOrder></soap:Body></soap:Envelope>"

    Call http.Open("POST", URL, False) //make sure to set async to FALSE!!!!!
    http.SetRequestHeader "Content-Type", "text/xml"

        envelope = Replace(envelope, "True", "true")
        envelope = Replace(envelope, "False", "false")
                http.Send (envelope) //Here you are sending the SOAP envelope to the webservice
                MsgBox (http.responseText) //Display results from service
            
      Set http = Nothing
End With
      Set MyParams = Nothing

There ya go, please note this line:
Call http.Open("POST", URL, False)

Make sure to set varAsync to FALSE. As the default is true, word does not handle Async with web services right from what I could tell and this is defaulted to true, so your code will fail if you try to read the response, especially the longer the response. Let it wait, so set this to false, this really caused me a lot of issues until I set it to false. more about  MSXML2.XMLHTTP  here:

http://msdn.microsoft.com/en-us/library/windows/desktop/ms757849(v=vs.85).aspx

Hope this helps you save some time, and if you have any questions, please let me know.






Sunday, October 28, 2012

New Job and new articles coming

I wanted to drop by and say hi to everyone and let you all know that I am sorry I am just answering some posts.

I started a new job a few months ago and just haven't had the time to blog. I am still working at the wonderful company that employs me *Dream Job* but have had the urge to spend some more time blogging again. To those I am just replying to, sorry for the delay, but I am back and if anyone ever needs anything, please just drop me a line.

Jim

BTW we have finally hit 40K views as of this month. I am so happy that the information here has been helping people. If there is anything you would ever like to see here, please feel free to drop me a line.

Thursday, August 9, 2012

How to watch Amazon video on a rooted Amazon Kindle

I have noticed that sometimes I cannot watch my Amazon Prime videos on a rooted kindle fire. I then noticed that with two apps, I could watch these movies, sometimes, but then sometimes it just wouldn't work, so here I will detail the two apps you will need and the exact steps to easily without Amazon Prime Videos on your Kindle Fire.

You will need to grab the following file from google play or search the web for the apk file. The program is called:
Voodoo OTA RootKeeper

You also have another program called Superuser which was installed when you rooted.

Now, go to your Amazon Video and try to watch one of the prime videos. You will notice your "Watch now" button is greyed out and you cannot click it to watch your Amazon prime videos.
Now first thing you will need to do is tap on the little gear in the upper left hand corner. Once you tap that, a menu will come up. Select More.

Once you tap on More, you will be at the settings screen.

Now tap Applications. By default it shows you your running applications, which is exactly what we want. Now tap on Amazon Video. Once that comes up, tap on Clear Data under the storage section.

Now once you have done that go back to your home screen and launch the Voodoo OTA Root Keeper. There you will want to click on Protect root, once you tap that tap on Temp Unroot "Keeps Backup"

Now go back to your home screen and launch the Amazon Videos.

You will now see you can watch your Prime or purchased videos without any issue. Now, here is the thing you can watch all you want. You may even go back and restore root in Voodoo OTA Root Keeper. Then continue watching your videos.

Everything will be fine, unless you stop the Amazon Video application, or reboot your Kindle Fire. If you do this, just go back into Voodoo OTA Root Keeper and temp unroot again, but make sure to follow these steps again, as you must kill the amazon video application before you remove root, as it does a root check on launch.
Enjoy..,.

Friday, June 1, 2012

Sign up for Telerik’s Q2’12 Webinar Week, see what’s new and win a license!


Telerik has announced Q2 2012 Webinar week. You can sign up at the link above or by clicking Here . One lucky person will win more than $2,000 worth of software just for attending, so make sure you get on board and sign up.

Thursday, April 5, 2012

Rooting the Kindle Fire Update for 6.3

As some of you who have rooted your Kindle in the past, have recently had an issue when 6.3 is applied.
If you have rooted your Kindle the update breaks your Kindle Fire in a strange way.
For me, it would power off then reboot to a little Android robot with an exclamation point.

Well there are many updates out there to Root your Kindle running the 6.3 software, but the easiest I have found is the following images from this post: http://forum.xda-developers.com/showthread.php?t=1569298

Thanks to eldarerathis.

You simply download the image and apply the ROM and your Kindle Fire will be rooted again, until the next Kindle Fire Update.

Monday, February 6, 2012

Telerik Announces Q1 Webinar week

Telerik has announced a full week of webinars, you can sign up here:

http://www.telerik.com/support/webinars.aspx

Everything from KendoUI to Sitefinity...

Thursday, January 26, 2012

Hacking the Kindle Fire , Rooting your Kindle Fire, getting the Google Marketplace working on the Kindle Fire.

On Christmas my mother in law bought my 13 year old son the Amazon Kindle Fire. After he went to sleep, the only time I could get to use it, I decided to play around. I have not had any experience with the Kindle product line or the Android operating system.

At first glance the Kindle Fire is a pretty sharp looking device, but I wasn't all that impressed with the Amazon layout so I decided to look around to see if I could blast it away. It turns out, you can, you can even install the Google marketplace, replace the loader and the web browser, and here is what you need to do:

Hardware needed:
  • a Kindle Fire
  • a USB cord
  • A Windows7 Based PC( for MAC support, go away or go get a real PC...) You can use any current Windows version, I am using Windows 7 for this tutorial.
Software needed (At this time, do not install anything, just put all this in a folder):

Instructions:

To Root Your Kindle Fire
  1. Install the Java SE Development Kit.
  2. Install the Andriod SDK.
Now once the Andriod SDK is installed, Plug in your Kindle fire and open up the SDK manager. 

Check the following:
  • Andriod SDK Tools.
  • Andriod SDK Platform-Tools.
  • Then at the bottom, check Google USB Driver Package.
  • Click install.
Now it should install the USB driver for the ADB which will allow you to communicate using the Andriod Debug Bridge.

UPDATE 2/6/2012 - In Regards to The Kindle Fire 6.2.2 Update and some not being able to use ADB over USB

Some are complaining that since the Kindle Fire 6.2.2 update they are not able to use ADB over USB after the update. here is a newer driver for the Kindle Fire's USB and instructions if you are having issues using the default UBS driver:

http://forum.xda-developers.com/showthread.php?t=1483177

http://www.jayceooi.com/2011/12/13/how-to-install-kindle-fire-adb-usb-driver/
Once the USB drivers are installed, unplug your Kindle from the PC and click start and type this: %SYSTEMDRIVE%\Users\%USERNAME%\.android

Open the file by right clicking and click on EDIT.

Add the following to the document: 0x1949.

Save the file.

      3. Now take BurritoRoot3, Root Explorer, Google Apps(Extracted), and the Google Vending app and copy them to the following folder: C:\Program Files (x86)\Android\android-sdk\platform-tools. If you changed the default install location, goto wherever you installed it.

      4. While holding the SHIFT key, right click and choose "Open Command Window Here".

      5. On the Kindle, go into Device Settings and turn on Allow Installation of Applications.

      6. Plug in your kindle to the PC and type in the following commands:

adb shell chmod 777 /data/local/tmp
adb install BurritoRoot3.apk 

Open BurritoRoot on the Kindle and then on the PC type:
adb shell /data/local/tmp/BurritoRoot3.bin --root
adb shell /data/local/tmp/BurritoRoot3.bin --install

Restart you Kindle Fire.

You are now rooted.

     7. Now let's install RootExplorer, type the following:
adb install RootExplorer.apk

     8. Take the Google files and copy them using Windows Explorer to the folder Documents on your Kindle Fire. Now unplug your Kindle Fire.

     9. On the Kindle Fire open up Root Explorer. Navigate to the Documents folder(in the SDCard folder under root). Now click on the GoogleServicesFramework.apk and select install.

   10. Now go back to the Documents folder and install Vendor.apk. Setup your Google account. Then find the app called Go Launcher EX and install it. You have to install this as the Kindle launcher ignores the Google Marketplace.

  11. BACKUP - Now reboot your Kindle Fire. When starting it, keep tapping the power button until recovery comes up. Select backup and restore, then backup, then yes. Once it is complete, connect the Kindle Fire back to the PC and restart. Once rebooted, from the PC go into the folder of the Kindle fire called clockworkmod, under SD card, which is the root when connecting the Kindle Fire. Copy this folder to your PC, so you have a good backup.

You now have a rooted Kindle Fire with the Google Marketplace.

TIPS:

Search the marketplace for the app called OTA Rootkeeper. This will allow you to protect root from updates and turn root off if you want to use Amazon video.

Now that you have the marketplace, install Firefox, it is a much better web browser than silk.

If you want to re-partition the internal storage, you can use https://github.com/eldarerathis/FireParted/downloads, but be careful.

Here are some useful links:

If you brick:

http://forum.xda-developers.com/showthread.php?t=1414832
http://forum.xda-developers.com/showthread.php?t=1356257

Kindle Fire XDA forums:

http://forum.xda-developers.com/forumdisplay.php?f=1306

BurritoRoot3:
http://forum.xda-developers.com/showthread.php?t=1410223

Running Ice Cream Sandwich (Andriod 4.X) on your Kindle Fire:
http://forum.xda-developers.com/showthread.php?t=1411895

CM7 on your Kindle Fire:
http://forum.xda-developers.com/showthread.php?t=1390773