How to Integrate WordPress with Excel For Free

Deepanshu Bhalla Add Comment ,

This tutorial explains how to integrate WordPress with MS Excel for free. It automates data transfers between WordPress and Excel.

Automate WordPress with Excel

Prerequisite : No coding or special tools are required. You just need admin access to your WordPress site.

Table of Contents

Authorization

The first step is to authenticate your API requests. WordPress needs authentication to verify user credentials. You will need your WordPress username and an application password.

  1. Log in to your WordPress dashboard.
  2. Go to Users > Profile.
  3. Scroll down to the Application Passwords section.
  4. Enter a name for the application. Enter any name you want.
  5. Click Add New Application Password to generate a password.
  6. Copy the password and save it for future reference.
How to Use This Excel Tool

You may see this warning when you open the downloaded file. Microsoft always shows this warning message when you download a macro file from internet. Please follow the steps below to resolve this -

Microsoft has blocked macros from running because the source of this file is untrusted.
  1. Go to the folder where the downloaded macro file is located.
  2. Right-click the file and choose Properties from the menu.
  3. At the bottom of the General tab, select the Unblock checkbox under security option and then click OK.
  4. Open the file and now you will not see the warning of macros being blocked.
If the Developer tab is enabled, go to the Developer tab on the Ribbon and then click on Visual Basic.
OR
You can also press Alt + F11 to open the VBA editor directly. In the VBA editor, look at the Modules on the left side and click on Module1 and then edit the following lines of code.

Const wpSite As String = "https://www.example.com"
Const wpUsername As String = "XXXXXXX"
Const wpPassword As String = "XXXX XXXX XXXX XXXX" ' Application Password

Posts

List WordPress Posts

In Excel, hit "Get Posts" button to fetch details of all posts from your WordPress site.

If you want details of a specific post, you can specify id in the GetWpContent procedure in Module1 module.

To find the post ID, you need to open the post in edit mode and then check the browser's address bar. The URL will appear something like post.php?post=357&action=edit. The number after post= in this URL is the post ID. In this case, it is 357 so replace id = 0 with id = 357 in the code.

Create a New WordPress Post

The function CREATE_WP_CONTENT creates a new post on your WordPress site.

=CREATE_WP_CONTENT(B3:B6,C3:C6,"posts")
Fields to Create WordPress Post in MS Excel

In the "content" field, you can also include HTML to present content better as shown in the example below. You can even include CSS and Javascript as a body of the post.

<p> This is an introductory paragraph for testing. </p>

We have a function called CONVERT_TO_HTML to convert data to HTML table.

It considers headers in cells B3:B6 and values in cells C3:C6 to create a post as shown in the table above.

  • title: The name of the post.
  • content: The main body of the post including text, images, videos etc.
  • slug: A URL-friendly version of the title, used in the post's link.
  • status: The visibility of the post like publish, future, draft, pending, private.
  • date: The date the post was published, in the site's timezone.
  • date_gmt: The date the post was published, in GMT.
  • password: A password to protect access to the content and excerpt.
  • author: The ID of the author for the post.
  • excerpt: A brief summary or excerpt of the post.
  • featured_media: The ID of the featured media for the post.
  • comment_status: Whether or not comments are open on the post. Options include: open, closed.
  • ping_status: Whether or not the post can be pinged. Options include: open, closed.
  • format: The format of the post, such as standard, aside, chat, gallery, link, image, quote, status, video, or audio.
  • meta: Additional meta fields for custom data.
  • sticky: Indicates if the post should be treated as "sticky" (featured).
  • template: The theme file used to display the post.
  • categories: The categories assigned to the post.
  • tags: The tags assigned to the post, under the post_tag taxonomy.

Update WordPress Post

The function UPDATE_WP_CONTENT modifies an existing post of your WordPress site.

=UPDATE_WP_CONTENT(B3:B6,C3:C6,357,"posts")

In this example, we are updating post with ID 357.

Delete WordPress Post

