VBA WordPress connection class with first connection test

Tags

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