In the previous post to this topic we have seen a basic function to open a connection to WordPress using VBA. Lets now go some steps further and create our first, hopefully, working example. Therefore
• open Excel and create a new Workbook
• open the VBA Editor (Alt-F11)
• right-click on the Workbook Object and “Insert -> Class Module”
• rename the Class to “WordPress”
• add “Tools -> References -> ‘Microsoft XML, v6.0′”
paste the following code into the code section of the added class:

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Option Explicit
Private URL As String
Private Tags As String
Private WPID As String
Private Text As String
Private Title As String
Private Domain As String
Private UserName As String
Private PassWord As String
Private Categories As String
Private ContextSet As Boolean
Private publishURLTemplate As String
Private publishImmediately As Boolean

Public Function setContext(ByVal wpDomain As String, ByVal wpUserName As String, ByVal wpPassWord As String, ByVal wpBlogID As String) As String
    WPID = wpBlogID
    Domain = wpDomain
    UserName = wpUserName
    PassWord = wpPassWord
    URL = "http://" & Domain & "/xmlrpc.php"
    publishURLTemplate = "https://wordpress.com/post/" & WPID & "/#blgID#/?optin"
    If Not ((Domain = "") Or (WPID = "") Or (UserName = "") Or (PassWord = "")) Then ContextSet = True
End Function
Public Function ping() As String
    ping = extract(post("demo.sayHello"))
End Function
Private Function extract(ByVal Term As String) As String
    Dim P As Long, aSpec As String, eSpec As String
    aSpec = "": eSpec = ""
    P = InStr(1, Term, aSpec): Term = Mid(Term, P + Len(aSpec))
    P = InStr(1, Term, eSpec): extract = Mid(Term, 1, P - 1)
End Function
Private Function post(ByVal wpXMLRPC As String) As String
    'http://codex.wordpress.org/XML-RPC_MetaWeblog_API
    Dim oHTTP As XMLHTTP, Response As String 
    Response = ""
    If ContextSet Then
       Set oHTTP = New XMLHTTP 
       Call oHTTP.Open("POST", URL, False, UserName, PassWord)
       Call oHTTP.setRequestHeader("Accept", "application/xml")
       Call oHTTP.setRequestHeader("Content-Type", "application/xml")
       Call oHTTP.send(wpXMLRPC)
       Response = oHTTP.responseText
       Set oHTTP = Nothing
    End If
    post = Response
End Function
Private Sub Class_Initialize()
    ContextSet = False
    URL = ""
    WPID = ""
    Tags = ""
    Text = ""
    Title = ""
    Domain = ""
    UserName = ""
    PassWord = ""
    Categories = ""
    publishURLTemplate = ""
    publishImmediately = False
End Sub
Private Sub Class_Terminate()
    '
End Sub
' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

• right-click on the Workbook Object and “Insert -> Module”
• paste the following code into the code section of the module:

Sub wpTest()
    Dim wp As New WordPress
    Call wp.setContext("##YourDomain##", "##YourUserName##", "##YourPassWord##", "##YourWPID##")
    Call MsgBox(wp.ping())
    Set wp = Nothing
End Sub

Here you have to substitute
• “##YourDomain##” with your domain, e.g. “yourblog.wordpress.com”
• “##YourWPID##” with the ID you can find in the new Blog Editor (we will use this in upcoming posts):

• “##YourUserName##” and “##YourPassWord##” with your credendials.

When running the program “wpTest” you then should see an Excel dialog saying “Hello!“.

If you want to send an XML-RPC payload to WordPress from Microsoft Office (e.g. Excel) then you can use the following function. In there the wpURL is expected e.g of the form “http://#yourDomain#/xmlrpc.php “, where you have to replace “#yourDomain#” by your blog address. wpUserName, wpPassword is your credentials and the wpXMLRPC is the xml payload which can be created also in VBA using basic VBA functionality and based on the specification described in “http://codex.wordpress.org/XML-RPC_MetaWeblog_API “. Please note that you have to add a library reference to a “Microsoft XML” library in the VBE (VB Editor) (e.g. “Microsoft XML, v6.0” in “Tools -> References”).

In later posts we will see how to prepare messages using basic functionality (XML parser free) so that you can maintain easily.

'----------------------------------------------------------
Function wpPost(ByVal wpURL As String, ByVal wpUserName As String, ByVal wpPassword As String, ByVal wpXMLRPC As String) As String
    Dim oHTTP As XMLHTTP, wpResponse As String
    ' VBAProject References needed: "Microsoft XML"
    ' wpURL: e.g. "http://#yourDomain#/xmlrpc.php "
    ' wpXMLRPC see: http://codex.wordpress.org/XML-RPC_MetaWeblog_API
    Set oHTTP = New XMLHTTP
    Call oHTTP.Open("POST", wpURL, False, wpUserName, wpPassword)
    Call oHTTP.setRequestHeader("Accept", "application/xml")
    Call oHTTP.setRequestHeader("Content-Type", "application/xml")
    Call oHTTP.Send(wpXMLRPC)
    wpResponse = oHTTP.responseText
    Set oHTTP = Nothing
    wpPost = wpResponse
End Function
'-----------------------------------------------------------