The function DELETE_WP_CONTENT deletes a specific post of your WordPress site.

=DELETE_WP_CONTENT("posts", 774)

In this example, we are removing post with ID 774.

Pages

List WordPress Pages

In Excel, hit "Get Pages" button to fetch details of all pages from your WordPress site.

If you want details of a specific page, you can specify id in the GetWpContent_Pages procedure in Module1 module.

Create a New WordPress Page

The following function creates a new page on your WordPress site.

=CREATE_WP_CONTENT(B3:B6,C3:C6,"pages")

It considers headers in cells B3:B6 and values in cells C3:C6 to create a post as shown in the table below.

BC
3titleMy New Title
4contentThis is introduction to using Wordpress API in MS Excel
5slugwp-in-excel
6statuspublish
  • title: The name of the page.
  • content: The main body of the page including text, images, videos etc.
  • slug: A URL-friendly version of the title, used in the page's link.
  • status: The visibility of the page like publish, future, draft, pending, private.
  • date: The date the page was published, in the site's timezone.
  • date_gmt: The date the page was published, in GMT.
  • password: A password to protect access to the content and excerpt.
  • author: The ID of the author for the page.
  • parent: The ID The ID for the parent of the page.
  • excerpt: A brief summary or excerpt of the page.
  • featured_media: The ID of the featured media for the page.
  • comment_status: Whether or not comments are open on the page. Options include: open, closed.
  • ping_status: Whether or not the page can be pinged. Options include: open, closed.
  • meta: Additional meta fields for custom data.
  • template: The theme file used to display the page.
  • menu_order: The order of the page in relation to other pages.

Update WordPress Page

The function UPDATE_WP_CONTENT modifies an existing page of your WordPress site.

=UPDATE_WP_CONTENT(B3:B6,C3:C6,451,"pages")

In this example, we are updating page with ID 451.

Delete WordPress Page

The function DELETE_WP_CONTENT deletes a specific page of your WordPress site.

=DELETE_WP_CONTENT("pages", 818)

In this example, we are removing page with ID 818.

Media Items (Images)

Get Media Items from WordPress

In Excel, hit "Get Media" button to fetch details of all media items (images) from your WordPress site.

If you want details of a specific image, you can specify id in the GetWpContent_Media procedure in Module1 module.

Upload Image to WordPress Using Excel

In Excel, hit "Upload Image" button to upload an image to your WordPress site. Click the button to select an image to upload. After a few seconds, a message will confirm the upload was successful.

Delete Media Items from WordPress

The following function deletes a specific media item like image from the WordPress media library. In this case, we are removing a media item with ID 804.

=DELETE_WP_CONTENT("media", 804)

Comments

Get Comments from WordPress

In Excel, hit "Get Media" button to extract all comments from your WordPress site.

If you want details of a specific comment, you can specify id in the GetWpContent_Comments procedure in Module1 module.

Delete Comments from WordPress

The function DELETE_WP_CONTENT deletes a comment from your WordPress site.

=DELETE_WP_CONTENT("comments", 5)

Create a New Comment

The following function creates a new comment on your WordPress site.

=CREATE_WP_CONTENT(E9:E12,F9:F12,"comments")
EF
9post351
10contentthis is a new comment
11author_namedeeps
12author_emaildeeps@gmail.com

Update Comment

The following function modifies an existing comment on a WordPress site.

=UPDATE_WP_CONTENT(E9:E12,F9:F12,10,"comments")

How to Send Table From Excel to WordPress

Let's say you have a table in cells B3:C7 and you want to transfer data from the table to your wordpress site.

Step 1 : Convert Table to HTML

You can use the function below to generate table's HTML source code.

=CONVERT_TO_HTML("B3:C7")
Step 2 : Use HTML code in content field

Now you can use the table's HTML source code in the "content" field in the CREATE_WP_CONTENT or UPDATE_WP_CONTENT functions.

Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 0 Response to "How to Integrate WordPress with Excel For Free"
Next →