Quick and dirty : posting to WordPress from Word with VBA. I put the text source on the server.

It works, to a point. You can take a Word document, run the PostToWordpress macro (with your own blogs settings) and it puts the content in WordPress.

I haven’t worked with Word for a few years, you’d have to ask someone with up-to-date Word vba skills how to grab the document title, and sanitize and format the document text into proper html-output. For the tests I grabbed a snippet off the net, it does not do a perfect job but the basic idea is kosher.

metaWeblog.newPost

Most sites (including blogger) have skipped from blogger.newPost to newer formats. I prefer using the metaWeblog.newPost rpc method, it is a bit more versatile, and supported by WordPress as well.

(ref. msdn library article on metaWeblog.newPost)

The methodcall is structured like this :

  • methodname : metaWeblog.newPost
  • blogid
  • blog user name
  • blog password
  • the post struct with it’s members
    • categories array
    • description (the doc text)
    • title
    • date created
  • publish (0=save as draft, 1=publish immediately)

So let’s build that step by step as vba routine :


Sub PostToWordpress()

'the basic blog settings
    txtURL = "http://www.blog.com/xmlrpc.php"
    txtBlogId = "1"
    txtUserName = "MyUserName"
    txtPassWord = "MyPassword"

'the document settings
    txtTitle = "MyTitle"
    'note : you can use the documents creation date here
    txtDateCreated = Format(Now(), "yyyyMMdd") & "T" & Format(Now(), "hh:mm:ss")
   
    'Categories is an array in the post,
    'you can use category names,
    'and you can always use "Uncategorized" 
    'as all wordpress blogs have it

    Dim MyCategories(2) As String
    MyCategories(1) = "Uncategorized"
    MyCategories(2) = "example_category_one"

    'note: this function grabs the document text, 
    'replaces linebreaks with html linebreaks etc. 

    'it needs a lot more work
    txtDocument = getCurrentDocAsSimpleHtml()

Once I have the blog settings and the content + attributes for the new WordPress post, I set up the XMLHttpRequest object :

      
    Dim objSvrHTTP As ServerXMLHTTP
    Dim strT As String
    Set objSvrHTTP = New ServerXMLHTTP
  
    objSvrHTTP.Open "POST", txtURL, False, CStr(txtUserName), _
    CStr(txtPassWord)
  
    objSvrHTTP.setRequestHeader "Accept", "application/xml"
    objSvrHTTP.setRequestHeader "Content-Type", "application/xml"

Then I start building the XML to send to WordPress,


'methodcall    
    strT = strT & ""

'methodname
    strT = strT & "metaWeblog.newPost"

'parameters : blog settings
    strT = strT & ""
    strT = strT & "" & txtBlogId & ""
    strT = strT & "" & txtUserName & ""
    strT = strT & "" & txtPassWord & ""

'parameters : the post structure
    strT = strT & ""
        strT = strT & ""

'parameters : post : category array
            strT = strT & "categories"
                strT = strT & ""
                    strT = strT & ""
                        strT = strT & ""

   For i = 1 To UBound(MyCategories)
                            strT = strT & "" & MyCategories(i) & ""
   Next i
                        strT = strT & ""
                    strT = strT & ""
                strT = strT & ""
            strT = strT & ""

'parameters : post : the content
        strT = strT & "description"
            strT = strT & "< [!CDATA[" & txtDocument & "]]>"
        strT = strT & ""

'parameters : post : title
        strT = strT & "title" & txtTitle & ""

'parameters : post : date created
        strT = strT & "dateCreated" &  txtDateCreated & ""
        strT = strT & ""
    strT = strT & ""

'parameters : post : store as draft (0) or publish immediately (1)
    strT = strT & "0"

'end parameters
    strT = strT & ""

'end methodcall
    strT = strT & ""

'send it to wordpress
    objSvrHTTP.send strT

'send the response to the debug window
    Debug.Print objSvrHTTP.responseText

End Sub

conversion to html

The main practical problem is converting the Word document content to html. I grabbed two quick functions to select the current document text, convert the most common html entities and replace linebreaks with their html counterparts, but it is far from perfect.

Private Function getCurrentDocAsSimpleHtml() As String
'... see source text
End Function

' simple HTML entity encoder
Private Function encode(ByVal s As String) As String
    If s = "&" Then
        encode = "&"
    ElseIf s = "< " Then
        encode = "<"
    ElseIf s = ">" Then
        encode = ">"
    ElseIf s = Chr(13) Then
        encode = "
" Else encode = s End If End Function

The WordPress Incutio xml-parser will not pass content it cannot make sense of, which leaves you with an empty post.

using CDATA

A rather common hack I used above is using < ![CDATA[ ]]> to wrap the post content, which indicates to the wordpress xml-parser that I pass a string of raw character data as content. The parser ignores it, and WordPress stuffs everything in the database.

Works great, but it can backfire and slip stuff in the database that wordpress cannot handle and display correctly.

I’d test without using CDATA if you want to post from Word on a regular basis, or use a more professional html-converter on the word documents first.

10 thoughts on “metaWeblog.newPost posting to WordPress from Word”

  1. Thanks for the quick response dude… Just what I was looking for.

    Just thought these would help you a bit…

    Post from Excel to WordPress
    Post from Word to WordPress
    VBA to WordPress

    Excellent.

  2. Thanks to you for the tutorial.

    I have one question for you if you can answer please.

    I have used the code above and have also added some more code to also include custom fields but it does not working. Could you tell me what you think the problem might be?

    I have added the following code after ‘parameters : post : date created

    ‘parameter : post structure : member : custom_fields
    strT = strT & “custom_fields”

    strT = strT & “”

    strT = strT & “MyField1My Field 1 Value”
    strT = strT & “MyField2My Field 2 Value”

    strT = strT & “”
    strT = strT & “”

    Thanks

    Right-oh

  3. Lets us try again…

    Change the “[” with “”

    ‘parameter : post structure : member : custom_fields
    strT = strT & “[member][name]custom_fields[/name][value][array]”

    strT = strT & “[data][array]”

    strT = strT & “[array][key]MyField1[/key][value]My Field 1 Value[/value][/array]”
    strT = strT & “[array][key]MyField2[/key][value]My Field 2 Value[/value][/array]”

    strT = strT & “[/array][/data][/array]”
    strT = strT & “[/value][/member]”

    1. @Right foot, you were on the right way.

      Custom fields can have a unique id as well, so a basic key-value pair does not cover it ( http://codex.wordpress.org/XML-RPC_wp shows the mw_newPost data structure ).

      It requires an array with structs as [data] in stead of an array with arrays :

      ‘parameter : post structure : member : custom_fields
      strT = strT & “[member][name]custom_fields[/name][value][array][data]”

      ‘list with id-key-value structs, id is optional
      strT = strT & “[struct]”
      strT = strT & “[member][name]key[/name][value]MyField1[/value][/member]”
      strT = strT & “[member][name]value[/name][value]MyField1 Value[/value][/member]”
      strT = strT & “[/struct]”

      strT = strT & “[struct]”
      strT = strT & “[member][name]key[/name][value]MyField2[/value][/member]”
      strT = strT & “[member][name]value[/name][value]MyField2 Value[/value][/member]”
      strT = strT & “[/struct]”

      strT = strT & “[/data][/array][/value][/member]“

  4. Hey,
    great post. But it seems that you’ve made a misprint in this string:
    strT = strT & “”

    It should looks like this:
    strT = strT & “”

    Cheers
    Alex

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top