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):
• “##YourUserName##” and “##YourPassWord##” with your credendials.
When running the program “wpTest” you then should see an Excel dialog saying “Hello!“.