This tutorial explains how to integrate WordPress with MS Excel for free. It automates data transfers between WordPress and Excel.
Prerequisite : No coding or special tools are required. You just need admin access to your WordPress site.
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.
- Log in to your WordPress dashboard.
- Go to Users > Profile.
- Scroll down to the Application Passwords section.
- Enter a name for the application. Enter any name you want.
- Click Add New Application Password to generate a password.
- Copy the password and save it for future reference.
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 -
- Go to the folder where the downloaded macro file is located.
- Right-click the file and choose Properties from the menu.
- At the bottom of the General tab, select the Unblock checkbox under security option and then click OK.
- 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")
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.
B | C | |
---|---|---|
3 | title | My New Title |
4 | content | This is introduction to using Wordpress API in MS Excel |
5 | slug | wp-in-excel |
6 | status | publish |
- 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")
E | F | |
---|---|---|
9 | post | 351 |
10 | content | this is a new comment |
11 | author_name | deeps |
12 | author_email | deeps@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.
You can use the function below to generate table's HTML source code.
=CONVERT_TO_HTML("B3:C7")
Now you can use the table's HTML source code in the "content" field in the CREATE_WP_CONTENT
or UPDATE_WP_CONTENT
functions.